c# SqlServer DbHelper类

来源：互联网发布：v2ex源码搭建编辑：程序博客网时间：2024/05/16 16:10

DbHelper.cs

internal class DbHelper{    public static String GetConnectionString(ConnectionStringPattern pattern)    {        String connectionString = String.Empty;        switch (pattern)        {            case ConnectionStringPattern.ByString:                connectionString = "Data Source=.;Initial Catalog=MyDatebase;Integrated Security=True";                break;            case ConnectionStringPattern.BySqlConnectionStringBuilder:                SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder();                sqlBuilder.DataSource = ".";                sqlBuilder.InitialCatalog = "MyDatebase";                sqlBuilder.IntegratedSecurity = false;                sqlBuilder.UserID = "sa";                sqlBuilder.Password = "Hn1234";                connectionString = sqlBuilder.ConnectionString;                break;            case ConnectionStringPattern.ByConfiguration:                connectionString = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;                break;            default:                throw new Exception("Pattern Error");        }        return connectionString;    }    public static SqlDataReader SqlReadExecute(String commendText, SqlParameter[] sqlParameter)    {        SqlConnection conn = new SqlConnection();        conn.ConnectionString = DbHelper.GetConnectionString(ConnectionStringPattern.BySqlConnectionStringBuilder);        conn.Open();        SqlCommand command = new SqlCommand(commendText, conn);        if (sqlParameter != null)        {            command.Parameters.AddRange(sqlParameter);        }        Console.WriteLine("CommandText:" + command.CommandText);        try        {            SqlDataReader reader = command.ExecuteReader();            Console.WriteLine("读取成功：");            return reader;        }        catch (Exception ex)        {            Console.WriteLine("读取失败：" + ex.Message);            return null;        }    }    public static void SqlWriteExecute(String commendText, SqlParameter[] sqlParameter)    {        using (SqlConnection conn = new SqlConnection())        {            conn.ConnectionString = DbHelper.GetConnectionString(ConnectionStringPattern.BySqlConnectionStringBuilder);            conn.Open();            Console.WriteLine(conn.State.ToString());            SqlCommand command = new SqlCommand(commendText, conn);            if (sqlParameter != null)            {                command.Parameters.AddRange(sqlParameter);            }            Console.WriteLine("CommandText:" + command.CommandText);            try            {                int affectRows = command.ExecuteNonQuery();                if (affectRows > 0)                {                    Console.WriteLine("操作成功：");                }                else                {                    Console.WriteLine("操作失败： 没有找到要更改数据 ");                }            }            catch (Exception ex)            {                Console.WriteLine("操作失败： 写入失败" + ex.Message);            }        }    }}//登录类型public enum ConnectionStringPattern{    ByString,    BySqlConnectionStringBuilder,    ByConfiguration}

下面列举两个读写时的例子

public static void AddEmployee(Employee employee){    String commandText = "insert into staff(id,name,gender,department,job) values(@id,@name,@gender,@department,@job)";    SqlParameter[] paras = new SqlParameter[]    {                new SqlParameter("@id",SqlDbType.Int) { Value = employee.id},                new SqlParameter("@name",SqlDbType.VarChar) { Value = employee.name},                new SqlParameter("@gender",SqlDbType.VarChar) { Value = employee.gender},                new SqlParameter("@department",SqlDbType.VarChar){ Value = employee.department},                new SqlParameter("@job",SqlDbType.VarChar) { Value = employee.job}    };    DbHelper.SqlWriteExecute(commandText, paras);}public static void FindAllStaff(){    string commandText = "select a.id,a.name,a.gender,a.department,a.job, b.name as chief from staff as a, staff as b,department where b.id in(select staffid from staff,manage where staff.department = manage.department and job = '科长')";    SqlParameter[] paras = null;    using (SqlDataReader reader = DbHelper.SqlReadExecute(commandText, paras))    {        while (reader.Read())        {            Console.Write(reader["id"] + " ");            Console.Write(reader["name"] + " ");            Console.Write(reader["gender"] + " ");            Console.Write(reader["department"] + " ");            Console.Write(reader["job"] + " ");            Console.Write(reader["chief"] + " ");            Console.WriteLine();        }        Console.WriteLine();    }}

注：添加SqlParameter时要指定类型
如new SqlParameter(“@id”,SqlDbType.Int) { Value = employee.id}
如果写成 new SqlParameter(“@id”,employee.id); 会报@id无法转换成int的错误
参考 http://www.cnblogs.com/lzrabbit/archive/2012/04/22/2465313.html

0 0