C#操作数据库的方法

string connStr = "server=.;uid=sa;pwd=123qweASD;database=Test";
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
conn.Open();
//向数据库添加一条数据
cmd.CommandText = "INSERT INTO [User](UserName) VALUES('李四')";
cmd.ExecuteNonQuery();//执行一个非查询语句,返回一个受影响的行数
Console.WriteLine("数据添加成功!");
}
}

 

ublic class sqlHelper
{
private static readonly string connStr = "server=.;uid=sa;pwd=123qweASD;database=my_ado";
/// <summary>
/// 返回数据库表格
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="type">类型</param>
/// <param name="pars">可选参数</param>
/// <returns>返回表格</returns>
public static DataTable GetTableDate(string sql, CommandType type, params SqlParameter[] pars)
{

using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlDataAdapter apter = new SqlDataAdapter(sql, conn))
{
if (pars != null)
{
apter.SelectCommand.Parameters.AddRange(pars);
}
apter.SelectCommand.CommandType = type;
DataTable da = new DataTable();
apter.Fill(da);
return da;
}
}
}
/// <summary>
/// 返回操作影响行数
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="type">类型</param>
/// <param name="pars">可选参数</param>
/// <returns>返回操作影响行数</returns>
public static int ExecuteQuery(string sql, CommandType type, params SqlParameter[] pars)
{
using(SqlConnection conn = new SqlConnection(connStr))
{
using(SqlCommand cmd = new SqlCommand(sql, conn))
{
if (pars != null)
{
cmd.Parameters.AddRange(pars);
}
cmd.CommandType = type;
conn.Open();
return cmd.ExecuteNonQuery();
}
}
}
}