SqlHelper

 

 

   public static class SqlHelper
    {
        //准备一个连接字符串
        private static string connStr = ConfigurationManager.ConnectionStrings["myconnStr"].ConnectionString;

        //private static string connStr = ConfigurationManager.ConnectionStrings["MyTestConnectionString1"].ConnectionString;

 

        /// <summary>
        /// (1)执行一个存储过程得到一个DataTable对象
        /// </summary>
        /// <param name="Procname"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static DataTable ExecuteProc(string Procname, params SqlParameter[] paras)
        {
            SqlConnection con = new SqlConnection(SqlHelper.connStr);
            SqlDataAdapter da = new SqlDataAdapter(Procname, con);
            da.SelectCommand.Parameters.AddRange(paras);

            da.SelectCommand.CommandType = CommandType.StoredProcedure;//设置使用存储过程
            DataTable table = new DataTable();

            da.Fill(table);//把得到的DataTable对象存放到适配器中
            return table;//返回得到的DataTable对象

        }

        /// <summary>
        /// (2)执行一个没有参数的存储过程得到一个DataTable对象
        /// </summary>
        /// <param name="Procname"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static DataTable ExecuteProcNoQuery(string Procname)
        {
            SqlConnection con = new SqlConnection(SqlHelper.connStr);
            SqlDataAdapter da = new SqlDataAdapter(Procname, con);
            //da.SelectCommand.Parameters.AddRange(paras);

            da.SelectCommand.CommandType = CommandType.StoredProcedure;//设置使用存储过程
            DataTable table = new DataTable();

            da.Fill(table);//把得到的DataTable对象存放到适配器中
            return table;//返回得到的DataTable对象

        }

 

        /// <summary>
        /// (3)执行一个没有参数的存储过程得到一个int返回值
        /// (之所以用返回值是int类型是为了在执行增删改的时候做个判断)
        /// </summary>
        /// <param name="Procname"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static int ExecuteProcNoQueryInt(string Procname)
        {
            SqlConnection con = new SqlConnection(SqlHelper.connStr);
            SqlDataAdapter da = new SqlDataAdapter(Procname, con);
            //da.SelectCommand.Parameters.AddRange(paras);

            da.SelectCommand.CommandType = CommandType.StoredProcedure;//设置使用存储过程
            DataTable table = new DataTable();

            da.Fill(table);//把得到的DataTable对象存放到适配器中
            return table.Rows.Count;//返回得到的DataTable中数据行数

        }

 

       

        /// <summary>
        /// (4)执行存储过程的增删改操作,返回值为int类型参数
        /// </summary>
        /// <param name="Procname"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static int ExecuteNonQueryProcInt(string Procname, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {

                    cmd.CommandType = CommandType.StoredProcedure;//设置为存储过程
                    cmd.CommandText = Procname;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteNonQuery();
                }
            }
        }

 

 

        /// <summary>
        /// (5)如果从数据库中取到的数据是NULL的时候，则转化为C#中的null类型
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static object FromDbValue(object value)
        {
            if (value == DBNull.Value)
            {
                return null;
            }
            else
            {
                return value;
            }
        }

 

 

        /// <summary>
        /// (6)如果数据为Null的话，则把数据转化为数据库中的DBNULL类型
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static object ToDbValue(object value)
        {
            if (value == null)
            {
                return DBNull.Value;
            }
            else
            {
                return value;
            }
        }

 

 

        //(7)执行增删改操作
        public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteNonQuery();
                }
            }
        }

 

        
        /// <summary>
        /// (8)执行带参数的查询操作
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteScalar();
                }
            }
        }

 

 

        /// <summary>
        /// (9)执行不带参数的查询操作
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    //cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteScalar();
                }
            }
        }

 

 

        //(10)返回一个DataTable的方法
        public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameters);
                    DataSet dataset = new DataSet();
                    SqlDataAdapter apdater = new SqlDataAdapter(cmd);
                    apdater.Fill(dataset);
                    return  dataset.Tables[0];
                }
            }
        }

 

 

 

        /// <summary>
        /// (11)执行存储过程，返回一个数据表
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static DataTable ExcuteProctest(string procName, params SqlParameter[] paras)
        {
            SqlConnection conn = new SqlConnection(SqlHelper.connStr);
            SqlDataAdapter da = new SqlDataAdapter(procName, conn);
            da.SelectCommand.Parameters.AddRange(paras);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;//设置 使用存储过程

            DataTable dt = new DataTable();
            //查询数据，并将返回的数据装入数据表dt
            da.Fill(dt);
            return dt;
        }

 

 

        /// <summary>
        /// (12)执行存储过程，返回一个数据表
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static DataTable ExcuteProctestTable(string procName, params SqlParameter[] paras)
        {
            SqlConnection conn = new SqlConnection(SqlHelper.connStr);
            SqlDataAdapter da = new SqlDataAdapter(procName, conn);
            da.SelectCommand.Parameters.AddRange(paras);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;//设置 使用存储过程

            DataTable dt = new DataTable();
            //查询数据，并将返回的数据装入数据表dt
            da.Fill(dt);
            return dt;
        }

 

 

        /// <summary>
        /// (13)执行存储过程，返回一个数据集
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static DataSet ExcuteProctestDataSet(string procName)
        {
            SqlConnection conn = new SqlConnection(SqlHelper.connStr);
            SqlDataAdapter da = new SqlDataAdapter(procName, conn);
            //da.SelectCommand.Parameters.AddRange(paras);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;//设置 使用存储过程

            //DataTable dt = new DataTable();
            //da.Fill(dt);
            //return dt;

            DataSet ds = new DataSet();
            //查询数据，并将返回的数据装入数据表dt
            da.Fill(ds);
            return ds;

        }

 

     //(14)连接数据库
        public static SqlConnection GetConnection()
        {
            //temp="data source = (local);initial catalog = test; user id = sa;password = 111";
            //StreamReader sr = File.OpenText(@"1.txt");
            //string temp = sr.ReadToEnd();

            SqlConnection myConn = new SqlConnection();
            myConn.ConnectionString = connStr;
            return myConn;
        }

 

        //(15)查询数据库
        public static DataSet GetDataSetStr(string sqlStr)
        {
            SqlConnection myConn = GetConnection();
            myConn.Open();
            DataSet ds = new DataSet();
            SqlDataAdapter adapt = new SqlDataAdapter(sqlStr, myConn);

            adapt.Fill(ds);
            myConn.Close();
            return ds;
        }

        //(16)非查询数据库，即增，删，改，查数据库
        public static bool notQuery(string sql)
        {
            SqlConnection conn3 = new SqlConnection(connStr);
            conn3.Open();
            SqlCommand cmd = new SqlCommand(sql, conn3);
            try
            {
                cmd.ExecuteNonQuery();
                return true;

            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                conn3.Close();
            }

        }

 
 

 

 

    }