连接式查询运用

 /// <summary>/// 数据库连接字符串，从配置文件读取/// </summary>private static readonly string strConn = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;/// <summary>/// 执行查询（非连接式），将查询结果全部加载到内存中/// </summary>/// <param name="sql">SQL语句</param>/// <param name="parameters">SQL参数集合</param>/// <returns>包含查询结果的表</returns>public static DataTable ExecQuery(string sql, params SqlParameter[] parameters){//using加载完之后自动关闭using (SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, strConn)){using (DataTable table = new DataTable()){//判断参数中是否有值if (parameters != null && parameters.Count() > 0){//将参数集合加载到SelectCommanddataAdapter.SelectCommand.Parameters.AddRange(parameters);}try{dataAdapter.Fill(table);}catch (Exception ex) //执行出错，释放资源{table.Dispose();dataAdapter.Dispose();//抛出异常throw ex;}return table;}}}

执行查询（连接式），逐行读取查询结果，使用完毕后，一定不要忘记Reader.Close()；

 public static SqlDataReader ExecReader(string sql, params SqlParameter[] parameters){//创建连接对象//【为什么不能使用using？】SqlConnection sqlConnection = new SqlConnection(strConn);using (SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection)){//判断参数中是否有值if (parameters != null && parameters.Count() > 0){sqlCommand.Parameters.AddRange(parameters);}try{sqlConnection.Open();//将Reader与Connection进行绑定，关闭Reader的同时释放Connectionreturn sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);}catch (Exception ex) //执行出错，释放资源{sqlCommand.Dispose();sqlConnection.Close();sqlConnection.Dispose();throw ex;}}}

执行非查询（增删改）语句

 public static int ExecNonQuery(string sql, params SqlParameter[] parameters){using (SqlConnection sqlConnection = new SqlConnection(strConn)){using (SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection)){if (parameters != null && parameters.Count() > 0){sqlCommand.Parameters.AddRange(parameters);}try{sqlConnection.Open();//返回受影响的行数return sqlCommand.ExecuteNonQuery();}catch (Exception ex){sqlCommand.Dispose();sqlConnection.Close();sqlConnection.Dispose();throw ex;}}}}

执行查询，返回查询结果第一行第一列的值，常用于查询SUM、COUNT等聚合函数的结果

 public static object ExecScalar(string sql, params SqlParameter[] parameters){using (SqlConnection sqlConnection = new SqlConnection(strConn)){using (SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection)){if (parameters != null && parameters.Count() > 0){sqlCommand.Parameters.AddRange(parameters);}try{sqlConnection.Open();return sqlCommand.ExecuteScalar();}catch (Exception ex){sqlCommand.Dispose();sqlConnection.Close();sqlConnection.Dispose();throw ex;}}}}
}