一、资源释放
在客户端代码中,创建一个数据库连接,它需要占用一定的系统资源,当操作完毕之后我们需要释放占用的系统资源,
当然,我们可以手动释放资源,具体实现如下:
var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN1"].ToString());
conn.Open();
//// This is a massive SQL injection vulnerability,
//// don't ever write your own SQL statements with string formatting!
string sql = String.Format(
@"INSERT INTO jk_users (user_login, user_pass, user_nicename, user_email, user_status,display_name, user_url, user_activation_key)
VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}')",
userLogin, userPass, userNicename, userEmail, userStatus, displayName, userUrl, userActivationKey);
var cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
//// If throws an exception on cmd dispose.
cmd.Dispose();
//// conn can't be disposed.
conn.Close();
conn.Dispose();
假如:在释放SqlCommand资源时抛出异常,那么在它后面的资源SqlConnection将得不到释放。
我们仔细想想当发生异常时,可以通过try/catch捕获异常,所以无论是否发生异常都可以使用finally检查资源是否已经释放了。
具体实现如下:
SqlCommand cmd = null;
SqlConnection conn = null;
try
{
//// Creates a database connection.
conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN1"].ToString());
conn.Open();
//// This is a massive SQL injection vulnerability,
//// don't ever write your own SQL statements with string formatting!
string sql = String.Format(
@"INSERT INTO jk_users (user_login, user_pass, user_nicename, user_email, user_status,display_name, user_url, user_activation_key)
VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}')",
userLogin, userPass, userNicename, userEmail, userStatus, displayName, userUrl, userActivationKey);
cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
}
finally
{
//// Regardless of whether there is an exception,
//// we will dispose the resource.
if (cmd != null) cmd.Dispose();
if (conn != null) conn.Dispose();
}
通过上面的finally方式处理了异常情况是很普遍的,但为了更安全释放资源,使得我们增加了finally和if语句,那么是否有更简洁的方法实现资源的安全释放呢?
其实,我们可以使用using语句实现资源的释放,具体实现如下:
using语句:定义一个范围,将在此范围之外释放一个或多个对象。
string sql = String.Format(
@"INSERT INTO jk_users (user_login, user_pass, user_nicename, user_email, user_status,display_name, user_url, user_activation_key)
VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}')",
userLogin, userPass, userNicename, userEmail, userStatus, displayName, userUrl, userActivationKey);
//// Creates a database connection.
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN1"].ToString()))
using (var cmd = new SqlCommand(sql, conn))
{
//// Your code here.
}
只有类型实现了IDisposable接口并且重写Dispose()方法可以使用using语句实现资源释放
由于SqlConnection和SqlCommand实现了IDisposable接口,那么我们可以使用using语句实现资源释放和异常处理。
二、SQL注入
在客户端代码中,我们使用拼接SQL语句方式实现数据写入,由于SQL语句是动态执行的,所以恶意用户可以通过拼接SQL的方式实施SQL注入攻击。
对于SQL注入攻击,我们可以通过以下方式防御:
1、正则表达校验用户输入
2、参数化存储过程
3、参数化SQL语句
4、添加数据库新架构
5、LINQ to SQL
接下来,我们将通过参数化SQL语句防御SQL注入攻击,大家也可以使用其他的方法防御SQL注入攻击,具体实现代码如下:
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN1"].ToString()))
{
conn.Open();
string sql = string.Format(
@"INSERT INTO jk_users (user_login, user_pass, user_nicename, user_email,
user_status,display_name, user_url, user_activation_key)");
using (var cmd = new SqlCommand(sql, conn))
{
//// Parameterized SQL to defense injection attacks
cmd.Parameters.Add("@user_login", userLogin);
cmd.Parameters.Add("@user_pass", userPass);
cmd.Parameters.Add("@user_nicename", userNicename);
cmd.Parameters.Add("@user_email", userEmail);
cmd.Parameters.Add("@user_status", userStatus);
cmd.Parameters.Add("@display_name", displayName);
cmd.Parameters.Add("@user_url", userUrl);
cmd.Parameters.Add("@user_activation_key", userActivationKey);
cmd.ExecuteNonQuery();
}
}
上面通过参数化SQL语句和using语句对代码进行改进,现在代码的可读性更强了,而且也避免了SQL注入攻击和资源释放等问题。
三、数据库事务
数据库事务是数据库管理系统执行过程中的一个逻辑单位,由一个有限的数据库操作序列构成。 SQL Server确保事务执行成功后,数据写入到数据库中,反之,事务将回滚。
如果我们对数据库进行十次独立的操作,那么SQL Server就需要分配十次锁开销,但如果把这些操作都封装在一个事务中,那么SQL Server只需要分配一次锁开销。
var sw = Stopwatch.StartNew();
Creates a database connection.
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN2"].ToString()))
{
conn.Open();
int cnt = 0;
SqlTransaction trans = conn.BeginTransaction();
while (cnt++ < 10000)
{
using (var cmd = new SqlCommand("SP_Insert_jk_users", conn))
{
Parameterized SQL to defense injection attacks
cmd.CommandType = CommandType.StoredProcedure;
Uses transcation to batch insert data.
To avoid lock and connection overhead.
cmd.Transaction = trans;
cmd.Parameters.Add("@user_login", userLogin);
cmd.Parameters.Add("@user_pass", userPass);
cmd.Parameters.Add("@user_nicename", userNicename);
cmd.Parameters.Add("@user_email", userEmail);
cmd.Parameters.Add("@user_status", userStatus);
cmd.Parameters.Add("@display_name", displayName);
cmd.Parameters.Add("@user_url", userUrl);
cmd.Parameters.Add("@user_activation_key", userActivationKey);
cmd.ExecuteNonQuery();
}
}
If no exception, commit transcation.
trans.Commit();
}
sw.Stop();
}
使用SqlBulkCopy
DataRow[] GetDataRows(int rowCnt)
{
//// Creates a custom table.
var dt = new DataTable("jk_users");
dt.Columns.Add(new DataColumn("user_login", typeof(System.String)));
dt.Columns.Add(new DataColumn("user_pass", typeof(System.String)));
dt.Columns.Add(new DataColumn("user_nicename", typeof(System.String)));
dt.Columns.Add(new DataColumn("user_email", typeof(System.String)));
dt.Columns.Add(new DataColumn("user_url", typeof(System.String)));
dt.Columns.Add(new DataColumn("user_registered", typeof(System.DateTime)));
dt.Columns.Add(new DataColumn("user_activation_key", typeof(System.String)));
dt.Columns.Add(new DataColumn("user_status", typeof(System.Int32)));
dt.Columns.Add(new DataColumn("display_name", typeof(System.String)));
//// Initializes data row.
var dr = dt.NewRow();
dr["user_login"] = "JK_RUSH";
dr["user_pass"] = "D*<1C2jK#-";
dr["user_nicename"] = "JK";
dr["user_email"] = "jkhuang@gamil.com";
dr["user_status"] = 1;
dr["display_name"] = "JK_RUSH";
dr["user_url"] = "http://www.cnblogs.com/rush";
dr["user_activation_key"] = "347894102386";
dr["user_registered"] = DateTime.Now;
//// Creates data row array.
var dataRows = new DataRow[rowCnt];
for (int i = 0; i < rowCnt; i++)
{
dataRows[i] = dr;
}
return dataRows;
}
定义了GetDataRows()方法用来创建数据行,首先我们创建了一个自定义表,给该表添加相应的数据列
这里我们把数据列都命名为对应于表中列名,当然,名字可以不一样,这时我们就有一个疑问了
那么数据库如何把自定义数据列和表中数据列对应起来呢?
其实,我们需要调用ColumnMappings.Add方法建立起自定义数据列和表中数据列的对应关系,接下来,我们调用SqlBulkCopy的WriteToServer()方法将数据行写入表中。
var dataRows = GetDataRows(10001);
var sw = Stopwatch.StartNew();
Creates a database connection.
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN2"].ToString()))
{
conn.Open();
using (var bulkCopy = new SqlBulkCopy(conn))
{
Maping the data columns.
bulkCopy.ColumnMappings.Add("user_login", "user_login");
bulkCopy.ColumnMappings.Add("user_pass", "user_pass");
bulkCopy.ColumnMappings.Add("user_nicename", "user_nicename");
bulkCopy.ColumnMappings.Add("user_email", "user_email");
bulkCopy.ColumnMappings.Add("user_url", "user_url");
bulkCopy.ColumnMappings.Add("user_registered", "user_registered");
bulkCopy.ColumnMappings.Add("user_activation_key", "user_activation_key");
bulkCopy.ColumnMappings.Add("user_status", "user_status");
bulkCopy.ColumnMappings.Add("display_name", "display_name");
bulkCopy.DestinationTableName = "dbo.jk_users";
Insert data into datatable.
bulkCopy.WriteToServer(dataRows);
}
sw.Stop();
}