数据库SQL Server性能优化之ADO.NET代码总结

刺骨的言语ヽ痛彻心扉 2022-05-31 12:35 217阅读 0赞

## 一、资源释放 ##

在客户端代码中，创建一个数据库连接，它需要占用一定的系统资源，当操作完毕之后我们需要释放占用的系统资源，

当然，我们可以手动释放资源，具体实现如下：

假如：在释放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只需要分配一次锁开销。

使用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();
    }