C#写一套最全的SQL server帮助类（包括增删改查） - IT评测·应用市场-qidao123.com

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
public class SqlserverHelper
{
private static string connectionString = "Data Source=服务器名称;Initial Catalog=数据库名称;User ID=用户名;Password=密码"; // 数据库连接字符串
/// <summary>
/// 执行指定的SQL语句并返回受影响的行数
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <returns>受影响的行数</returns>
public static int ExecuteNonQuery(string sql)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open(); // 打开数据库连接
SqlCommand cmd = new SqlCommand(sql, conn);
int count = cmd.ExecuteNonQuery(); // 执行SQL语句并返回受影响的行数
return count; // 返回受影响的行数
}
}
/// <summary>
/// 执行指定的SQL语句并返回查询结果的第一行第一列
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <returns>查询结果的第一行第一列</returns>
public static object ExecuteScalar(string sql)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open(); // 打开数据库连接
SqlCommand cmd = new SqlCommand(sql, conn);
object obj = cmd.ExecuteScalar(); // 执行SQL语句并返回查询结果的第一行第一列
return obj; // 返回查询结果的第一行第一列
}
}
/// <summary>
/// 执行指定的SQL语句并返回一个数据表
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <returns>查询结果的数据表</returns>
public static DataTable ExecuteDataTable(string sql)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open(); // 打开数据库连接
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
adapter.Fill(ds); // 执行SQL语句并将查询结果填充到DataSet对象中
return ds.Tables[0]; // 返回查询结果的数据表
}
}
/// <summary>
/// 执行指定的SQL语句并返回一个DataReader对象
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <returns>查询结果的DataReader对象</returns>
public static SqlDataReader ExecuteReader(string sql)
{
SqlConnection conn = new SqlConnection(connectionString);
conn.Open(); // 打开数据库连接
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); // 执行SQL语句并返回DataReader对象
return reader; // 返回查询结果的DataReader对象
}
/// <summary>
/// 向指定的表中插入一条记录，并返回新记录的ID值（如果有自增字段）
/// </summary>
/// <param name="tableName">要插入记录的表名称</param>
/// <param name="fields">要插入记录的字段和值的集合</param>
/// <returns>新记录的ID值（如果有自增字段）</returns>
public static int Insert(string tableName, Dictionary<string, object> fields)
{
string sql = "insert into " + tableName + "(";
string values = ") values (";
SqlCommand cmd = new SqlCommand();
foreach (KeyValuePair<string, object> field in fields)
{
sql += "[" + field.Key + "],";
values += "@" + field.Key + ",";
cmd.Parameters.AddWithValue("@" + field.Key, field.Value);
}
sql = sql.TrimEnd(',') + values.TrimEnd(',') + ")";
sql += ";select @@identity"; // 如果有自增字段，则返回新记录的ID值
cmd.Connection = new SqlConnection(connectionString);
cmd.CommandText = sql;
cmd.Connection.Open(); // 打开数据库连接
int newId;
if (cmd.ExecuteScalar() != DBNull.Value)
{
newId = Convert.ToInt32(cmd.ExecuteScalar());
}
else
{
newId = 0; // 如果没有自增字段，则返回0
}
cmd.Connection.Close(); // 关闭数据库连接
return newId; // 返回新记录的ID值
}
/// <summary>
/// 更新指定表中满足条件的记录，并返回受影响的行数
/// </summary>
/// <param name="tableName">要更新记录的表名称</param>
/// <param name="fields">要更新的字段和值的集合</param>
/// <param name="conditions">更新条件的字段和值的集合</param>
/// <returns>受影响的行数</returns>
public static int Update(string tableName, Dictionary<string, object> fields, Dictionary<string, object> conditions)
{
string sql = "update " + tableName + " set ";
SqlCommand cmd = new SqlCommand();
foreach (KeyValuePair<string, object> field in fields)
{
sql += "[" + field.Key + "]=@" + field.Key + ",";
cmd.Parameters.AddWithValue("@" + field.Key, field.Value);
}
sql = sql.TrimEnd(',');
if (conditions != null && conditions.Count > 0)
{
sql += " where ";
foreach (KeyValuePair<string, object> condition in conditions)
{
sql += "[" + condition.Key + "]=@" + condition.Key + " and ";
cmd.Parameters.AddWithValue("@" + condition.Key, condition.Value);
}
sql = sql.TrimEnd(' ', 'a', 'n', 'd');
}
cmd.Connection = new SqlConnection(connectionString);
cmd.CommandText = sql;
cmd.Connection.Open(); // 打开数据库连接
int count = cmd.ExecuteNonQuery(); // 执行SQL语句并返回受影响的行数
cmd.Connection.Close(); // 关闭数据库连接
return count; // 返回受影响的行数
}
/// <summary>
/// 删除指定表中满足条件的记录，并返回受影响的行数
/// </summary>
/// <param name="tableName">要删除记录的表名称</param>
/// <param name="conditions">删除条件的字段和值的集合</param>
/// <returns>受影响的行数</returns>
public static int Delete(string tableName, Dictionary<string, object> conditions)
{
string sql = "delete from " + tableName;
SqlCommand cmd = new SqlCommand();
if (conditions != null && conditions.Count > 0)
{
sql += " where ";
foreach (KeyValuePair<string, object> condition in conditions)
{
sql += "[" + condition.Key + "]=@" + condition.Key + " and ";
cmd.Parameters.AddWithValue("@" + condition.Key, condition.Value);
}
sql = sql.TrimEnd(' ', 'a', 'n', 'd');
}
cmd.Connection = new SqlConnection(connectionString);
cmd.CommandText = sql;
cmd.Connection.Open(); // 打开数据库连接
int count = cmd.ExecuteNonQuery(); // 执行SQL语句并返回受影响的行数
cmd.Connection.Close(); // 关闭数据库连接
return count; // 返回受影响的行数
}
}

