在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();
}
为多个插入、更新和删除创建一个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);
试着写出这样的代码。
这将减少您的系统开销。