C# Caching is dropping rows when synchronizing to the database.

Question

Hi all,

im using temporary table to hold data to display it in gridview and then from that table to database, im using cache for this process.

Problem :
sometimes one record from the table is not loading into the database. if im using break point its working.

if (Cache["Info"] != null)
{
       Table = (DataTable)Cache["Info"];
       Table.Rows.Add(0, this.ddl1.SelectedItem.ToString(), 
                this.ddl1.SelectedValue.ToString());
       Table.Rows.Add(Table.Rows.Count, this.ddl2.SelectedItem.ToString(), 
                this.ddl2.SelectedValue.ToString());
}

foreach (DataRow dr in Table.Rows) 
{
  this.SetInfo(Convert.ToInt32(dr["No"]), Convert.ToString(dr["ID"])); 
}

query to insert data:

INSERT INTO sample(field1, field2, field3, field4, field5, field6)
VALUES
(@field1, @field2, @field3, @field4, @field5, @field6)

try
{
    if (this.sqlCon.State == ConnectionState.Closed)
        this.sqlCon.Open();

    this.sqlCmd = new SqlCommand("procedure_name", this.sqlCon);
    this.sqlCmd.CommandType = CommandType.StoredProcedure;

    this.sqlCmd.Parameters.Add(new SqlParameter("@field1", SqlDbType.VarChar));
    this.sqlCmd.Parameters["@field1"].Value = value;

    this.sqlCmd.Parameters.Add(new SqlParameter("@field2", SqlDbType.VarChar));
    this.sqlCmd.Parameters["@field2"].Value = value;

    this.sqlCmd.Parameters.Add(new SqlParameter("@field3", SqlDbType.VarChar));
    this.sqlCmd.Parameters["@field3"].Value = value;

    this.sqlCmd.Parameters.Add(new SqlParameter("@field4", SqlDbType.Int));
    this.sqlCmd.Parameters["@field4"].Value = value;

    this.sqlCmd.Parameters.Add(new SqlParameter("@field5", SqlDbType.VarChar));
    this.sqlCmd.Parameters["@field5"].Value = value;

    this.sqlCmd.Parameters.Add(new SqlParameter("@field6",SqlDbType.DateTime));
    this.sqlCmd.Parameters["@field6"].Value = value;

    this.sqlCmd.ExecuteNonQuery();
}
catch (Exception ex) { }

Answer 1

If thats the case maybe your temporary table is getting dropped....maybe due to a connection close call...also could you elaborate on what you are trying to do....

take a look at this link to know more about temporary tables

http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

Answer 2

try using this code instead, might help:

var Table = Cache["Info"] as DataTable;

if (Table != null)
{       
   ......

Though it is only syntactically different, but as your code is working if you put break point this may do the trick. It is just like restarting dev server or rebooting the machine :)

If its still not working please read this great post by Rick Strahl

EXAMPLE: I've setup two pages, on first one i'm adding values to datatable in cache (function AddClicked) and on second page I'm retrieving datatable and binding to a gridview and this is working fine.

private DataTable CreateTable()
{
    var dtb = new DataTable();
    dtb.Columns.Add(new DataColumn("SerialNo", typeof(int)));
    dtb.Columns.Add("Item");

    for (var i = 1; i < 11; i++)
        dtb.Rows.Add(new object[] { i, i + "" });

     Cache.Add("data", dtb, null, Cache.NoAbsoluteExpiration, 
        TimeSpan.FromMinutes(60), CacheItemPriority.Normal, null);

    return dtb;
}

protected void AddClick(object sender, EventArgs e)
{
    if (string.IsNullOrEmpty(txtItem.Text)) return;

    var table = Cache["data"] as DataTable;

    if (table == null) return;

    var i = table.Rows.Count + 1;

    table.Rows.Add(new object[] { i, txtItem.Text });

    BindData(table);
}

THIS IS ON SECOND PAGE

    private void BindData()
    {
        var dtb = Cache["data"] as DataTable;
        if (dtb == null) return;
        grdTest.DataSource = dtb;
        grdTest.DataBind();
    }

Answer 3

Caching is only temporary and may be dropped at any time.

You should use the session or application objects instead.

Answer 4

I got the sql exception. im getting the error - duplication of primary key.

thank for all ...:)