//获取连接字符串
public string myConn { get { return ConfigurationManager.ConnectionStrings["connStr"].ConnectionString; } } /// /// 执行增删改的SQL语句或存储过程 /// /// /// /// /// public int ExecuteNonQuery(string sql, SqlParameter[] paras, CommandType type) { SqlConnection conn = new SqlConnection(myConn); try { SqlCommand cmd = new SqlCommand(sql, conn); if (conn.State == ConnectionState.Closed) { conn.Open(); } if (paras != null && paras.Length > 0) { foreach (SqlParameter p in paras) { cmd.Parameters.Add(p); } } cmd.CommandType = type; int count = cmd.ExecuteNonQuery(); return count; } catch (Exception ex) { throw ex; } finally { conn.Close(); } } /// /// 执行查询的SQL语句或存储过程 /// /// /// /// /// public DataTable ExecuteTables(string sql, SqlParameter[] paras, CommandType type) { SqlConnection conn = new SqlConnection(myConn); SqlDataAdapter sda = new SqlDataAdapter(sql, conn); if (paras != null && paras.Length > 0) { foreach (SqlParameter p in paras) { sda.SelectCommand.Parameters.Add(p); } } sda.SelectCommand.CommandType = type; DataTable dt = new DataTable(); sda.Fill(dt); return dt; } /// /// 返回数据阅读器对象 /// /// /// /// /// public SqlDataReader ExecuteReader(string sql, SqlParameter[] paras, CommandType type) { SqlConnection conn = new SqlConnection(myConn); try { SqlCommand cmd = new SqlCommand(sql, conn); if (conn.State == ConnectionState.Closed) { conn.Open(); } if (paras != null && paras.Length > 0) { foreach (SqlParameter p in paras) { cmd.Parameters.Add(p); } } cmd.CommandType = type; SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); return sdr; } catch (Exception ex) { throw ex; } finally { conn.Close(); } } /// /// 执行首行首列的SQL语句或存储过程 /// /// /// /// /// public int ExecuteScalar(string sql, SqlParameter[] paras, CommandType type) { SqlConnection conn = new SqlConnection(myConn); try { SqlCommand cmd = new SqlCommand(sql, conn); if (conn.State == ConnectionState.Closed) { conn.Open(); } if (paras != null && paras.Length > 0) { foreach (SqlParameter p in paras) { cmd.Parameters.Add(p); } } cmd.CommandType = type; int count = Convert.ToInt32(cmd.ExecuteScalar()); return count; } catch (Exception ex) { throw ex; } finally { conn.Close(); } }