简单C#数据库操作类-CSDN博客

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

/**//// <summary>
/// SqlHelper 的摘要说明
/// </summary>
public class SqlHelper
...{
    public SqlHelper()
    ...{
        //
        // TODO: 在此处添加构造函数逻辑
        //
    }
    /**//// <summary>
    /// 获取数据库连接字符串
    /// </summary>
    /// <returns></returns>
    private static string sqlConnectionString
    ...{
        get
        ...{
            //直接在这里获取数据库联接
            //return  "Data Source = (local); Initial Catalog=window;Integrated Security=SSPI;";
            //VS.NET2003下的使用方式
            //return System.Configuration.ConfigurationSettings.AppSettings["ConnectDataBase"];
            //VS.NET2005下的使用方式
            return System.Configuration.ConfigurationManager.AppSettings["ConnectDataBase"];
        }
    }
    /**//// <summary>
    /// 获取数据库连接
    /// </summary>
    /// <returns>Conn</returns>
    private static SqlConnection sqlConn
    ...{
        get
        ...{
            //VS.NET2003下的使用方式
            //return System.Configuration.ConfigurationSettings.AppSettings["ConnectDataBase"];
            //VS.NET2005下的使用方式
            //ConnStrings = System.Configuration.ConfigurationManager.AppSettings["ConnectDataBase"];
            string ConnStrings = "Data Source = (local); Initial Catalog=window;Integrated Security=SSPI;";
            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConnStrings;
            return Conn;
        }
    }
    /**//// <summary>
    /// 执行sql语句，返回DataSet
    /// </summary>
    /// <param name="sqlString">sql语句参数</param>
    /// <returns>DataSet</returns>
    public static DataSet ExecuteDataSet(string sqlString)
    ...{
        DataSet dsSet = new DataSet();
        SqlDataAdapter adp = new SqlDataAdapter(sqlString, sqlConnectionString);
        try
        ...{
            adp.Fill(dsSet);
        }
        catch (Exception e)
        ...{
            throw (e);
        }
        finally
        ...{
            adp.Dispose();
        }
        return dsSet;
    }
    /**//// <summary>
    /// 执行sql语句，返回DataTable
    /// </summary>
    /// <param name="sqlString">sql语句参数</param>
    /// <returns>DataTable</returns>
    public static DataTable ExecuteDataTable(string sqlString)
    ...{
        DataTable dt = new DataTable();
        SqlDataAdapter adp = new SqlDataAdapter(sqlString, sqlConnectionString);
        try
        ...{
            adp.Fill(dt);
        }
        catch (Exception e)
        ...{
            throw (e);
        }
        finally
        ...{
            adp.Dispose();
        }
        return dt;
    }
    /**//// <summary>
    /// 执行存储过程返回DataSet
    /// </summary>
    /// <param name="spName">存储过程名称</param>
    /// <param name="commandParameters">存储过程参数</param>
    /// <returns>DataSet</returns>
    public static DataSet ExecuteDataSet(string spName, SqlParameter[] commandParameters)
    ...{
        SqlConnection conn = sqlConn;
        conn.Open();
        SqlCommand sqlcommand = new SqlCommand();
        SqlDataAdapter adapter = new SqlDataAdapter();
        DataSet dataSet = new DataSet();
        sqlcommand.Connection = conn;
        sqlcommand.CommandText = spName;
        sqlcommand.CommandType = CommandType.StoredProcedure;
        if (commandParameters != null)
        ...{
            foreach (SqlParameter p in commandParameters)
            ...{
                if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
                ...{
                    p.Value = DBNull.Value;
                }
                sqlcommand.Parameters.Add(p);
            }
        }
        adapter.SelectCommand = sqlcommand;
        try
        ...{
            adapter.Fill(dataSet);
        }
        catch (Exception e)
        ...{
            throw (e);
        }
        finally
        ...{
            sqlcommand.Parameters.Clear();
            sqlcommand.Dispose();
            adapter.Dispose();
            conn.Close();
        }
        return dataSet;
    }
    /**//// <summary>
    /// 执行存储过程返回DataTable
    /// </summary>
    /// <param name="spName">存储过程名称</param>
    /// <param name="commandParameters">存储过程参数</param>
    /// <returns>DataTable</returns>
    public static DataTable ExecuteDataTable(string spName, SqlParameter[] commandParameters)
    ...{
        SqlConnection conn = sqlConn;
        conn.Open();
        SqlCommand sqlcommand = new SqlCommand();
        SqlDataAdapter adapter = new SqlDataAdapter();
        DataTable dataTable = new DataTable();
        sqlcommand.Connection = conn;
        sqlcommand.CommandText = spName;
        sqlcommand.CommandType = CommandType.StoredProcedure;
        if (commandParameters != null)
        ...{
            foreach (SqlParameter p in commandParameters)
            ...{
                if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
                ...{
                    p.Value = DBNull.Value;
                }
                sqlcommand.Parameters.Add(p);
            }
        }
        adapter.SelectCommand = sqlcommand;
        try
        ...{
            adapter.Fill(dataTable);
        }
        catch (Exception e)
        ...{
            throw (e);
        }
        finally
        ...{
            sqlcommand.Parameters.Clear();
            sqlcommand.Dispose();
            adapter.Dispose();
            conn.Close();
        }
        return dataTable;
    }
    /**//// <summary>
    /// 执行存储过程
    /// </summary>
    /// <param name="spName">存储过程名称</param>
    /// <param name="commandParameters">存储过程参数</param>
    /// <returns>true or false</returns>
    public static bool ExecuteProcedure(string spName, SqlParameter[] commandParameters)
    ...{
        bool result = false;
        SqlConnection conn = sqlConn;
        conn.Open();
        SqlCommand sqlcommand = new SqlCommand();
        sqlcommand.Connection = conn;
        sqlcommand.CommandText = spName;
        sqlcommand.CommandType = CommandType.StoredProcedure;
        if (commandParameters != null)
        ...{
            foreach (SqlParameter p in commandParameters)
            ...{
                if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
                ...{
                    p.Value = DBNull.Value;
                }
                sqlcommand.Parameters.Add(p);
            }
        }
        try
        ...{
            sqlcommand.ExecuteNonQuery();
            result = true;
        }
        catch (Exception e)
        ...{
            throw (e);
        }
        finally
        ...{
            sqlcommand.Parameters.Clear();
            sqlcommand.Dispose();
            conn.Close();
        }
        return result;
    }
    /**//// <summary>
    /// 执行存储过程返回一个object对象
    /// </summary>
    /// <param name="spName">存储过程名称</param>
    /// <param name="commandParameters">存储过程参数</param>
    /// <returns>object</returns>
    public static object ExecuteProcedures(string spName, SqlParameter[] commandParameters)
    ...{
        object ret = new object();
        ret = DBNull.Value;
        SqlConnection conn = sqlConn;
        conn.Open();
        SqlCommand sqlcommand = new SqlCommand();

          sqlcommand.Connection = conn;//2009/2/27丢掉的Connection连接
        sqlcommand.CommandText = spName;
        sqlcommand.CommandType = CommandType.StoredProcedure;
        if (commandParameters != null)
        ...{
            foreach (SqlParameter p in commandParameters)
            ...{
                if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
                ...{
                    p.Value = DBNull.Value;
                }
                sqlcommand.Parameters.Add(p);
            }
        }
        try
        ...{
            ret = sqlcommand.ExecuteScalar();
        }
        catch (Exception e)
        ...{
            throw (e);
        }
        finally
        ...{
            sqlcommand.Parameters.Clear();
            sqlcommand.Dispose();
            conn.Close();
        }
        return ret;
    }
    /**//// <summary>
    /// 执行sql语句，返回一个object对象
    /// </summary>
    /// <param name="sqlString">自定义sql语句</param>
    /// <returns>object</returns>
    public static object ExecuteScalar(string sqlString)
    ...{
        object ret = new object();
        ret = DBNull.Value;
        SqlConnection conn = sqlConn;
        SqlCommand sqlcommand = new SqlCommand(sqlString, conn);
        try
        ...{
            ret = sqlcommand.ExecuteScalar();
        }
        catch (Exception e)
        ...{
            throw (e);
        }
        finally
        ...{
            sqlcommand.Dispose();
            conn.Close();
        }
        return ret;
    }
    /**//// <summary>
    /// 执行自定义sql语句
    /// </summary>
    /// <param name="sqlString">自定sql语句</param>
    /// <returns>true or false</returns>
    public static bool ExecuteNoQueryString(string sqlString)
    ...{
        bool result = false;
        SqlCommand sqlcommand = new SqlCommand();
        SqlConnection conn = new SqlConnection();
        conn.Open();
        sqlcommand.Connection = conn;
        try
        ...{
            sqlcommand.ExecuteScalar();
            result = true;
        }
        catch
        ...{
            result = false;
        }
        finally
        ...{
            sqlcommand.Dispose();
            conn.Close();
        }
        return result;
    }
}

这个文件在网上应该是很流行的。不做解释，记录在案以后使用。

下边是调用存储过程的方法带参数的：

        public bool InsertUsers(Users user)
        {
            SqlParameter[] parms = {
                new SqlParameter("@UserName",user.userName),
                new SqlParameter("@UserPass",user.userPass),
                new SqlParameter("@UserRole",user.userRole),
                new SqlParameter("@UserEmail",user.userEmail),
                new SqlParameter("@Remark",user.remark)
            };
            return SQLHelper.ExecuteProcedure("tfwk_InsertUser", parms);
        }

转载于:https://www.cnblogs.com/mushaobai/archive/2009/02/26/1398606.html