C#SQL操作

类库

System.Data.SqlClient;

步骤

0.整体代码呈现

// 连接字符串
private static readonly string conStr ="XXXX.COM;Initial Catalog=XXXX;Persist Security Info=True;User ID=XX;Password=XXXX"

 // 1.执行增(insert)、删(delete)、改(update)的方法 
 // ExecuteNonQuery()
 public static int ExecuteNonQuery(string sql, params SqlParameter[] pms)
 {
     using (SqlConnection con = new SqlConnection(conStr))
     {
         using (SqlCommand cmd = new SqlCommand(sql, con))
         {
             if (pms != null)
             {
                 cmd.Parameters.AddRange(pms);
             }
             try
             {
                 con.Open();
                 return cmd.ExecuteNonQuery();
             }
             catch (Exception)
             {
                 con.Close();
                 con.Dispose();
                 throw;
             }
         }
     }
 }

 // 2. 执行查询，返回单个值的方法
 // ExecuteScalar()
 public static object ExecuteScalar(string sql, params SqlParameter[] pms)
 {
     using (SqlConnection con = new SqlConnection(conStr))
     {
         using (SqlCommand cmd = new SqlCommand(sql, con))
         {
             if (pms != null)
             {
                 cmd.Parameters.AddRange(pms);
             }
             try
             {
                 con.Open();
                 return cmd.ExecuteScalar();
             }
             catch (Exception)
             {
                 con.Close();
                 con.Dispose();
                 throw;
             }
         }
     }
 }

 // 3. 执行查询，返回多行多列的方法
 // ExecuteReader()

 public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] pms)
 {
     SqlConnection con = new SqlConnection(conStr);

     using (SqlCommand cmd = new SqlCommand(sql, con))
     {
         if (pms != null)
         {
             cmd.Parameters.AddRange(pms);
         }
         try
         {
             con.Open();
             // System.Data.CommandBehavior.CloseConnection这个枚举参数表示
             // 在关闭SqlDataReader时，在SqlDataReader会把相关联的connnection关掉
             return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
         }
         catch (Exception)
         {
             con.Close();
             con.Dispose();
             throw;
         }
     }
 }


 public static DataTable ExecuteDataTable(string sql, params SqlParameter[] pms)
 {
     using (SqlConnection con = new SqlConnection(conStr))
     {
         // 构造适配器对象
         SqlDataAdapter adapter = new SqlDataAdapter(sql, con);
         DataTable dt = new DataTable();
         if (pms != null)
         {
             adapter.SelectCommand.Parameters.AddRange(pms);
         }
         try
         {
             // 执行结果
             adapter.Fill(dt);
             // 返回结果集
             return dt;
         }
         catch (Exception)
         {
             con.Close();
             con.Dispose();
             throw;
         }
     }
 }

1.连接字符串

 using (SqlConnection con = new SqlConnection(conStr))
 {
 }

2.执行命令

增删改

   using (SqlCommand cmd = new SqlCommand(sql, con))
   {
       if (pms != null)
       {
           cmd.Parameters.AddRange(pms);
       }
       con.Open();
       return cmd.ExecuteNonQuery();
   }

查

3.显示

注意

数据Null的转换

 /// <summary>
 /// 将值传入数据库时，如果值为NULL,则转换为数据库的DBNull
 /// </summary>
 /// <param name="value"></param>
 /// <returns></returns>
 public static object ToDbValue(object value)
 {
     if (value == null)
     {
         return DBNull.Value;
     }
     else
     {
         return value;
     }
 }

 /// <summary>
 /// 从数据库取值时，如果值为DBNull，则转换为null
 /// </summary>
 /// <param name="value"></param>
 /// <returns></returns>
 public static object FromDbVaule(object value)
 {
     return value == DBNull.Value ? null : value;
 }