在c# 2008中将数据从DataGridView保存到Sql Server 2008需要花费太多时间

本文关键字:2008 Server 时间 太多 Sql 保存 数据 DataGridView | 更新日期: 2023-09-27 18:10:07

我有一个DataGridView有303行和11列。当我要保存所有这些数据到我的sql表它需要30-40秒。是否可以在1-2秒内保存此数据?我的代码是这样的

public SqlConnection  conn;
SqlTransaction transaction;
string strconn = "data source=nabid;Persist Security Info=false;database=dbTest;user id=sa ;password=123;Connection Timeout = 10000";
conn = new SqlConnection(strconn);
conn.Open();
transaction = conn.BeginTransaction();
            try
            {
                if (DataGridView1.Rows.Count > 0)
                {
                    clsProd.DeleteRecord("[tbl1]", "");
                    foreach (DataGridViewRow r in DataGridView1.Rows)
                    {
                        if (r.Cells[0].Value != null)
                        {
                            Ticker = r.Cells[1].Value.ToString().Trim();
                            string values = "'" + tt.Trim() + "',";
                            values += "'" + r.Cells[1].Value.ToString().Trim() + "',"; // Ticker
                            values += (r.Cells[2].Value.ToString().Trim() == "" ? 0 : Convert.ToDouble(r.Cells[2].Value.ToString().Trim())) + ",";    // LTP                        
                            values += Convert.ToDouble("0") + ","; // Open
                            values += (r.Cells[3].Value.ToString().Trim() == "" ? 0 : Convert.ToDouble(r.Cells[3].Value.ToString().Trim())) + ","; // High
                            values += (r.Cells[4].Value.ToString().Trim() == "" ? 0 : Convert.ToDouble(r.Cells[4].Value.ToString().Trim())) + ","; // LOW
                            values += (r.Cells[5].Value.ToString().Trim() == "" ? 0 : Convert.ToDouble(r.Cells[5].Value.ToString().Trim())) + ","; // Close
                            values += (r.Cells[6].Value.ToString().Trim() == "" ? 0 : Convert.ToDouble(r.Cells[6].Value.ToString().Trim())) + ","; // YCP
                            if (r.Cells[7].Value.ToString().Trim() == "--" || r.Cells[7].Value.ToString().Trim() == "")
                                chg_Prc = "0";
                            else
                                chg_Prc = r.Cells[7].Value.ToString().Trim();
                            values += Convert.ToDouble(chg_Prc.Trim()) + ","; // Change
                            values += (r.Cells[8].Value.ToString().Trim() == "" ? 0 : Convert.ToDouble(r.Cells[8].Value.ToString().Trim())) + ","; // Trade No
                            values += (r.Cells[9].Value.ToString().Trim() == "" ? 0 : Convert.ToDouble(r.Cells[9].Value.ToString().Trim())) + ","; // Volume
                            values += Convert.ToDouble("0") + ",";
                            if (r.Cells[2].Value.ToString().Trim() == "0.0" || r.Cells[2].Value.ToString().Trim() == "0.00" || r.Cells[2].Value.ToString().Trim() == "0")
                                tradeStatus = "N";
                            else
                                tradeStatus = "Y";
                            values += "'" + tradeStatus.Trim() + "',0";
                            addRecord("[tbl1]", values);
                        }
                    }
                    closeConnection();
                this.Cursor = Cursors.Default;
            }
            catch (Exception exx)
            {
                this.Cursor = Cursors.Default;
        errorTransaction();
                return;
            }
protected void ExecuteSQL(string sSQL)
        {
            SqlCommand cmd = new SqlCommand(sSQL, conn, transaction);
            cmd.ExecuteNonQuery();            
        }
public void DeleteRecord(string tblName, string values)
        {
            string sSQL = "DELETE FROM " + tblName + " " + values;
            ExecuteSQL(sSQL);
        }
public void addRecord(string tblName, string values)
        {
            string sSQL = "INSERT INTO " + tblName + " VALUES(" + values + ")";
            ExecuteSQL(sSQL);
        }
public void closeConnection()
        {
            transaction.Commit();
            conn.Close();
        }
public void errorTransaction()
        {
            transaction.Rollback();
            conn.Close();
        }

在c# 2008中将数据从DataGridView保存到Sql Server 2008需要花费太多时间

为多个插入、更新和删除创建一个sql查询字符串,而不是调用函数并给程序带来开销。

     string sqlInsert="";
        foreach (DataGridViewRow r in DataGridView1.Rows)
         {
         if (r.Cells[0].Value != null)
                                {
                                    Ticker = r.Cells[1].Value.ToString().Trim();
                                    string values = "'" + tt.Trim() + "',";
                                    values += "'" + r.Cells[1].Value.ToString().Trim() + "',"; // Ticker
                                    values += (r.Cells[2].Value.ToString().Trim() == "" ? 0 : Convert.ToDouble(r.Cells[2].Value.ToString().Trim())) + ",";    // LTP                        
                                    values += Convert.ToDouble("0") + ","; // Open
                                    values += (r.Cells[3].Value.ToString().Trim() == "" ? 0 : Convert.ToDouble(r.Cells[3].Value.ToString().Trim())) + ","; // High
                                    values += (r.Cells[4].Value.ToString().Trim() == "" ? 0 : Convert.ToDouble(r.Cells[4].Value.ToString().Trim())) + ","; // LOW
                                    values += (r.Cells[5].Value.ToString().Trim() == "" ? 0 : Convert.ToDouble(r.Cells[5].Value.ToString().Trim())) + ","; // Close
                                    values += (r.Cells[6].Value.ToString().Trim() == "" ? 0 : Convert.ToDouble(r.Cells[6].Value.ToString().Trim())) + ","; // YCP
                                    if (r.Cells[7].Value.ToString().Trim() == "--" || r.Cells[7].Value.ToString().Trim() == "")
                                        chg_Prc = "0";
                                    else
                                        chg_Prc = r.Cells[7].Value.ToString().Trim();
                                    values += Convert.ToDouble(chg_Prc.Trim()) + ","; // Change
                                    values += (r.Cells[8].Value.ToString().Trim() == "" ? 0 : Convert.ToDouble(r.Cells[8].Value.ToString().Trim())) + ","; // Trade No
                                    values += (r.Cells[9].Value.ToString().Trim() == "" ? 0 : Convert.ToDouble(r.Cells[9].Value.ToString().Trim())) + ","; // Volume
                                    values += Convert.ToDouble("0") + ",";
                                    if (r.Cells[2].Value.ToString().Trim() == "0.0" || r.Cells[2].Value.ToString().Trim() == "0.00" || r.Cells[2].Value.ToString().Trim() == "0")
                                        tradeStatus = "N";
                                    else
                                        tradeStatus = "Y";
                                    values += "'" + tradeStatus.Trim() + "',0";
                                    sqlInsert=sqlInsert+"Insert into " + tblName + " VALUES(" + values + ")";
                                }
                            }
ExecuteSQL(sqlInsert);

试着写出这样的代码。

这将减少您的系统开销。