C#读写SQL　Server的操作，仅作为记录

public static string connStr = "Server=127.0.0.1;Database=WJB;User Id=sa;Password=XXXXXX";
/// <summary>
/// 根据SQL语句返回所查询的DataTable对像，有参数
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="param">SqlParameter参数</param>
/// <param name="type"></param>
/// <returns></returns>
public static DataTable GetTable(string sql, List<SqlParameter> param, CommandType type = CommandType.Text)
{
    DataTable dt = new DataTable();
    using (SqlConnection con = new SqlConnection(connStr))
    {
        using (SqlCommand cmd = new SqlCommand(sql, con))
        {
            con.Open();
            if (param != null)
            {
                cmd.Parameters.AddRange(param.ToArray());
            }
            cmd.CommandType = type;
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                da.Fill(dt);
            }
        }
    }
    return dt;
}
/// <summary>
/// 根据SQL语句返回所查询的DataTable对像,无参数
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="type"></param>
/// <returns></returns>
public static DataTable GetTable(string sql,CommandType type = CommandType.Text)
{
    DataTable dt = new DataTable();
    using (SqlConnection con = new SqlConnection(connStr))
    {
        using (SqlCommand cmd = new SqlCommand(sql, con))
        {
            con.Open();
            cmd.CommandType = type;
            using (SqlDataAdapter da = new SqlDataAdapter(cmd))
            {
                da.Fill(dt);
            }
        }
    }
    return dt;
}
/// <summary>
/// 根据SQL语句查询只有一个返回结果的数据
/// </summary>
/// <param name="sql">查询的SQL语句</param>
/// <param name="type"></param>
/// <returns></returns>
public static string GetOneSQL(string sql, CommandType type = CommandType.Text)
{
    string mes="";
    using (SqlConnection con = new SqlConnection(connStr))
    {
        using (SqlCommand cmd = new SqlCommand(sql, con))
        {
            con.Open();
            cmd.CommandType = type;
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    mes = reader[0].ToString();
                }
            }
        }
    }
    return mes;
}
/// <summary>
/// 根据SQL语句查询只有一个返回结果的数据,只能用来查询count(*)等有明确返回结果且为数字的数据
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int GetOneSQL(string sql) 
{
    int num = 0;
    num = Convert.ToInt32(GetOneSQL(sql,CommandType.Text));
    return num;
}


/// <summary>
/// 根据SQL语句更新数据，返回受影响的行数，有参数
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="param">SqlParameter参数</param>
/// <param name="type"></param>
/// <returns></returns>
public static int UpdateDB(string sql, List<SqlParameter> param, CommandType type = CommandType.Text)
{
    using (SqlConnection con = new SqlConnection(connStr))
    {
        using (SqlCommand cmd = new SqlCommand(sql, con))
        {
            con.Open();
            if (param != null)
            {
                cmd.Parameters.AddRange(param.ToArray());
            }
            cmd.CommandType = type;
            return cmd.ExecuteNonQuery();
        }
    }
}
/// <summary>
/// 根据SQL语句更新数据，返回受影响的行数
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="type"></param>
/// <returns></returns>
public static int UpdateDB(string sql, CommandType type = CommandType.Text)
{
    using (SqlConnection con = new SqlConnection(connStr))
    {
        using (SqlCommand cmd = new SqlCommand(sql, con))
        {
            con.Open();
            cmd.CommandType = type;
            return cmd.ExecuteNonQuery();
        }
    }
}