CODEWITHSUNDEEP
c#cassandracqlcassandra-2.0
user2704472
user2704472

Reputation: 219

C# insert loop fails for cassandra

I am trying to insert rows into cassandra table using C# datareader loop, after insert one records it give me the below error

Error:{"Object cannot be cast from DBNull to other types."}

What am i doing wrong

--Code

        ConnectionString._SQLServerName = txtsql.Text;
        ConnectionString._DBName = "casmonitor";
        SqlConnection MyConn = ConnectionString.GetOpenedConnection();
        SqlCommand MyCommand = ConnectionString.GetOpenedConnection().CreateCommand();
        SqlDataReader SqlReader;

        string sqltext = ("select * from DBAdmin..[stockhist1] (nolock)");
        MyCommand.CommandText = sqltext;
        SqlReader = MyCommand.ExecuteReader();
        while (SqlReader.Read())
        {
            ISession CluSession = cluster2.Connect("dbs");
            var ps = CluSession.Prepare("insert into stocks (id,name,price1,price2,price3,price4) values (?,?,?,?,?,?)");

            //  var dbstring= "insert into stocks (id,name,price1,price2,price3,price4) values (" + SqlReader["id"].ToString() + ",'" + SqlReader["name"].ToString() + "'," + Convert.ToUInt64(SqlReader["price1"].ToString()) + "," + Convert.ToUInt64(SqlReader["price2"]) + "," + Convert.ToUInt64(SqlReader["price3"]) + "," + Convert.ToUInt64(SqlReader["price4"]) + ");";
            // CluSession.Execute(dbstring);
            //  CluSession.Execute("insert into stocks (id,name,price1,price2,price3,price4) values (" + SqlReader["id"].ToString() + ",'" + SqlReader["name"].ToString() + "'," + Convert.ToUInt64(SqlReader["price1"].ToString()) + "," + Convert.ToUInt64(SqlReader["price2"]) + "," +Convert.ToUInt64(SqlReader["price3"]) + "," +Convert.ToUInt64(SqlReader["price4"]) + ")");

           // var ps = CluSession.Prepare("insert into Product(id,name,p_image) values (?,?,?)");
            var statement = ps.Bind(Convert.ToInt32(SqlReader["id"]),SqlReader["name"].ToString() ,Convert.ToDecimal(SqlReader["price1"]) ,Convert.ToDecimal(SqlReader["price2"]) ,Convert.ToDecimal(SqlReader["price3"]),Convert.ToDecimal(SqlReader["price4"]));
            CluSession.Execute(statement);

            cluster2.Shutdown();
            CluSession.Dispose();


        }
        SqlReader.Close();
        MyConn.Close();

Upvotes: 0

Views: 108

Answers (1)

Darren Wainwright
Darren Wainwright

Reputation: 30727

One of these values (the Convert.To....()) is returning null from the database

var statement = ps.Bind(Convert.ToInt32(SqlReader["id"]),SqlReader["name"].ToString() ,Convert.ToDecimal(SqlReader["price1"]) ,Convert.ToDecimal(SqlReader["price2"]) ,Convert.ToDecimal(SqlReader["price3"]),Convert.ToDecimal(SqlReader["price4"]));

You cannot, for example, convert a DBNull to a decimal or int.

I would suggest you create a function that you can pass each of these into, and then return either a 0 (where DBNull) or the value

Something like this (not tested, just quick and dirty):

    public static decimal GetDecimalValue(SqlDataReader reader, string fieldName)
    {
        if (!reader.IsDBNull(reader[fieldName])))
            return reader.GetDecimal(reader[fieldName]);
        else
            return 0;
    }

then adjust your statement to:

var statement = ps.Bind(Convert.ToInt32(SqlReader["id"]),
SqlReader["name"].ToString() ,
GetDecimalValue(SqlReader, "price1"),
GetDecimalValue(SqlReader, "price2"),
GetDecimalValue(SqlReader, "price3"),
GetDecimalValue(SqlReader, "price4"));`

Create one for int's too (haven't done that for you in the above)

Upvotes: 2

Related Questions