.Net平台下的SqlHelper

来源：互联网发布：期货软件的不足编辑：程序博客网时间：2024/05/20 23:57
class SqlHelper    {        //通过ConfigurationManager可以读到App.config中的配置信息        private static string conStr = ConfigurationManager.ConnectionStrings["dbConStr"].ConnectionString;        /// <summary>        /// 将null转换成数据库中的DBNull        /// </summary>        /// <param name="value"></param>        /// <returns></returns>        public static object ToDBValue(object value)        {            if (value == null)                value = DBNull.Value;            return value;        }        /// <summary>        /// 从数据库中获得DBNull数据转换成null        /// </summary>        /// <param name="value"></param>        /// <returns></returns>        public static object FromDBValue(object value)        {            if (value == DBNull.Value)                value = null;            return value;        }        /// <summary>        /// 执行不查询sql语句        /// </summary>        /// <param name="sql">要执行的sql语句</param>        /// <returns>执行完sql语句后，数据库受影响的行数</returns>        public static int ExecuteNonQuery(string sql,params SqlParameter[] parameters)        {            using (SqlConnection sqlCon = new SqlConnection(conStr))            {                sqlCon.Open();                using (SqlCommand sqlCmd = sqlCon.CreateCommand())                {                    sqlCmd.CommandText = sql;                    //foreach (SqlParameter param in parameters)                    //{                    //    sqlCmd.Parameters.Add(param);                    //}                    sqlCmd.Parameters.AddRange(parameters);                    return sqlCmd.ExecuteNonQuery();                }            }        }        /// <summary>        /// 执行查询        /// </summary>        /// <param name="sql">要执行的sql语句</param>        /// <returns>返回查询结果中的第一行第一列</returns>        public static object ExecuteScaler(string sql,params SqlParameter[] parameters)        {            using (SqlConnection sqlCon = new SqlConnection(conStr))            {                sqlCon.Open();                using (SqlCommand sqlCmd = sqlCon.CreateCommand())                {                    sqlCmd.CommandText = sql;                    sqlCmd.Parameters.AddRange(parameters);                    return sqlCmd.ExecuteScalar();                }            }        }        /// <summary>        /// 执行离线数据集查询        /// </summary>        /// <param name="sql">要执行的sql语句</param>        /// <returns>返回离线数据集</returns>        public static DataTable ExecuteDataTable(string sql,params SqlParameter[] parameters)        {            using (SqlConnection sqlCon = new SqlConnection(conStr))            {                sqlCon.Open();                using (SqlCommand sqlCmd = sqlCon.CreateCommand())                {                    sqlCmd.CommandText = sql;                    sqlCmd.Parameters.AddRange(parameters);                    SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCmd);                    DataSet dataSet = new DataSet();                    sqlAdapter.Fill(dataSet);                    return dataSet.Tables[0];                }            }        }        /// <summary>        /// 执行存储过程        /// </summary>        /// <param name="storedProc"></param>        /// <param name="parameters"></param>        /// <returns></returns>        public static int ExecuteStoredProc(string storedProc,params SqlParameter[] parameters)        {            using (SqlConnection conn = new SqlConnection(conStr))            {                using (SqlCommand sqlCmd = new SqlCommand(storedProc, conn))                {                    //sql命令为存储过程                    sqlCmd.CommandType = CommandType.StoredProcedure;                    //添加参数                    sqlCmd.Parameters.AddRange(parameters);                    //晚打开，早关闭                    conn.Open();                    return sqlCmd.ExecuteNonQuery();                }            }        }    }
0 0