在工程中,总是是遇到需要连接sqlser数据库,每次遇到都要面向百度编程一次,索性一次性直接写一个完整的sqlserer连接池工具。
不废话了,直接看代码。一切都在代码里。
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
namespace MES.APP
{
internal abstract class SqlServerConnectstring
{
/// <summary>
/// 数据库链接
/// </summary>
public static string connectstringMES = "Data source=127.0.0.1;Initial Catalog=master;User ID=sa;pwd=12345;";
}
internal class SqlServerHelper
{
#region[字段]
SqlConnection connect = null;
SqlCommand command = null;
private int CommandTimeOut = 300;
#endregion
#region[属性]
/// <summary>
/// 数据库连接字符串
/// </summary>
public string ConnectString { get; set; } = "Data Source=127.0.0.1;Initial Catalog=master;User ID=sa;pwd=123456;";
#endregion
#region[构造函数]
/// <summary>
/// 构造函数,初始化
/// </summary>
/// <param name="ConStr">正确的数据库连接字符串</param>
/// <param name="TimeOut">超时时间</param>
public SqlServerHelper(string ConStr, int TimeOut)
{
try
{
ConnectString = ConStr;
connect = new SqlConnection(ConnectString);
CommandTimeOut = TimeOut;
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// 构造函数,初始化
/// </summary>
/// <param name="ConStr">正确的数据库连接字符串</param>
public SqlServerHelper(string ConStr)
{
try
{
ConnectString = ConStr;
connect = new SqlConnection(ConnectString);
CommandTimeOut = 300;
}catch(Exception e)
{
throw e;
}
}
/// <summary>
/// 构造函数,初始化
/// </summary>
/// <param name="DataServer">数据库服务器名称或地址</param>
/// <param name="DataBase">访问的数据库</param>
/// <param name="UserID">用户名</param>
/// <param name="Pwd">密码</param>
/// <param name="TimeOut">超时时间</param>
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;
}
/// <summary>
/// 构造函数,初始化
/// </summary>
/// <param name="DataServer">数据库服务器名称或地址</param>
/// <param name="DataBase">访问的数据库</param>
/// <param name="UserID">用户名</param>
/// <param name="Pwd">密码</param>
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;
}
/// <summary>
/// 构造函数,初始化
/// </summary>
/// <param name="database">访问的数据库</param>
/// <param name="isLocal">是否为本地数据库?若False,则抛出异常</param>
/// <param name="TimeOut">超时时间</param>
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;
}
/// <summary>
/// 构造函数,初始化
/// </summary>
/// <param name="database">访问的数据库</param>
/// <param name="isLocal">是否为本地数据库?若False,则抛出异常</param>
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
/// <summary>
/// 测试是否能够连通
/// </summary>
/// <returns>布尔值</returns>
public bool ConnectTest()
{
try
{
connect.Open();
}
catch
{
connect.Close();
return false;
}
finally
{
connect.Close();
}
return true;
}
/// <summary>
/// 执行无返回的Sql语句,如插入,删除,更新
/// </summary>
/// <param name="sqlstr">SQL语句</param>
/// <returns>受影响的条数</returns>
public int ExecuteNonQuery(string sqlstr)
{
int num = 0;
try
{
Open();
command = new SqlCommand(sqlstr, connect);
num = command.ExecuteNonQuery();
command.Parameters.Clear();
Close();
}
catch (Exception ex)
{
throw;
}
finally
{
Close();
}
return num;
}
/// <summary>
/// 执行查询语句,返回DataSet
/// </summary>
/// <param name="sqlstr">Sql</param>
/// <returns>DataSet数据集</returns>
public DataSet ReturnDataSet(string sqlstr)
{
DataSet ds = new DataSet();
try
{
Open();
SqlDataAdapter adapter = new SqlDataAdapter(sqlstr, connect);
adapter.Fill(ds, "Obj");
}
catch (Exception)
{
throw;
}
finally
{
Close();
}
return ds;
}
/// <summary>
/// 执行查询语句,返回DataTable
/// </summary>
/// <param name="sqlstr">Sqk</param>
/// <returns>DataTable数据表</returns>
public DataTable ReturnDataTable(string sqlstr)
{
var ds = ReturnDataSet(sqlstr);
if (ds.Tables.Count == 0)
return null;
return ds.Tables[0];
}
/// <summary>
/// 执行查询语句,返回DataReader
/// </summary>
/// <param name="sqlstr">Sql</param>
/// <returns>DataReader</returns>
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);
}
finally
{
Close();
}
}
/// <summary>
/// 执行事务
/// </summary>
/// <param name="SQLStringList"></param>
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 e)
{
tx.Rollback();
throw e;
}
finally
{
Close();
}
}
/// <summary>
/// 获取网内的数据库服务器名称(sqlClientFactory方式)
/// </summary>
/// <returns>服务器名称数组</returns>
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<string>(array);
return array;
}
/// <summary>
/// 根据不带数据库的连接字符串,遍历查找出所有数据库实例
/// </summary>
/// <returns>指定服务器的所有数据库</returns>
public string[] GetDataBases()
{
List<string> list = new List<string>();
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;
}
/// <summary>
/// 获取SqlServer指定数据库的所有表
/// </summary>
/// <returns>表集合,出错则产生异常</returns>
public string[] GetTables()
{
string sql = "select object_name (id) from sysobjects where xtype = 'u' and objectproperty (id,'IsMSShipped') = 0";
DataTable dt = ReturnDataTable(sql);
List<string> Ls = new List<string>();
for (int i = 0; i < dt.Rows.Count; i++)
{
Ls.Add(dt.Rows[i][0].ToString());
}
return Ls.ToArray();
}
/// <summary>
/// 获取指定表的所有列
/// </summary>
/// <param name="TableName">表名</param>
/// <returns>列集合,出错则产生异常</returns>
public string[] GetColumns(string TableName)
{
string sql = string.Format("select name from syscolumns where id=object_id('{0}')", TableName);
try
{
List<string> Ls = new List<string>();
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;
}
}
}
}