C#SqlServer操作辅助类(SqlServerHelper.cs)
2020-11-09
来源：布克知识网
开发小型软件过程中，为了节约开发时间，数据库操作的代码自己都封装了。上篇介绍了SqlCeHelper这个关于操作本地数据库sdf的例子。现在介绍一下在Sqlserver中的代码。 因为Sqlserver数据库有多种登录方式，所以在构造函数中： #region [构造函数] /// summar
开发小型软件过程中，为了节约开发时间，数据库操作的代码自己都封装了。上篇介绍了SqlCeHelper这个关于操作本地数据库sdf的例子。现在介绍一下在Sqlserver中的代码。
因为Sqlserver数据库有多种登录方式，所以在构造函数中：
#region[构造函数] ////// 构造函数，初始化 /// /// 正确的数据库连接字符串 /// 超时时间 public SqlServerHelper(string ConStr, int TimeOut) { ConnectString = ConStr; connect = new SqlConnection(ConnectString); CommandTimeOut = TimeOut; } ////// 构造函数，初始化 /// /// 正确的数据库连接字符串 public SqlServerHelper(string ConStr) { ConnectString = ConStr; connect = new SqlConnection(ConnectString); CommandTimeOut = 30; } ////// 构造函数，初始化 /// /// 数据库服务器名称或地址 /// 访问的数据库 /// 用户名 /// 密码 /// 超时时间 public SqlServerHelper(string DataServer, string DataBase, string UserID, string Pwd,int TimeOut) { ConnectString = string.Format("Data Source={0};Initial Catalog={1};User ID={2};pwd={3}", DataServer, DataBase, UserID, Pwd); connect = new SqlConnection(ConnectString); CommandTimeOut = TimeOut; } ////// 构造函数，初始化 /// /// 数据库服务器名称或地址 /// 访问的数据库 /// 用户名 /// 密码 public SqlServerHelper(string DataServer, string DataBase, string UserID, string Pwd) { ConnectString = string.Format("Data Source={0};Initial Catalog={1};User ID={2};pwd={3}", DataServer, DataBase, UserID, Pwd); connect = new SqlConnection(ConnectString); CommandTimeOut = 30; } ////// 构造函数，初始化 /// /// 访问的数据库 /// 是否为本地数据库？若False，则抛出异常 /// 超时时间 public SqlServerHelper(string database, bool isLocal,int TimeOut) { if (!isLocal) { throw (new Exception("不在本地而是用了本地数据库访问方式，导致异常，请修改isLocal的值为True。")); } connectstring = string.Format("Data Source=127.0.0.1;Initial Catalog={0};Integrated Security=True", database); connect = new SqlConnection(ConnectString); CommandTimeOut = TimeOut; } ////// 构造函数，初始化 /// /// 访问的数据库 /// 是否为本地数据库？若False，则抛出异常 public SqlServerHelper(string database, bool isLocal) { if (!isLocal) { throw (new Exception("不在本地而是用了本地数据库访问方式，导致异常，请修改isLocal的值为True。")); } connectstring = string.Format("Data Source=127.0.0.1;Initial Catalog={0};Integrated Security=True", database); connect = new SqlConnection(ConnectString); CommandTimeOut = 30; } #endregion
这样，可以方便地构造出自己的连接字符串。
同时这里用到几个比较有用的有关数据库服务器及数据库结构的函数。比如查询局域网中所有数据库实例，获取目标实例所有数据库，获取指定数据库的所有表，获取指定表所有行：
View Code
#region 供使用API方式时使用
[DllImport("odbc32.dll")]
private static extern short SQLAllocHandle(short hType, IntPtr inputHandle, out IntPtr outputHandle);
[DllImport("odbc32.dll")]
private static extern short SQLSetEnvAttr(IntPtr henv, int attribute, IntPtr valuePtr, int strLength);
[DllImport("odbc32.dll")]
private static extern short SQLFreeHandle(short hType, IntPtr handle);
[DllImport("odbc32.dll", CharSet = System.Runtime.InteropServices.CharSet.Ansi)]
private static extern short SQLBrowseConnect(IntPtr hconn, System.Text.StringBuilder inString,
short inStringLength, System.Text.StringBuilder outString, short outStringLength,
out short outLengthNeeded);
private const short SQL_HANDLE_ENV = 1;
private const short SQL_HANDLE_DBC = 2;
private const int SQL_ATTR_ODBC_VERSION = 200;
private const int SQL_OV_ODBC3 = 3;
private const short SQL_SUCCESS = 0;
private const short SQL_NEED_DATA = 99;
private const short DEFAULT_RESULT_SIZE = 1024;
private const string SQL_DRIVER_STR = "DRIVER=SQL SERVER";
#endregion
///
/// 获取网内的数据库服务器名称(API方式)
///
/// 服务器名称数组
public static string[] GetServers()
{
string list = string.Empty;
IntPtr henv = IntPtr.Zero;
IntPtr hconn = IntPtr.Zero;
System.Text.StringBuilder inString = new System.Text.StringBuilder(SQL_DRIVER_STR);
System.Text.StringBuilder outString = new System.Text.StringBuilder(DEFAULT_RESULT_SIZE);
short inStringLength = (short)inString.Length;
short lenNeeded = 0;
try
{
if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_ENV, henv, out henv))
{
if (SQL_SUCCESS == SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (IntPtr)SQL_OV_ODBC3, 0))
{
if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_DBC, henv, out hconn))
{
if (SQL_NEED_DATA == SQLBrowseConnect(hconn, inString, inStringLength, outString,
DEFAULT_RESULT_SIZE, out lenNeeded))
{
if (DEFAULT_RESULT_SIZE < lenNeeded)
{
outString.Capacity = lenNeeded;
if (SQL_NEED_DATA != SQLBrowseConnect(hconn, inString, inStringLength, outString,
lenNeeded, out lenNeeded))
{
throw new ApplicationException("Unabled to aquire SQL Servers from ODBC driver.");
}
}
list = outString.ToString();
int start = list.IndexOf("{") + 1;
int len = list.IndexOf("}") - start;
if ((start > 0) && (len > 0))
{
list = list.Substring(start, len);
}
else
{
list = string.Empty;
}
}
}
}
}
}
catch
{
list = string.Empty;
}
finally
{
if (hconn != IntPtr.Zero)
{
SQLFreeHandle(SQL_HANDLE_DBC, hconn);
}
if (henv != IntPtr.Zero)
{
SQLFreeHandle(SQL_HANDLE_ENV, hconn);
}
}
string[] array = null;
if (list.Length > 0)
{
array = list.Split(',');
}
return array;
}
///
/// 获取网内的数据库服务器名称(qlClientFactory方式)
///
/// 服务器名称数组
public static string[] GetServersBySqlClientFactory()
{
DataTable dataSources = SqlClientFactory.Instance.CreateDataSourceEnumerator().GetDataSources();
DataColumn column2 = dataSources.Columns["ServerName"];
DataColumn column = dataSources.Columns["InstanceName"];
DataRowCollection rows = dataSources.Rows;
string[] array = new string[rows.Count];
for (int i = 0; i < array.Length; i++)
{
string str2 = rows[i][column2] as string;
string str = rows[i][column] as string;
if (((str == null) || (str.Length == 0)) || ("MSSQLSERVER" == str))
{
array[i] = str2;
}
else
{
array[i] = str2 + @"\" + str;
}
}
Array.Sort(array);
return array;
}
///
/// 根据不带数据库的连接字符串，遍历查找出所有数据库实例
///
/// 指定服务器的所有数据库
public string[] GetDataBases()
{
List list = new List();
SqlConnection sqlConnection = new SqlConnection(ConnectString);
string[] result;
try
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand("select name AS 数据库 from master..sysdatabases", sqlConnection);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
while (sqlDataReader.Read())
{
list.Add(sqlDataReader.GetString(0));
}
sqlDataReader.Close();
string[] array = new string[]
{
"master",
"tempdb",
"model",
"msdb"
};
string[] array2 = array;
for (int i = 0; i < array2.Length; i++)
{
string item = array2[i];
try
{
list.Remove(item);
}
catch
{
}
}
result = list.ToArray();
}
catch
{
result = list.ToArray();
}
finally
{
sqlConnection.Close();
}
return result;
}
///
/// 获取SqlServer指定数据库的所有表
///
/// 表集合,出错则产生异常
public string[] GetTables()
{
string sql = "select object_name (id) from sysobjects where xtype = 'u' and objectproperty (id,'IsMSShipped') = 0";
DataTable dt = ReturnDataTable(sql);
List Ls = new List();
for (int i = 0; i < dt.Rows.Count; i++)
{
Ls.Add(dt.Rows[i][0].ToString());
}
return Ls.ToArray();
}
///
/// 获取指定表的所有列
///
/// 表名
/// 列集合,出错则产生异常
public string[] GetColumns(string TableName)
{
string sql = string.Format("select name from syscolumns where id=object_id('{0}')",TableName);
try
{
List Ls = new List();
DataTable dt = ReturnDataTable(sql);
for (int i = 0; i < dt.Rows.Count; i++)
{
Ls.Add(dt.Rows[i][0].ToString());
}
return Ls.ToArray();
}
catch
{
throw;
}
}
接下来就是许多比较常用的增删改查的操作了。就不分开写了。
为了查看方便，贴出自己所有代码：
View Code
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Collections;
using System.Runtime.InteropServices;
namespace MyTool.DataBase
{
///
/// SQL Server 数据库的操作类库。代码原创。
///
public class SqlServerHelper
{
#region[字段]
private string connectstring = "Data Source=127.0.0.1;Initial Catalog=AdventureWorksDW;Integrated Security=True";
SqlConnection connect = null;
SqlCommand command = null;
private int CommandTimeOut = 30;
#endregion
#region[属性]
///
/// 数据库连接字符串
///
public string ConnectString
{
get { return connectstring; }
set { connectstring = value; }
}
#endregion
#region[构造函数]
///
/// 构造函数，初始化
///
/// 正确的数据库连接字符串
/// 超时时间
public SqlServerHelper(string ConStr, int TimeOut)
{
ConnectString = ConStr;
connect = new SqlConnection(ConnectString);
CommandTimeOut = TimeOut;
}
///
/// 构造函数，初始化
///
/// 正确的数据库连接字符串
public SqlServerHelper(string ConStr)
{
ConnectString = ConStr;
connect = new SqlConnection(ConnectString);
CommandTimeOut = 30;
}
///
/// 构造函数，初始化
///
/// 数据库服务器名称或地址
/// 访问的数据库
/// 用户名
/// 密码
/// 超时时间
public SqlServerHelper(string DataServer, string DataBase, string UserID, string Pwd,int TimeOut)
{
ConnectString = string.Format("Data Source={0};Initial Catalog={1};User ID={2};pwd={3}", DataServer, DataBase, UserID, Pwd);
connect = new SqlConnection(ConnectString);
CommandTimeOut = TimeOut;
}
///
/// 构造函数，初始化
///
/// 数据库服务器名称或地址
/// 访问的数据库
/// 用户名
/// 密码
public SqlServerHelper(string DataServer, string DataBase, string UserID, string Pwd)
{
ConnectString = string.Format("Data Source={0};Initial Catalog={1};User ID={2};pwd={3}", DataServer, DataBase, UserID, Pwd);
connect = new SqlConnection(ConnectString);
CommandTimeOut = 30;
}
///
/// 构造函数，初始化
///
/// 访问的数据库
/// 是否为本地数据库？若False，则抛出异常
/// 超时时间
public SqlServerHelper(string database, bool isLocal,int TimeOut)
{
if (!isLocal)
{
throw (new Exception("不在本地而是用了本地数据库访问方式，导致异常，请修改isLocal的值为True。"));
}
connectstring = string.Format("Data Source=127.0.0.1;Initial Catalog={0};Integrated Security=True", database);
connect = new SqlConnection(ConnectString);
CommandTimeOut = TimeOut;
}
///
/// 构造函数，初始化
///
/// 访问的数据库
/// 是否为本地数据库？若False，则抛出异常
public SqlServerHelper(string database, bool isLocal)
{
if (!isLocal)
{
throw (new Exception("不在本地而是用了本地数据库访问方式，导致异常，请修改isLocal的值为True。"));
}
connectstring = string.Format("Data Source=127.0.0.1;Initial Catalog={0};Integrated Security=True", database);
connect = new SqlConnection(ConnectString);
CommandTimeOut = 30;
}
#endregion
#region[私有函数]
private void Open()
{
try
{
if (connect.State != System.Data.ConnectionState.Open)
{
connect.Open();
}
}
catch (Exception ex)
{
throw (new Exception(ex.Message));
}
}
private void Close()
{
try
{
if (connect.State != System.Data.ConnectionState.Closed)
{
connect.Close();
}
}
catch (Exception ex)
{
throw (new Exception(ex.Message));
}
}
#endregion
///
/// 测试是否能够连通
///
/// 布尔值
public bool ConnectTest()
{
try
{
connect.Open();
}
catch
{
connect.Close();
return false;
}
return true;
}
///
/// 执行无返回的Sql语句，如插入，删除，更新
///
/// SQL语句
/// 受影响的条数
public int ExecuteNonQuery(string sqlstr)
{
try
{
Open();
command = new SqlCommand(sqlstr, connect);
int num = command.ExecuteNonQuery();
command.Parameters.Clear();
Close();
return num;
}
catch
{
throw;
}
}
///
/// 执行查询语句，返回DataSet
///
/// Sql
/// DataSet数据集
public DataSet ReturnDataSet(string sqlstr)
{
DataSet ds = new DataSet();
try
{
Open();
SqlDataAdapter adapter = new SqlDataAdapter(sqlstr, connect);
adapter.Fill(ds, "Obj");
}
catch (Exception)
{
throw;
}
return ds;
}
///
/// 执行查询语句，返回DataTable
///
/// Sqk
/// DataTable数据表
public DataTable ReturnDataTable(string sqlstr)
{
return ReturnDataSet(sqlstr).Tables[0];
}
///
/// 执行查询语句，返回DataReader
///
/// Sql
/// DataReader
public SqlDataReader ReturnDataReader(string sqlstr)
{
try
{
Open();
command = new SqlCommand(sqlstr, connect);
SqlDataReader myReader = command.ExecuteReader();
command.Parameters.Clear();
Close();
return myReader;
}
catch (System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);
}
}
///
/// 执行事务
///
///
public void ExecuteSqlTran(ArrayList SQLStringList)
{
Open();
command = new SqlCommand();
command.Connection = connect;
SqlTransaction tx = connect.BeginTransaction();
command.Transaction = tx;
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > 1)
{
command.CommandText = strsql;
command.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (Exception)
{
tx.Rollback();
throw;
}
}
#region 供使用API方式时使用
[DllImport("odbc32.dll")]
private static extern short SQLAllocHandle(short hType, IntPtr inputHandle, out IntPtr outputHandle);
[DllImport("odbc32.dll")]
private static extern short SQLSetEnvAttr(IntPtr henv, int attribute, IntPtr valuePtr, int strLength);
[DllImport("odbc32.dll")]
private static extern short SQLFreeHandle(short hType, IntPtr handle);
[DllImport("odbc32.dll", CharSet = System.Runtime.InteropServices.CharSet.Ansi)]
private static extern short SQLBrowseConnect(IntPtr hconn, System.Text.StringBuilder inString,
short inStringLength, System.Text.StringBuilder outString, short outStringLength,
out short outLengthNeeded);
private const short SQL_HANDLE_ENV = 1;
private const short SQL_HANDLE_DBC = 2;
private const int SQL_ATTR_ODBC_VERSION = 200;
private const int SQL_OV_ODBC3 = 3;
private const short SQL_SUCCESS = 0;
private const short SQL_NEED_DATA = 99;
private const short DEFAULT_RESULT_SIZE = 1024;
private const string SQL_DRIVER_STR = "DRIVER=SQL SERVER";
#endregion
///
/// 获取网内的数据库服务器名称(API方式)
///
/// 服务器名称数组
public static string[] GetServers()
{
string list = string.Empty;
IntPtr henv = IntPtr.Zero;
IntPtr hconn = IntPtr.Zero;
System.Text.StringBuilder inString = new System.Text.StringBuilder(SQL_DRIVER_STR);
System.Text.StringBuilder outString = new System.Text.StringBuilder(DEFAULT_RESULT_SIZE);
short inStringLength = (short)inString.Length;
short lenNeeded = 0;
try
{
if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_ENV, henv, out henv))
{
if (SQL_SUCCESS == SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (IntPtr)SQL_OV_ODBC3, 0))
{
if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_DBC, henv, out hconn))
{
if (SQL_NEED_DATA == SQLBrowseConnect(hconn, inString, inStringLength, outString,
DEFAULT_RESULT_SIZE, out lenNeeded))
{
if (DEFAULT_RESULT_SIZE < lenNeeded)
{
outString.Capacity = lenNeeded;
if (SQL_NEED_DATA != SQLBrowseConnect(hconn, inString, inStringLength, outString,
lenNeeded, out lenNeeded))
{
throw new ApplicationException("Unabled to aquire SQL Servers from ODBC driver.");
}
}
list = outString.ToString();
int start = list.IndexOf("{") + 1;
int len = list.IndexOf("}") - start;
if ((start > 0) && (len > 0))
{
list = list.Substring(start, len);
}
else
{
list = string.Empty;
}
}
}
}
}
}
catch
{
list = string.Empty;
}
finally
{
if (hconn != IntPtr.Zero)
{
SQLFreeHandle(SQL_HANDLE_DBC, hconn);
}
if (henv != IntPtr.Zero)
{
SQLFreeHandle(SQL_HANDLE_ENV, hconn);
}
}
string[] array = null;
if (list.Length > 0)
{
array = list.Split(',');
}
return array;
}
///
/// 获取网内的数据库服务器名称(qlClientFactory方式)
///
/// 服务器名称数组
public static string[] GetServersBySqlClientFactory()
{
DataTable dataSources = SqlClientFactory.Instance.CreateDataSourceEnumerator().GetDataSources();
DataColumn column2 = dataSources.Columns["ServerName"];
DataColumn column = dataSources.Columns["InstanceName"];
DataRowCollection rows = dataSources.Rows;
string[] array = new string[rows.Count];
for (int i = 0; i < array.Length; i++)
{
string str2 = rows[i][column2] as string;
string str = rows[i][column] as string;
if (((str == null) || (str.Length == 0)) || ("MSSQLSERVER" == str))
{
array[i] = str2;
}
else
{
array[i] = str2 + @"\" + str;
}
}
Array.Sort(array);
return array;
}
///
/// 根据不带数据库的连接字符串，遍历查找出所有数据库实例
///
/// 指定服务器的所有数据库
public string[] GetDataBases()
{
List list = new List();
SqlConnection sqlConnection = new SqlConnection(ConnectString);
string[] result;
try
{
sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand("select name AS 数据库 from master..sysdatabases", sqlConnection);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
while (sqlDataReader.Read())
{
list.Add(sqlDataReader.GetString(0));
}
sqlDataReader.Close();
string[] array = new string[]
{
"master",
"tempdb",
"model",
"msdb"
};
string[] array2 = array;
for (int i = 0; i < array2.Length; i++)
{
string item = array2[i];
try
{
list.Remove(item);
}
catch
{
}
}
result = list.ToArray();
}
catch
{
result = list.ToArray();
}
finally
{
sqlConnection.Close();
}
return result;
}
///
/// 获取SqlServer指定数据库的所有表
///
/// 表集合,出错则产生异常
public string[] GetTables()
{
string sql = "select object_name (id) from sysobjects where xtype = 'u' and objectproperty (id,'IsMSShipped') = 0";
DataTable dt = ReturnDataTable(sql);
List Ls = new List();
for (int i = 0; i < dt.Rows.Count; i++)
{
Ls.Add(dt.Rows[i][0].ToString());
}
return Ls.ToArray();
}
///
/// 获取指定表的所有列
///
/// 表名
/// 列集合,出错则产生异常
public string[] GetColumns(string TableName)
{
string sql = string.Format("select name from syscolumns where id=object_id('{0}')",TableName);
try
{
List Ls = new List();
DataTable dt = ReturnDataTable(sql);
for (int i = 0; i < dt.Rows.Count; i++)
{
Ls.Add(dt.Rows[i][0].ToString());
}
return Ls.ToArray();
}
catch
{
throw;
}
}
}
}