连接式查询用法

/// <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) { //将参数集合加载到SelectCommand dataAdapter.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的同时释放Connection return 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; } } } }

}