c# SqlServer DbHelper类

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