ADO.Net 操作 SqlServer 2012 数据库封装
一共包括MyDbTool_SqlServer.cs和Program.cs以及App.config 三个文件
MyDbTool_SqlServer.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace ADO.NET练习
{
class MyDbTool_SqlServer
{
private string DBtype = null;
private SqlConnection SqlConn = null;
private string SqlConnStr = null;//记录连接服务器字符串
private SqlCommand SqlCommand = null;
public MyDbTool_SqlServer(string inType)
{
this.DBtype = inType;
switch (DBtype.ToUpper())
{
case "LFC0001":
//SqlConnStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=LFC0001)));User Id=sa;Password=123456;";
//SqlConnStr = "server=.;uid=sa;pwd=123456;database=LFC0001;"//连接池默认是开启的
SqlConnStr = "server=.;uid=sa;pwd=123456;database=LFC0001;pooling=true";
//SqlConnStr = "data source=.;user id=sa;password=123456;initial catalog=LFC0001;pooling=true ;min pool size=4";
break;
case "": ; break;
}
}
#region DB的关闭\连接\连接状态的获取
/// <summary>
/// 连接指定数据库
/// </summary>
public void OpenConnection()
{
try
{
SqlConn = new SqlConnection();
SqlConn.ConnectionString = SqlConnStr;
if (SqlConn.State == System.Data.ConnectionState.Closed)
{
//环境变量 设置
//System.Environment.SetEnvironmentVariable("nls_lang", "AMERICAN_AMERICA.WE8ISO8859P1");
SqlConn.Open();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// 关闭当前数据库连接
/// </summary>
public void CloseConnection()
{
SqlConn.Close();
SqlConn.Dispose();//释放连接资源
}
/// <summary>
/// 获取当前数据库连接状态
/// </summary>
/// <returns></returns>
public string GetConnectionState()
{
return SqlConn.State.ToString();
}
#endregion
#region DB的查询操作
/// <summary>
/// 在數據庫中查詢一個數值。(需連接數據庫和處理異常)
/// </summary>
/// <param name="strQuery">查询语句</param>
/// <returns>查詢數值對象</returns>
public Object ExecuteScalarQuery(string strQuery)
{
SqlCommand cmd = new SqlCommand(strQuery);
cmd.Connection = SqlConn;
cmd.CommandType = CommandType.Text;
Object result = cmd.ExecuteScalar();
return result;
}
/// <summary>
/// 在數據庫中查詢一個數值。(外部不需連接數據庫和處理異常)
/// </summary>
/// <param name="strQuery">查询语句</param>
/// <returns>查詢數值對象</returns>
public Object LazyExecuteScalarQuery(string strQuery)
{
try
{
OpenConnection();
return ExecuteScalarQuery(strQuery);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.StackTrace);
return null;
}
finally
{
CloseConnection();
}
}
/// <summary>
/// 使用DataReader存放SELECT查詢結果,不緩存到內存。(需連接數據庫和處理異常)
/// </summary>
/// <param name="strQuery">sql語句</param>
/// <returns></returns>
public SqlDataReader ExecuteNoCacheQuery(string strQuery)
{
SqlCommand cmd = new SqlCommand(strQuery);
cmd.Connection = SqlConn;
cmd.CommandType = CommandType.Text;
SqlDataReader reader = cmd.ExecuteReader();
cmd.Dispose();
return reader;
}
/// <summary>
/// 使用DataReader存放SELECT查詢結果,不緩存到內存。(外部不需連接數據庫和處理異常)
/// </summary>
/// <param name="strQuery">sql語句</param>
/// <returns></returns>
public SqlDataReader LazyExecuteNoCacheQuery(string strQuery)
{
try
{
OpenConnection();
return ExecuteNoCacheQuery(strQuery);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.StackTrace);
return null;
}
finally
{
//loseConnection();//SqlDataReader使用的时候得保持连接数据库不关闭
}
}
/// <summary>
/// 執行SELECT,將查詢結果存儲到DataSet中。(需連接數據庫和處理異常)
/// </summary>
/// <param name="strQuery">sql語句</param>
/// <returns></returns>
public DataSet ExecuteCacheQuery(string strQuery)
{
DataSet dsResult = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(strQuery, SqlConn);
adapter.Fill(dsResult);
adapter.Dispose();
return dsResult;
}
/// <summary>
/// 執行SELECT,將查詢結果存儲到DataSet中。(外部不需連接數據庫和處理異常)
/// </summary>
/// <param name="strQuery">sql語句</param>
/// <returns></returns>
public DataSet LazyExecuteCacheQuery(string strQuery)
{
try
{
OpenConnection();
return ExecuteCacheQuery(strQuery);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.StackTrace);
return null;
}
finally
{
CloseConnection();
}
}
#endregion
#region DB的增删改查操作
/// <summary>
/// 通过SqlDataAdapter快使更新DB数据.(外部不需連接數據庫和處理異常)
/// </summary>
/// <param name="strQuery">一定要与查询的时候的sql语句一样</param>
/// <param name="dt">DataSource 可以是DataTable类型 </param>
public bool LazyQuickUpdateDbRowsBySqlDataAdapter(string strQuery, DataTable dt)
{
try
{
using (SqlDataAdapter adapter = new SqlDataAdapter(strQuery, SqlConnStr))
{
//SqlCommandBuilder 帮我们的Adapter生成相关的CRUD
using (SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(adapter))
{
adapter.Update(dt);
}
}
return true;
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.StackTrace);
return false;
}
}
/// <summary>
/// 執行一条UPDATE、DELETE、INSERT。(外部不需連接數據庫和處理異常)
/// </summary>
/// <param name="str">sql語句</param>
/// <returns>執行狀態:1: 成功; 0:不成功</returns>
public int LazyExecuteNonQuery(string str)
{
int iResult = 0;
try
{
OpenConnection();
SqlCommand = new SqlCommand(str, SqlConn);
iResult = SqlCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.StackTrace);
}
finally
{
SqlCommand.Dispose();
CloseConnection();
}
return iResult;
}
/// <summary>
/// 執行一条UPDATE、DELETE、INSERT。(需連接數據庫和處理異常)
/// </summary>
/// <param name="str">查詢sql語句</param>
/// <returns>執行狀態:1: 成功; 0:不成功</returns>
public int ExecuteNonQuery(string str)
{
int iResult = 0;
SqlCommand = new SqlCommand(str, SqlConn);
iResult = SqlCommand.ExecuteNonQuery();
SqlCommand.Dispose();
return iResult;
}
/// <summary>
/// 執行多條UPDATE、DELETE、INSERT。(外部不需連接數據庫和處理異常)
/// </summary>
/// <param name="list">sql語句列表</param>
public void LazyExecuteNonQueryTransac(List<string> list)
{
SqlTransaction txn = null;
SqlCommand cmd = null;
try
{
OpenConnection();
txn = SqlConn.BeginTransaction();
cmd = new SqlCommand("", SqlConn, txn);
for (int i = 0; i < list.Count; i++)
{
string sql = list[i].Trim();
if (sql.Length > 1)
{
cmd.CommandText = list[i];
cmd.ExecuteNonQuery();
}
}
txn.Commit();
}
catch (Exception ex)
{
txn.Rollback();
System.Diagnostics.Debug.WriteLine(ex.StackTrace);
}
finally
{
cmd.Dispose();
txn.Dispose();
CloseConnection();
}
}
/// <summary>
/// 執行多條UPDATE、DELETE、INSERT。(外部需連接數據庫和處理異常)
/// </summary>
/// <param name="list">sql語句列表</param>
public void ExecuteNonQueryTransac(List<string> list)
{
SqlCommand cmd = new SqlCommand("", SqlConn);
SqlTransaction txn = SqlConn.BeginTransaction();
cmd.Transaction = txn;
try
{
for (int i = 0; i < list.Count; i++)
{
string sql = list[i].Trim();
if (sql.Length > 1)
{
cmd.CommandText = list[i];
cmd.ExecuteNonQuery();
}
}
txn.Commit();
}
catch (Exception ex)
{
txn.Rollback();
throw ex;
}
finally
{
cmd.Dispose();
txn.Dispose();
}
}
#endregion
#region 執行存儲過程,無返回記錄 。除此之外当前MyDbTool_SqlServer类中所有的NonQuery的方法均可执行过程化sql
/// <summary>
/// 執行存儲過程,無返回記錄。(外部不需連接數據庫和處理異常)。
/// </summary>
/// <param name="spname">spname 存儲過程名稱</param>
/// <param name="hxqb">hxqb 參數</param>
public void ExecuteSPNonQuery(string spname, string hxqb)
{
int iResult = 0;
OpenConnection();
SqlCommand = new SqlCommand(spname, SqlConn);
SqlCommand.CommandType = CommandType.StoredProcedure;
SqlCommand.Parameters.Add("ID", hxqb);
try
{
iResult = SqlCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
SqlCommand.Dispose();
CloseConnection();
}
}
#endregion
}
}
Program.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace ADO.NET练习
{
class Program
{
static void Main(string[] args)
{
MyDbTool_SqlServer myDb = new MyDbTool_SqlServer("LFC0001");
//string sql = "select * from TEST0001 ;";
//string value= GetValue(myDb, sql);
// GetSqlDataReader(myDb, sql);
// GetDataSetToDataTable(myDb, sql);
//bool flag= UpdateDbRowsBySqlDataAdapter(myDb, sql, GetDataSetToDataTable(myDb, sql));
// List<string> sqlList = new List<string>(){
// string.Format(@"INSERT INTO [dbo].[TEST0001]
// ([ID]
// ,[AA]
// ,[BB]
// ,[CC])
// VALUES
// (5,null,null,null
// );"),
// string.Format(@"INSERT INTO [dbo].[TEST0001]
// ([ID]
// ,[AA]
// ,[BB]
// ,[CC])
// VALUES
// (6,null,null,null
// );")
// };
// myDb.OpenConnection();
// myDb.ExecuteNonQueryTransac(sqlList);
// myDb.CloseConnection();
string sql = string.Format(@"
DECLARE @return_value int
EXEC @return_value = [dbo].[SP_Insert_Test0001]
@id = N'5'
SELECT 'Return Value' = @return_value
");
myDb.LazyExecuteNonQuery(sql);
//myDb.OpenConnection();
//myDb.ExecuteSPNonQuery("[dbo].[SP_Insert_Test0001]","5");
//myDb.CloseConnection();
Console.WriteLine(myDb.GetConnectionState());
Console.ReadKey();
}
/// <summary>
/// 使用DataAdapter去更新数据库
/// </summary>
/// <param name="myDb"></param>
/// <param name="sql">必须与查询出DataTable的SQL一致</param>
/// <param name="dt"></param>
/// <returns></returns>
private static bool UpdateDbRowsBySqlDataAdapter(MyDbTool_SqlServer myDb, string sql, DataTable dt)
{
DataRow dr = dt.NewRow();//添加行数据
dr["ID"] = "4";
dr["aa"] = "aaaa";
dr["bb"] = "bbbb";
dr[3] = "cccc";
dt.Rows.Add(dr);
bool flag = myDb.LazyQuickUpdateDbRowsBySqlDataAdapter(sql, dt);
return flag;
}
/// <summary>
/// 获取DataSet,并返回DataSet中指定的DataTable
/// </summary>
/// <param name="myDb"></param>
/// <param name="sql"></param>
private static DataTable GetDataSetToDataTable(MyDbTool_SqlServer myDb, string sql)
{
DataSet dset = myDb.LazyExecuteCacheQuery(sql);
DataTable dt = dset.Tables["table"];//dset.Tables[0];
//打印dt内容
//foreach (DataRow item in dt.Rows)
//{
// Console.WriteLine(
// item[0].ToString()+
// item[1].ToString()+
// item["bb"].ToString() +
// item["cc"].ToString());
//}
return dt;
}
/// <summary>
/// 获取SqlDataReader并打印
/// </summary>
/// <param name="myDb"></param>
/// <param name="sql"></param>
private static void GetSqlDataReader(MyDbTool_SqlServer myDb, string sql)
{
SqlDataReader reader = myDb.LazyExecuteNoCacheQuery(sql);
//DataTable dt = reader.GetSchemaTable();
while (reader.Read())
{
Console.WriteLine(
reader["id"].ToString()
+ reader[1].ToString()
+ reader["bb"].ToString()
+ reader["cc"].ToString()
);
}
// reader.Close();
myDb.CloseConnection();//使用SqlDataReader后别忘了关闭连接
}
/// <summary>
/// 查询一个值出来,(首行首列的值)
/// </summary>
/// <param name="myDb"></param>
/// <param name="sql"></param>
/// <returns></returns>
private static string GetValue(MyDbTool_SqlServer myDb, string sql)
{
string value = myDb.LazyExecuteScalarQuery(sql).ToString();
return value;
}
}
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<!--应用程序配置-->
<appSettings>
<add key="Version" value="1.0.0.1"></add>
<add key="SqlConn" value="server=.;uid=sa;pwd=123456;database=LFC0001;pooling=true"></add>
</appSettings>
<!--连接字符串的配置节点-->
<connectionStrings>
<add name="SqlConn" connectionString="server=.;uid=sa;pwd=123456;database=LFC0001;pooling=true"/>
</connectionStrings>
</configuration>
<!--
在C#中添加System.Configuration程序集引用来访问该config内容(适用于控制台和winform项目)
Console.WriteLine(ConfigurationManager.AppSettings["Version"]);
Console.WriteLine(ConfigurationManager.ConnectionStrings["SqlConn"].ConnectionString);
-->