复制代码

static void Main(string[] args)
{
// 执行指定的SQL语句并返回受影响的行数
int count = SqlserverHelper.ExecuteNonQuery("insert into [User](UserName, Password) values('admin', '123456')");
Console.WriteLine("受影响的行数：" + count);
// 执行指定的SQL语句并返回查询结果的第一行第一列
object obj = SqlserverHelper.ExecuteScalar("select count(*) from [User]");
Console.WriteLine("记录总数：" + obj);
// 执行指定的SQL语句并返回一个数据表
DataTable dt = SqlserverHelper.ExecuteDataTable("select * from [User] where UserName='admin'");
foreach (DataRow row in dt.Rows)
{
Console.WriteLine("用户名：" + row["UserName"] + " 密码：" + row["Password"]);
}
// 执行指定的SQL语句并返回一个DataReader对象
SqlDataReader reader = SqlserverHelper.ExecuteReader("select * from [User]");
while (reader.Read())
{
Console.WriteLine("用户名：" + reader["UserName"] + " 密码：" + reader["Password"]);
}
reader.Close();
// 向指定的表中插入一条记录，并返回新记录的ID值（如果有自增字段）
Dictionary<string, object> fields = new Dictionary<string, object>();
fields.Add("UserName", "test");
fields.Add("Password", "123456");
int newId = SqlserverHelper.Insert("User", fields);
Console.WriteLine("新记录的ID值：" + newId);
// 更新指定表中满足条件的记录，并返回受影响的行数
Dictionary<string, object> conditions = new Dictionary<string, object>();
conditions.Add("UserName", "test");
Dictionary<string, object> newFields = new Dictionary<string, object>();
newFields.Add("Password", "654321");
int updateCount = SqlserverHelper.Update("User", newFields, conditions);
Console.WriteLine("更新记录的数量：" + updateCount);
// 删除指定表中满足条件的记录，并返回受影响的行数
Dictionary<string, object> delConditions = new Dictionary<string, object>();
delConditions.Add("UserName", "test");
int delCount = SqlserverHelper.Delete("User", delConditions);
Console.WriteLine("删除记录的数量：" + delCount);
Console.ReadKey();
}

复制代码