作为我的之前的问题:如何保持与数据库连接直到屏幕关闭?
首先,我要向大家道歉,我没有解释清楚我的情况。
好吧,我的情况是更新多达一百条记录。我使用
这里是性能等级。
好吧,我的情况是更新多达一百条记录。我使用
For循环
创建了一个真实的工作方式,并记录了它们的结果。private void button1_Click(object sender, EventArgs e)
{
int i;
KeyEventArgs keyEvent = new KeyEventArgs(Keys.Enter); //Create keydown event
Performance perf = new Performance(); //Class for measure time and logging
perf.Start(); //Start stopwatch
for (i = 1; i <= 100; i++)
{
txtLotNo.Text = i.ToString("0000000000") + "$01"; //Generate input ID
txtLotNo_KeyDown(sender, keyEvent); //Fire keydown event
}
perf.Stop(); //Stop stopwatch
perf.Log(frmInvCtrl.appPath,"Stock In (Stay connected)- " + frmInvCtrl.instance); //Logging
}
这里是性能等级。
class Performance
{
private Stopwatch _sw = new Stopwatch(); //Create stopwatch property
public double GetWatch
{
get
{
return this._sw.ElapsedMilliseconds;
}
}
public void Start()
{
Stop();
_sw.Reset();
_sw.Start();
}
public void Stop()
{
if (_sw.IsRunning)
{
_sw.Stop();
}
}
public void Log(string path,string menu)
{
string logName = path + "\\Log_" + System.DateTime.Now.ToString("yyyyMMdd") + ".txt";
string logDetail = System.DateTime.Now.ToString("yyyy/MM/dd hh:mm:ss") + " - [" + menu + "] "
+ "Process 100 record in [" + (((double)_sw.ElapsedMilliseconds / 1000)).ToString() + "] seconds";
using(StreamWriter writer = new StreamWriter(logName,true))
{
writer.WriteLine(logDetail); //wirtelog
}
}
}
以下是日志结果
2017/02/19 08:16:05 - [Stock In - On Cloud] Process 100 record in [68.352] seconds
2017/02/19 08:17:34 - [Stock In - On Cloud] Process 100 record in [70.184] seconds
2017/02/19 08:20:28 - [Stock In - On Cloud] Process 100 record in [56.66] seconds
2017/02/19 08:21:34 - [Stock In - On Cloud] Process 100 record in [60.605] seconds
2017/02/19 08:22:44 - [Stock In - On Cloud] Process 100 record in [68.27] seconds
2017/02/19 08:24:43 - [Stock In - Network Server] Process 100 record in [46.86] seconds
2017/02/19 08:26:05 - [Stock In - Network Server] Process 100 record in [31.746] seconds
2017/02/19 08:26:48 - [Stock In - Network Server] Process 100 record in [31.859] seconds
2017/02/19 08:27:32 - [Stock In - Network Server] Process 100 record in [31.003] seconds
2017/02/19 08:28:17 - [Stock In - Network Server] Process 100 record in [40.487] seconds
2017/02/19 08:32:42 - [Stock In (Stay connected)- On Cloud] Process 100 record in [18.196] seconds
2017/02/19 08:35:47 - [Stock In (Stay connected)- On Cloud] Process 100 record in [14.721] seconds
2017/02/19 08:36:30 - [Stock In (Stay connected)- On Cloud] Process 100 record in [15.903] seconds
2017/02/19 08:37:31 - [Stock In (Stay connected)- On Cloud] Process 100 record in [15.811] seconds
2017/02/19 08:38:15 - [Stock In (Stay connected)- On Cloud] Process 100 record in [16.4] seconds
2017/02/19 08:43:08 - [Stock In (Stay connected)- Network Server] Process 100 record in [13.09] seconds
2017/02/19 08:43:25 - [Stock In (Stay connected)- Network Server] Process 100 record in [13.03] seconds
2017/02/19 08:43:40 - [Stock In (Stay connected)- Network Server] Process 100 record in [13.051] seconds
2017/02/19 08:43:55 - [Stock In (Stay connected)- Network Server] Process 100 record in [12.992] seconds
2017/02/19 08:44:12 - [Stock In (Stay connected)- Network Server] Process 100 record in [14.953] seconds
我曾经练习过连接池技术。但是,结果表明在处理大量记录的情况下,保持与数据库的连接速度更快。
有没有适合这种情况的实践方法?
编辑:2017/02/21
这里是在打开表单代码时打开连接:
private void frm_Load(object sender, EventArgs e) //Open menu
{
... //statement
frmMain.sqlConn1 = new SqlConnection();
frmMain.sqlConn1.ConnectionString = frmMain.connectionString1;
frmMain.sqlConn1.Open();
... //statement
}
更新代码:
public static long ScanUpdate(string lotNo)
{
string scanLotNo = "";
int scanIndex = 0;
if (!SplitBarcode(lotNo, ref scanLotNo, ref scanIndex))
{
//Invalid Barcode data
return -919;
}
//Prepare sql command
string updStatus = (frmMain.shelfScan) ? "05" : "10";
string sql = <sql statement>
try
{
using (SqlCommand sqlCmd = new SqlCommand(sql, frmMain.sqlConn1)) //frmMain.sqlConn1 is connection in form_Load()
{
if (sqlCmd.ExecuteNonQuery() <= 0)
{
//No row affect
//frmMain.sndPlay.Play();
return -99;
}
else
{
//Completed
return 0;
}
}
}
catch
{
return 99;
}
finally
{
}
}
退出时处理连接
private void btnBack_Click(object sender, EventArgs e)
{
frmMain.sqlConn1.Dispose();
this.Close();
}
Close()
方法,以便连接返回到池中吗?你有测试过不使用池化并为每个更新操作打开一个新连接所需的时间吗? - C.EvenhuisForm_Load()
上打开连接,并在关闭窗体时进行Dispose。当我打开菜单时,只要菜单保持打开状态,我就可以使用此连接更新记录。而没有(保持连接),我使用using
块,在块结束后不调用Close()
,就像我之前的问题一样。 - Monkawee Maneewalaya