使用c#语言,写一个最全的sqlserver帮助类
时间: 2024-03-27 19:38:32 浏览: 11
好的,以下是一个使用 C# 语言编写的 SQL Server 帮助类,包含了常用的 SQL Server 操作,如连接、查询、插入、更新、删除等。
```csharp
using System;
using System.Data;
using System.Data.SqlClient;
public class SqlHelper
{
private string connectionString = ""; // 数据库连接字符串
public SqlHelper(string connectionString)
{
this.connectionString = connectionString;
}
// 执行查询,并返回结果集
public DataSet Query(string sql)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
adapter.Fill(ds);
}
catch (SqlException ex)
{
throw new Exception(ex.Message);
}
finally
{
connection.Close();
}
return ds;
}
}
// 执行查询,并返回单个值
public object QueryScalar(string sql)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
object result = null;
try
{
connection.Open();
SqlCommand command = new SqlCommand(sql, connection);
result = command.ExecuteScalar();
}
catch (SqlException ex)
{
throw new Exception(ex.Message);
}
finally
{
connection.Close();
}
return result;
}
}
// 执行插入、更新、删除操作
public int ExecuteNonQuery(string sql)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
int rowsAffected = 0;
try
{
connection.Open();
SqlCommand command = new SqlCommand(sql, connection);
rowsAffected = command.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw new Exception(ex.Message);
}
finally
{
connection.Close();
}
return rowsAffected;
}
}
// 执行存储过程,并返回结果集
public DataSet ExecuteProcedure(string procedureName, SqlParameter[] parameters)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
SqlCommand command = new SqlCommand(procedureName, connection);
command.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(ds);
}
catch (SqlException ex)
{
throw new Exception(ex.Message);
}
finally
{
connection.Close();
}
return ds;
}
}
// 执行事务
public int ExecuteTransaction(string[] sqls)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlTransaction transaction = null;
int rowsAffected = 0;
try
{
connection.Open();
transaction = connection.BeginTransaction();
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.Transaction = transaction;
foreach (string sql in sqls)
{
command.CommandText = sql;
rowsAffected += command.ExecuteNonQuery();
}
transaction.Commit();
}
catch (SqlException ex)
{
if (transaction != null)
{
transaction.Rollback();
}
throw new Exception(ex.Message);
}
finally
{
connection.Close();
}
return rowsAffected;
}
}
}
```
这个帮助类包含了常用的 SQL Server 操作,可用于快速开发 SQL Server 数据库应用程序。注意,在使用之前需要替换 `connectionString` 变量的值为你的数据库连接字符串。