C#数据库帮助类SqlHelper

    using System;  
    using System.Collections.Generic;  
    using System.Linq;  
    using System.Web;  
    using System.Data.OleDb;  
    using System.Data;  
    using System.Configuration;  
    using System.Data.Sql;  
    using System.Data.SqlClient;  
    namespace DAL  
    {  
        public class SqlHelper  
        {  
            public static string connstring = ConfigurationManager.ConnectionStrings["zzconstr"].ConnectionString;  
            //public static string connstring = "Data Source=ZZ-PC;Initial Catalog=IPTVDB;User ID=sa;Password=sa";  
            /// <summary>  
            /// 执行非查询，返回受影响行数,异常返回-1；  
            /// </summary>  
            /// <param name="sql"></param>  
            /// <param name="type"></param>  
            /// <param name="pars"></param>  
            /// <returns></returns>  
            public static bool ExceNonQuery(string sql, CommandType type, IDataParameter[] pars)  
            {  
                  
                SqlConnection con = new SqlConnection(connstring);  
                SqlCommand com = new SqlCommand(sql, con);  
                  
                if (pars != null && pars.Length > 0)  
                {  
                    foreach (SqlParameter pp in pars)//把参数集全部加进去  
                        com.Parameters.Add(pp);  
                }  
                try  
                {  
                    con.Open();  
                    int t = com.ExecuteNonQuery();  
                    if (t > 0)  
                    {  
                        return true;  
                    }  
                    else  
                        return false;  
                }  
                catch (Exception e) { return false; }  
                finally  
                {  
                    com.Parameters.Clear();  
                    com.Dispose();  
                    con.Close();  
                }  
            }  
            /// <summary>  
            /// 执行sql语句的查询，返回查询的数量。异常返回-1.  
            /// </summary>  
            /// <param name="sql"></param>  
            /// <param name="type"></param>  
            /// <param name="pars"></param>  
            /// <returns></returns>  
            public static int ExceQuery(string sql, CommandType type, IDataParameter[] pars)  
            {  
                SqlConnection con = new SqlConnection(connstring);  
                SqlCommand com = new SqlCommand(sql, con);  
                com.CommandType = type;  
                if (pars != null && pars.Length > 0)  
                {  
                    foreach (SqlParameter pp in pars)//把参数集全部加进去  
                        com.Parameters.Add(pp);  
                }  
                try  
                {  
                    con.Open();  
                    if (com.ExecuteScalar() != null)//查询结果为空时返回0  
                    {  
                        int t = (int)com.ExecuteScalar();  
      
                        return t;  
                    }  
                    else  
                        return -1;  
                }  
                catch (Exception e) { return -1; }  
                finally  
                {  
                    com.Parameters.Clear();  
                    com.Dispose();  
                    con.Close();  
                }  
            }  
            /// <summary>  
            /// 执行查询，返回一个数据集  
            /// </summary>  
            /// <param name="sql"></param>  
            /// <param name="pars"></param>  
            /// <returns></returns>  
            public static DataSet ExcueReturnDataset(string sql, IDataParameter[] pars)  
            {  
                SqlConnection con = new SqlConnection(connstring);  
                DataSet set = new DataSet();  
                SqlCommand com = new SqlCommand(sql, con);  
                if (pars != null && pars.Length > 0)  
                {  
                    foreach (SqlParameter pp in pars)//把参数集全部加进去  
                        com.Parameters.Add(pp);  
                }  
                  
                SqlDataAdapter adpter = new SqlDataAdapter(com);  
                  
                try  
                {  
                    set.Clear();  
                    adpter.Fill(set);  
                    return set;  
                }  
                catch (Exception ex) { return null; }  
                finally {  
                    com.Parameters.Clear();  
                    com.Dispose();  
                    con.Close();   
                }  
      
            }  
            public static DataSet ExcueReturnDataset(string sql,CommandType type,IDataParameter[] pars)  
            {  
                SqlConnection con = new SqlConnection(connstring);  
                DataSet set = new DataSet();  
                SqlCommand com = new SqlCommand(sql, con);  
                com.CommandType = type;  
                if (pars != null && pars.Length > 0)  
                {  
                    foreach (SqlParameter pp in pars)//把参数集全部加进去  
                        com.Parameters.Add(pp);  
                }  
      
                SqlDataAdapter adpter = new SqlDataAdapter(com);  
      
                try  
                {  
                    set.Clear();  
                    adpter.Fill(set);  
                    return set;  
                }  
                catch (Exception ex) { return null; }  
                finally  
                {  
                    com.Parameters.Clear();  
                    com.Dispose();  
                    con.Close();  
                }  
      
            }  
            public static IDataReader ExcueReturnDataReader(string sql, IDataParameter[] pars)  
            {  
                SqlConnection con = new SqlConnection(connstring);  
                SqlCommand com = new SqlCommand(sql, con);  
                  
                SqlDataReader reader;  
                if (pars != null && pars.Length > 0)  
                {  
                    foreach (SqlParameter pp in pars)//把参数集全部加进去  
                        com.Parameters.Add(pp);  
                }  
                try  
                {  
                    con.Open();  
                    reader = com.ExecuteReader(CommandBehavior.CloseConnection);  
                    return reader;  
                }  
                catch (Exception ex)  
                {   
                      
                    return null;   
                }  
                finally {  
                    com.Parameters.Clear();  
                    com.Dispose();  
                    //con.Close();  
                }  
      
            }  
            /// <summary>  
            /// 执行存储过程，返回影响的行数        
            /// </summary>  
            /// <param name="storedProcName">存储过程名</param>  
            /// <param name="parameters">存储过程参数</param>  
            /// <param name="rowsAffected">影响的行数</param>  
            /// <returns></returns>  
            public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)  
            {  
                using (SqlConnection connection = new SqlConnection(connstring))  
                {  
                    int result;  
                    connection.Open();  
                    SqlCommand command = new SqlCommand(storedProcName, connection);  
                    command.CommandType = CommandType.StoredProcedure;  
                    if (parameters!= null && parameters.Length > 0)  
                    {  
                        foreach (SqlParameter pp in parameters)//把参数集全部加进去  
                            command.Parameters.Add(pp);  
                    }  
                    command.Parameters.Add("@return","").Direction = ParameterDirection.ReturnValue;  
                    rowsAffected = command.ExecuteNonQuery();  
                    result = (int)command.Parameters["@return"].Value;  
                    connection.Close();  
                    return result;  
                }  
            }  
            /// <summary>  
            /// 执行存储过程  
            /// </summary>  
            /// <param name="storedProcName">存储过程名</param>  
            /// <param name="parameters">存储过程参数</param>  
            /// <param name="tableName">DataSet结果中的表名</param>  
            /// <returns>DataSet</returns>  
            public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)  
            {  
                using (SqlConnection connection = new SqlConnection(connstring))  
                {  
                    DataSet dataSet = new DataSet();  
                    SqlCommand com = new SqlCommand(storedProcName, connection);  
                    com.CommandType =CommandType.StoredProcedure;  
                    if (parameters != null && parameters.Length > 0)  
                    {  
                        foreach (SqlParameter pp in parameters)//把参数集全部加进去  
                            com.Parameters.Add(pp);  
                    }  
      
                    SqlDataAdapter adpter = new SqlDataAdapter(com);  
                    adpter.Fill(dataSet, tableName);  
                    return dataSet;  
                }  
            }  
            /// <summary>  
            /// 执行查询语句，返回DataSet  
            /// </summary>  
            /// <param name="SQLString">查询语句</param>  
            /// <returns>DataSet</returns>  
            public DataSet Query(string SQLString)  
            {  
                using (SqlConnection connection = new SqlConnection(connstring))  
                {  
                    DataSet ds = new DataSet();  
                    try  
                    {  
                        connection.Open();  
                        SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);  
                        command.Fill(ds, "ds");  
                    }  
                    catch (System.Data.SqlClient.SqlException ex)  
                    {  
                        throw new Exception(ex.Message);  
                    }  
                    return ds;  
                }  
            }  
        }  
    }  

标签： isp

版权申明：本站文章部分自网络，如有侵权，请联系：west999com@outlook.com
特别注意：本站所有转载文章言论不代表本站观点！
本站所提供的图片等素材，版权归原作者所有，如需使用，请与原作者联系。