主頁 > .NET開發 > DBHelper助手

DBHelper助手

2020-12-02 20:53:12 .NET開發

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Collections;

namespace LineWork.DAL
{
public class DBHelper
{
//資料庫連接字串(web.config來配置)，多資料庫可使用DbHelperSQLP來實作.
//public static string connectionString = ConfigurationManager.AppSettings["ConnectionString"];
private static string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

/// <summary>
///無參建構式
/// </summary>
public DBHelper()
{
connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
}

#region 公用方法

#region 判斷是否存在某表的某個欄位
/// <summary>
/// 判斷是否存在某表的某個欄位
/// </summary>
/// <param name="tableName">表名稱</param>
/// <param name="columnName">列名稱</param>
/// <returns>是否存在</returns>
public static bool ColumnExists(string tableName, string columnName)
{
string sql = "select count(1) from syscolumns where [id]=object_id('" + tableName + "') and [name]='" + columnName + "'";
object res = GetSingle(sql);
if (res == null)
{
return false;
}
return Convert.ToInt32(res) > 0;
}
#endregion

#region 獲取表內最大的ID
/// <summary>
/// 獲取表內最大的ID
/// </summary>
/// <param name="FieldName">欄位名</param>
/// <param name="TableName">表名</param>
/// <returns></returns>
public static int GetMaxID(string FieldName, string TableName)
{
string strsql = "select max(" + FieldName + ")+1 from " + TableName;
object obj = GetSingle(strsql);
if (obj == null)
{
return 1;
}
else
{
return int.Parse(obj.ToString());
}
}
#endregion

#region 執行一條計算查詢結果陳述句，回傳查詢結果（引數為StrSql）
/// <summary>
/// 執行一條計算查詢結果陳述句，回傳查詢結果（引數為StrSql）
/// </summary>
/// <param name="strSql">sql</param>
/// <returns></returns>
public static bool Exists(string strSql)
{
object obj = GetSingle(strSql);
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == 0)
{
return false;
}
else
{
return true;
}
}
#endregion

#region 執行一條計算查詢結果陳述句，回傳查詢結果（引數為TableName）
/// <summary>
/// 執行一條計算查詢結果陳述句，回傳查詢結果（引數為TableName）
/// </summary>
/// <param name="TableName">TableName</param>
/// <returns></returns>
public static bool TabExists(string TableName)
{
string strsql = "select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'IsUserTable') = 1";
//string strsql = "SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + TableName + "]') AND type in (N'U')";
object obj = GetSingle(strsql);
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == 0)
{
return false;
}
else
{
return true;
}
}
#endregion

#region 執行一條計算查詢結果陳述句，回傳查詢結果（引數為strSql,cmdParms）
/// <summary>
/// 執行一條計算查詢結果陳述句，回傳查詢結果（引數為strSql,cmdParms）
/// </summary>
/// <param name="strSql">strSql</param>
/// <param name="cmdParms">cmdParms</param>
/// <returns></returns>
public static bool Exists(CommandType cmdType, string strSql, params SqlParameter[] cmdParms)
{
object obj = GetSingle(cmdType, strSql, cmdParms);
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == 0)
{
return false;
}
else
{
return true;
}
}
#endregion

#endregion

#region 執行簡單SQL陳述句

#region 執行SQL陳述句，回傳影響的記錄數ExecuteSql
/// <summary>
/// 執行SQL陳述句，回傳影響的記錄數ExecuteSql
/// </summary>
/// <param name="SQLString">SQL陳述句</param>
/// <returns>影響的記錄數</returns>
public static int ExecuteSql(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}
finally
{
connection.Close();
}
}
}
}
#endregion

#region 執行SQL陳述句，回傳影響的記錄數ExecuteSqlByTime
/// <summary>
/// 執行SQL陳述句，回傳影響的記錄數ExecuteSqlByTime
/// </summary>
/// <param name="SQLString">sql</param>
/// <param name="Times"></param>
/// <returns></returns>
public static int ExecuteSqlByTime(string SQLString, int Times)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
cmd.CommandTimeout = Times;
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}
finally
{
connection.Close();
}
}
}
}

#endregion

#region 注釋掉的
///// <summary>
///// 執行Sql和Oracle滴混合事務
///// </summary>
///// <param name="list">SQL命令列串列</param>
///// <param name="oracleCmdSqlList">Oracle命令列串列</param>
///// <returns>執行結果 0-由于SQL造成事務失敗 -1 由于Oracle造成事務失敗 1-整體事務執行成功</returns>
//public static int ExecuteSqlTran(List<CommandInfo> list, List<CommandInfo> oracleCmdSqlList)
//{
// using (SqlConnection conn = new SqlConnection(connectionString))
// {
// conn.Open();
// SqlCommand cmd = new SqlCommand();
// cmd.Connection = conn;
// SqlTransaction tx = conn.BeginTransaction();
// cmd.Transaction = tx;
// try
// {
// foreach (CommandInfo myDE in list)
// {
// string cmdText = myDE.CommandText;
// SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
// PrepareCommand(cmd, conn, tx, cmdText, cmdParms);
// if (myDE.EffentNextType == EffentNextType.SolicitationEvent)
// {
// if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
// {
// tx.Rollback();
// throw new Exception("違背要求" + myDE.CommandText + "必須符合select count(..的格式");
// //return 0;
// }

// object obj = cmd.ExecuteScalar();
// bool isHave = false;
// if (obj == null && obj == DBNull.Value)
// {
// isHave = false;
// }
// isHave = Convert.ToInt32(obj) > 0;
// if (isHave)
// {
// //引發事件
// myDE.OnSolicitationEvent();
// }
// }
// if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
// {
// if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
// {
// tx.Rollback();
// throw new Exception("SQL:違背要求" + myDE.CommandText + "必須符合select count(..的格式");
// //return 0;
// }

// object obj = cmd.ExecuteScalar();
// bool isHave = false;
// if (obj == null && obj == DBNull.Value)
// {
// isHave = false;
// }
// isHave = Convert.ToInt32(obj) > 0;

// if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
// {
// tx.Rollback();
// throw new Exception("SQL:違背要求" + myDE.CommandText + "回傳值必須大于0");
// //return 0;
// }
// if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
// {
// tx.Rollback();
// throw new Exception("SQL:違背要求" + myDE.CommandText + "回傳值必須等于0");
// //return 0;
// }
// continue;
// }
// int val = cmd.ExecuteNonQuery();
// if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
// {
// tx.Rollback();
// throw new Exception("SQL:違背要求" + myDE.CommandText + "必須有影響行");
// //return 0;
// }
// cmd.Parameters.Clear();
// }
// string oraConnectionString = PubConstant.GetConnectionString("ConnectionStringPPC");
// bool res = OracleHelper.ExecuteSqlTran(oraConnectionString, oracleCmdSqlList);
// if (!res)
// {
// tx.Rollback();
// throw new Exception("Oracle執行失敗");
// // return -1;
// }
// tx.Commit();
// return 1;
// }
// catch (System.Data.SqlClient.SqlException e)
// {
// tx.Rollback();
// throw e;
// }
// catch (Exception e)
// {
// tx.Rollback();
// throw e;
// }
// }
//}
#endregion

#region 執行多條SQL陳述句，實作資料庫事務
/// <summary>
/// 執行多條SQL陳述句，實作資料庫事務
/// </summary>
/// <param name="SQLStringList">多條SQL陳述句</param>
public static int ExecuteSqlTran(List<String> SQLStringList)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
SqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
int count = 0;
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n];
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
count += cmd.ExecuteNonQuery();
}
}
tx.Commit();
return count;
}
catch
{
tx.Rollback();
return -1;
}
}
}
/// <summary>
/// 執行多條SQL陳述句，實作資料庫事務
/// </summary>
/// <param name="SQLStringList">多條SQL陳述句</param>
public static int ExecuteSqlTran2(List<StringBuilder> SQLStringList)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
SqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
int count = 0;
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
count += cmd.ExecuteNonQuery();
}
}
tx.Commit();
return count;
}
catch
{
tx.Rollback();
return -1;
}
}
}
#endregion

#region 執行帶一個存盤程序引數的的SQL陳述句
/// <summary>
/// 執行帶一個存盤程序引數的的SQL陳述句
/// </summary>
/// <param name="SQLString">SQL陳述句</param>
/// <param name="content">引數內容,比如一個欄位是格式復雜的文章，有特殊符號，可以通過這個方式添加</param>
/// <returns>影響的記錄數</returns>
public static int ExecuteSql(string SQLString, string content)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(SQLString, connection);
System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
myParameter.Value = https://www.cnblogs.com/ChinaBoys/p/content;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
#endregion

#region 執行帶一個存盤程序引數的SQL陳述句ExecuteSqlGet(string SQLString, string content)
/// <summary>
/// 執行帶一個存盤程序引數的SQL陳述句ExecuteSqlGet(string SQLString, string content)
/// </summary>
/// <param name="SQLString">SQL陳述句</param>
/// <param name="content">引數內容,比如一個欄位是格式復雜的文章，有特殊符號，可以通過這個方式添加</param>
/// <returns>影響的記錄數</returns>
public static object ExecuteSqlGet(string SQLString, string content)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(SQLString, connection);
System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText);
myParameter.Value = https://www.cnblogs.com/ChinaBoys/p/content;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
#endregion

#region 向資料庫里插入影像格式的欄位(和上面情況類似的另一種實體)ExecuteSqlInsertImg(string strSQL, byte[] fs)
/// <summary>
/// 向資料庫里插入影像格式的欄位(和上面情況類似的另一種實體)ExecuteSqlInsertImg(string strSQL, byte[] fs)
/// </summary>
/// <param name="strSQL">SQL陳述句</param>
/// <param name="fs">影像位元組,資料庫的欄位型別為image的情況</param>
/// <returns>影響的記錄數</returns>
public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(strSQL, connection);
System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image);
myParameter.Value = https://www.cnblogs.com/ChinaBoys/p/fs;
cmd.Parameters.Add(myParameter);
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}
finally
{
cmd.Dispose();
connection.Close();
}
}
}
#endregion

#region 執行一條計算查詢結果陳述句，回傳查詢結果（object）GetSingle(string SQLString)
/// <summary>
/// 執行一條計算查詢結果陳述句，回傳查詢結果（object）GetSingle(string SQLString)
/// </summary>
/// <param name="SQLString">計算查詢結果陳述句</param>
/// <returns>查詢結果（object）</returns>
public static object GetSingle(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return "";
}
else
{
return obj;
}
}
catch (System.Data.SqlClient.SqlException e)
{
connection.Close();
throw e;
}
}
}
}
#endregion

#region 執行一條計算查詢結果陳述句，回傳查詢結果（object）GetSingle(string SQLString, int Times)
/// <summary>
/// 執行一條計算查詢結果陳述句，回傳查詢結果（object）GetSingle(string SQLString, int Times)
/// </summary>
/// <param name="SQLString">計算查詢結果陳述句</param>
/// <param name="Times"></param>
/// <returns>查詢結果（object）</returns>
public static object GetSingle(string SQLString, int Times)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand(SQLString, connection))
{
try
{
connection.Open();
cmd.CommandTimeout = Times;
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System.Data.SqlClient.SqlException e)
{
connection.Close();
throw e;
}
}
}
}
#endregion

#region 執行查詢陳述句，回傳SqlDataReader ( 注意：呼叫該方法后，一定要對SqlDataReader進行Close )
/// <summary>
/// 執行查詢陳述句，回傳SqlDataReader ( 注意：呼叫該方法后，一定要對SqlDataReader進行Close )
/// </summary>
/// <param name="strSQL">查詢陳述句</param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader ExecuteReader(string strSQL)
{
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(strSQL, connection);
try
{
connection.Open();
SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return myReader;
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}

}
#endregion

#region 執行查詢陳述句，回傳DataSet--Query(string SQLString)
/// <summary>
/// 執行查詢陳述句，回傳DataSet--Query(string SQLString)
/// </summary>
/// <param name="SQLString">查詢陳述句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
command.Fill(ds, "ds");
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return ds;
}
}
#endregion

#region 執行查詢陳述句，回傳DataSet--Query(string SQLString, int Times)
/// <summary>
/// 執行查詢陳述句，回傳DataSet--Query(string SQLString, int Times)
/// </summary>
/// <param name="SQLString">查詢陳述句</param>
/// /// <param name="Times"></param>
/// <returns>DataSet</returns>
public static DataSet Query(string SQLString, int Times)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
command.SelectCommand.CommandTimeout = Times;
command.Fill(ds, "ds");
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return ds;
}
}
#endregion


#endregion

#region 執行多條帶引數的Sql陳述句

public static int ExecuteBatchSql(StringBuilder[] sqls, object[] param)
{
int num = 0;
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlTransaction transaction = connection.BeginTransaction();
try
{
for (int i = 0; i < sqls.Length; i++)
{
StringBuilder builder = sqls[i];
if (builder != null)
{
SqlParameter[] pars = (SqlParameter[])param[i];
SqlCommand exCmd = new SqlCommand(builder.ToString(), connection);
if (null != pars && pars.Length > 0)
exCmd.Parameters.AddRange(pars);
exCmd.Transaction = transaction;
num += exCmd.ExecuteNonQuery();
}
}
transaction.Commit();
}
catch (Exception)
{
num = -1;
}
finally
{
connection.Close();
}
}
}
catch (Exception)
{
num = -1;
}

return num;
}
#endregion

//public static string GetCount(string Sql)
//{
// try
// {
// SqlConnection connection = new SqlConnection(connectionString);
// SqlCommand exCmd = new SqlCommand(Sql, connection);

// connection.Open();
// SqlDataReader result = exCmd.ExecuteNonQuery();
// string i = "0";
// while (result.Read())
// {
// i = result.ToString();
// }
// result.Close();
// exCmd.Dispose();
// connection.Close();
// return i;
// }
// catch (Exception ex)
// {
// System.Windows.Forms.MessageBox.Show(ex.Message + "\r\n" + Sql);
// return "0";
// }
//}

#region 執行帶引數的SQL陳述句

/// <summary>
/// 執行SQL陳述句，回傳影響的記錄數
/// </summary>
/// <param name="SQLString">SQL陳述句</param>
/// <returns>影響的記錄數</returns>
public static int ExecuteSql(CommandType cmdType, string SQLString, params SqlParameter[] cmdParms)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
PrepareCommand(cmd, cmdType, connection, null, SQLString, cmdParms);
int rows = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return rows;
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}
}
}
}


/// <summary>
/// 執行多條SQL陳述句，實作資料庫事務，
/// </summary>
/// <param name="SQLStringList">SQL陳述句的哈希表（key為sql陳述句，value是該陳述句的SqlParameter[]）</param>
public static void ExecuteSqlTran(CommandType cmdType, Hashtable SQLStringList)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
SqlCommand cmd = new SqlCommand();
try
{
//回圈
foreach (DictionaryEntry myDE in SQLStringList)
{
string cmdText = myDE.Key.ToString();
SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
PrepareCommand(cmd, cmdType, conn, trans, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
}
}
}

///// <summary>
///// 執行多條SQL陳述句，實作資料庫事務
///// </summary>
///// <param name="SQLStringList">SQL陳述句的哈希表（key為sql陳述句，value是該陳述句的SqlParameter[]）</param>
//public static int ExecuteSqlTran(System.Collections.Generic.List<CommandInfo> cmdList)
//{
// using (SqlConnection conn = new SqlConnection(connectionString))
// {
// conn.Open();
// using (SqlTransaction trans = conn.BeginTransaction())
// {
// SqlCommand cmd = new SqlCommand();
// try
// {
// int count = 0;
// //回圈
// foreach (CommandInfo myDE in cmdList)
// {
// string cmdText = myDE.CommandText;
// SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
// PrepareCommand(cmd, conn, trans, cmdText, cmdParms);

// if (myDE.EffentNextType == EffentNextType.WhenHaveContine || myDE.EffentNextType == EffentNextType.WhenNoHaveContine)
// {
// if (myDE.CommandText.ToLower().IndexOf("count(") == -1)
// {
// trans.Rollback();
// return 0;
// }

// object obj = cmd.ExecuteScalar();
// bool isHave = false;
// if (obj == null && obj == DBNull.Value)
// {
// isHave = false;
// }
// isHave = Convert.ToInt32(obj) > 0;

// if (myDE.EffentNextType == EffentNextType.WhenHaveContine && !isHave)
// {
// trans.Rollback();
// return 0;
// }
// if (myDE.EffentNextType == EffentNextType.WhenNoHaveContine && isHave)
// {
// trans.Rollback();
// return 0;
// }
// continue;
// }
// int val = cmd.ExecuteNonQuery();
// count += val;
// if (myDE.EffentNextType == EffentNextType.ExcuteEffectRows && val == 0)
// {
// trans.Rollback();
// return 0;
// }
// cmd.Parameters.Clear();
// }
// trans.Commit();
// return count;
// }
// catch
// {
// trans.Rollback();
// throw;
// }
// }
// }
//}
///// <summary>
///// 執行多條SQL陳述句，實作資料庫事務，
///// </summary>
///// <param name="SQLStringList">SQL陳述句的哈希表（key為sql陳述句，value是該陳述句的SqlParameter[]）</param>
//public static void ExecuteSqlTranWithIndentity(System.Collections.Generic.List<CommandInfo> SQLStringList)
//{
// using (SqlConnection conn = new SqlConnection(connectionString))
// {
// conn.Open();
// using (SqlTransaction trans = conn.BeginTransaction())
// {
// SqlCommand cmd = new SqlCommand();
// try
// {
// int indentity = 0;
// //回圈
// foreach (CommandInfo myDE in SQLStringList)
// {
// string cmdText = myDE.CommandText;
// SqlParameter[] cmdParms = (SqlParameter[])myDE.Parameters;
// foreach (SqlParameter q in cmdParms)
// {
// if (q.Direction == ParameterDirection.InputOutput)
// {
// q.Value = https://www.cnblogs.com/ChinaBoys/p/indentity;
// }
// }
// PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
// int val = cmd.ExecuteNonQuery();
// foreach (SqlParameter q in cmdParms)
// {
// if (q.Direction == ParameterDirection.Output)
// {
// indentity = Convert.ToInt32(q.Value);
// }
// }
// cmd.Parameters.Clear();
// }
// trans.Commit();
// }
// catch
// {
// trans.Rollback();
// throw;
// }
// }
// }
//}

 

/// <summary>
/// 執行多條SQL陳述句，實作資料庫事務，
/// </summary>
/// <param name="SQLStringList">SQL陳述句的哈希表（key為sql陳述句，value是該陳述句的SqlParameter[]）</param>
public static void ExecuteSqlTranWithIndentity(CommandType cmdType, Hashtable SQLStringList)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
SqlCommand cmd = new SqlCommand();
try
{
int indentity = 0;
//回圈
foreach (DictionaryEntry myDE in SQLStringList)
{
string cmdText = myDE.Key.ToString();
SqlParameter[] cmdParms = (SqlParameter[])myDE.Value;
foreach (SqlParameter q in cmdParms)
{
if (q.Direction == ParameterDirection.InputOutput)
{
q.Value = https://www.cnblogs.com/ChinaBoys/p/indentity;
}
}
PrepareCommand(cmd, cmdType, conn, trans, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
foreach (SqlParameter q in cmdParms)
{
if (q.Direction == ParameterDirection.Output)
{
indentity = Convert.ToInt32(q.Value);
}
}
cmd.Parameters.Clear();
}
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
}
}
}
/// <summary>
/// 執行一條計算查詢結果陳述句，回傳查詢結果（object），
/// </summary>
/// <param name="SQLString">計算查詢結果陳述句</param>
/// <returns>查詢結果（object）</returns>
public static object GetSingle(CommandType cmdType, string SQLString, params SqlParameter[] cmdParms)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
using (SqlCommand cmd = new SqlCommand())
{
try
{
PrepareCommand(cmd, cmdType, connection, null, SQLString, cmdParms);
object obj = cmd.ExecuteScalar();
cmd.Parameters.Clear();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}
}
}
}

/// <summary>
/// 執行查詢陳述句，回傳SqlDataReader ( 注意：呼叫該方法后，一定要對SqlDataReader進行Close )
/// </summary>
/// <param name="strSQL">查詢陳述句</param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader ExecuteReader(CommandType cmdType, string SQLString, params SqlParameter[] cmdParms)
{
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
try
{
PrepareCommand(cmd, cmdType, connection, null, SQLString, cmdParms);
SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return myReader;
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}
// finally
// {
// cmd.Dispose();
// connection.Close();
// }

}

/// <summary>
/// 執行查詢陳述句，回傳DataSet
/// </summary>
/// <param name="SQLString">查詢陳述句</param>
/// <returns>DataSet</returns>
public static DataSet Query(CommandType cmdType, string SQLString, params SqlParameter[] cmdParms)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, cmdType, connection, null, SQLString, cmdParms);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
try
{
da.Fill(ds, "ds");
cmd.Parameters.Clear();
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return ds;
}
}
}


private static void PrepareCommand(SqlCommand cmd, CommandType cmdType, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;//cmdType;
if (cmdParms != null)
{


foreach (SqlParameter parameter in cmdParms)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value =https://www.cnblogs.com/ChinaBoys/p/= null))
{
parameter.Value = https://www.cnblogs.com/ChinaBoys/p/DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
}


#endregion

#region 存盤程序操作

/// <summary>
/// 執行存盤程序，回傳SqlDataReader ( 注意：呼叫該方法后，一定要對SqlDataReader進行Close )
/// </summary>
/// <param name="storedProcName">存盤程序名</param>
/// <param name="parameters">存盤程序引數</param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
{
SqlConnection connection = new SqlConnection(connectionString);
SqlDataReader returnReader;
connection.Open();
SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure;
returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
return returnReader;

}

/// <summary>
/// 執行存盤程序
/// </summary>
/// <param name="storedProcName">存盤程序名</param>
/// <param name="parameters">存盤程序引數</param>
/// <param name="tableName">DataSet結果中的表名</param>
/// <returns>DataSet</returns>
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet dataSet = new DataSet();
connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
sqlDA.Fill(dataSet, tableName);
connection.Close();
return dataSet;
}
}

public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet dataSet = new DataSet();
connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
sqlDA.SelectCommand.CommandTimeout = Times;
sqlDA.Fill(dataSet, tableName);
connection.Close();
return dataSet;
}
}

/// <summary>
/// 回傳DataTable
/// </summary>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="tableName"></param>
/// <param name="objParams"></param>
/// <returns></returns>
public static DataTable GetDataTable(CommandType cmdType, string cmdText, params SqlParameter[] objParams)
{
SqlCommand cmd = new SqlCommand();
DataTable dt = new DataTable();

using (SqlConnection conn = new SqlConnection(connectionString))
{
if (conn.State == ConnectionState.Closed)
{ conn.Open(); }

cmd.Connection = conn;
cmd.CommandType = cmdType;
cmd.CommandText = cmdText;

if (objParams != null)
{
// 引數
cmd.Parameters.AddRange(objParams);
}

SqlDataAdapter adapter = new SqlDataAdapter(cmd);

try
{
adapter.Fill(dt);

return dt;
}
catch
{
conn.Close();
return null;
}
}
}


/// <summary>
/// 構建 SqlCommand 物件(用來回傳一個結果集，而不是一個整數值)
/// </summary>
/// <param name="connection">資料庫連接</param>
/// <param name="storedProcName">存盤程序名</param>
/// <param name="parameters">存盤程序引數</param>
/// <returns>SqlCommand</returns>
private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand(storedProcName, connection);
command.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
if (parameter != null)
{
// 檢查未分配值的輸出引數,將其分配以DBNull.Value.
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value =https://www.cnblogs.com/ChinaBoys/p/= null))
{
parameter.Value = https://www.cnblogs.com/ChinaBoys/p/DBNull.Value;
}
command.Parameters.Add(parameter);
}
}
}
return command;
}

/// <summary>
/// 執行存盤程序，回傳影響的行數
/// </summary>
/// <param name="storedProcName">存盤程序名</param>
/// <param name="parameters">存盤程序引數</param>
/// <param name="rowsAffected">影響的行數</param>
/// <returns></returns>
public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
int result;
connection.Open();
SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
rowsAffected = command.ExecuteNonQuery();
result = (int)command.Parameters["ReturnValue"].Value;
//Connection.Close();
return result;
}
}

/// <summary>
/// 創建 SqlCommand 物件實體(用來回傳一個整數值)
/// </summary>
/// <param name="storedProcName">存盤程序名</param>
/// <param name="parameters">存盤程序引數</param>
/// <returns>SqlCommand 物件實體</returns>
private static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
command.Parameters.Add(new SqlParameter("ReturnValue",
SqlDbType.Int, 4, ParameterDirection.ReturnValue,
false, 0, 0, string.Empty, DataRowVersion.Default, null));
return command;
}
#endregion

#region 獲取資料庫連接字串GetConncetionString()
/// <summary>
/// 獲取資料庫連接字串GetConncetionString()
/// </summary>
/// <returns>回傳資料庫連接字串</returns>
public static string GetConnectionString()
{
// string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
return connectionString;//回傳資料庫連接字串
}
#endregion

/// <summary>
/// 根據唯一ID獲取Hashtable
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="pkName">欄位主鍵</param>
/// <param name="pkVal">欄位值</param>
/// <returns></returns>
public static Hashtable GetHashtableById(string Sql, string tableName, string pkName, string pkVal)
{
StringBuilder sb = new StringBuilder();
sb.Append("Select " + Sql + " From ").Append(tableName).Append(" Where ").Append(pkName).Append("=@ID");
DataTable dt = GetDataTableBySQL(sb, new SqlParameter[] { new SqlParameter("@ID", pkVal) });
return DataTableToHashtable(dt);
}

/// <summary>
/// DataTable 轉 DataTableToHashtable
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public static Hashtable DataTableToHashtable(DataTable dt)
{
Hashtable ht = new Hashtable();
foreach (DataRow dr in dt.Rows)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
string key = dt.Columns[i].ColumnName;
ht[key.ToUpper()] = dr[key];
}
}
return ht;
}

/// <summary>
/// 根據 SQL 回傳 DataTable 資料集，帶引數
/// </summary>
/// <param name="sql">sql陳述句</param>
/// <param name="param">引數化</param>
/// <returns>DataTable</returns>
private static DataTable GetDataTableBySQL(StringBuilder sb, SqlParameter[] sqlParameter)
{
SqlConnection conn = null;
try
{
conn = new SqlConnection(connectionString);
SqlCommand com = new SqlCommand(sb.ToString(), conn);
if (sqlParameter != null && sqlParameter.Length > 0)
com.Parameters.AddRange(sqlParameter);
conn.Open();
SqlDataReader sqlreader = com.ExecuteReader();
return DataTableToIDataReader(sqlreader);
}
catch (Exception)
{
return null;
}
finally
{
conn.Close();
}
}

/// <summary>
/// 將IDataReader轉換為DataTable
/// </summary>
/// <param name="reader"></param>
/// <returns></returns>
public static DataTable DataTableToIDataReader(IDataReader reader)
{
DataTable objDataTable = new DataTable("Table");
int intFieldCount = reader.FieldCount;
for (int intCounter = 0; intCounter < intFieldCount; ++intCounter)
{
objDataTable.Columns.Add(reader.GetName(intCounter).ToUpper(), reader.GetFieldType(intCounter));
}
objDataTable.BeginLoadData();
object[] objValues = new object[intFieldCount];
while (reader.Read())
{
reader.GetValues(objValues);
objDataTable.LoadDataRow(objValues, true);
}
reader.Close();
objDataTable.EndLoadData();
return objDataTable;
}

/// <summary>
/// 回傳DataSet
/// </summary>
/// <param name="storedProcName">存盤程序名稱</param>
/// <param name="parameters">引數</param>
/// <returns></returns>
public static DataSet DataSetProd(string storedProcName, IDataParameter[] parameters)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet dataSet = new DataSet();
connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
sqlDA.Fill(dataSet);
connection.Close();
return dataSet;
}
}


}
}

轉載請註明出處，本文鏈接：https://www.uj5u.com/net/228222.html

標籤：C#

上一篇：C#7 的一些新語法

下一篇：C#彈出對話框 確定或者取消 執行相應操作

標籤雲
其他(157675) Python(38076) JavaScript(25376) Java(17977) C(15215) 區塊鏈(8255) C＃(7972) AI(7469) 爪哇(7425) MySQL(7132) html(6777) 基礎類(6313) sql(6102) 熊猫(6058) PHP(5869) 数组(5741) R(5409) Linux(5327) 反应(5209) 腳本語言(PerlPython)(5129) 非技術區(4971) Android(4554) 数据框(4311) css(4259) 节点.js(4032) C語言(3288) json(3245) 列表(3129) 扑(3119) C++語言(3117) 安卓(2998) 打字稿(2995) VBA(2789) Java相關(2746) 疑難問題(2699) 细绳(2522) 單片機工控(2479) iOS(2429) ASP.NET(2402) MongoDB(2323) 麻木的(2285) 正则表达式(2254) 字典(2211) 循环(2198) 迅速(2185) 擅长(2169) 镖(2155) 功能(1967) .NET技术(1958) Web開發(1951) python-3.x(1918) HtmlCss(1915) 弹簧靴(1913) C++(1909) xml(1889) PostgreSQL(1872) .NETCore(1853) 谷歌表格(1846) Unity3D(1843) for循环(1842)

熱門瀏覽
  • WebAPI簡介

    Web體系結構： 有三個核心：資源(resource)，URL(統一資源識別符號)和表示 他們的關系是這樣的：一個資源由一個URL進行標識，HTTP客戶端使用URL定位資源，表示是從資源回傳資料，媒體型別是資源回傳的資料格式。 接下來我們說下HTTP. HTTP協議的系統是一種無狀態的方式，使用請求/ ......

    uj5u.com 2020-09-09 22:07:47 more
  • asp.net core 3.1 入口：Program.cs中的Main函式

    本文分析Program.cs 中Main()函式中代碼的運行順序分析asp.net core程式的啟動，重點不是剖析原始碼，而是理清程式開始時執行的順序。到呼叫了哪些實體，哪些法方。asp.net core 3.1 的程式入口在專案Program.cs檔案里，如下。ususing System; us ......

    uj5u.com 2020-09-09 22:07:49 more
  • asp.net網站作為websocket服務端的應用該如何寫

    最近被websocket的一個問題困擾了很久，有一個需求是在web網站中搭建websocket服務。客戶端通過網頁與服務器建立連接，然后服務器根據ip給客戶端網頁發送資訊。 其實，這個需求并不難，只是剛開始對websocket的內容不太了解。上網搜索了一下，有通過asp.net core 實作的、有 ......

    uj5u.com 2020-09-09 22:08:02 more
  • ASP.NET 開源匯入匯出庫Magicodes.IE Docker中使用

    Magicodes.IE在Docker中使用 更新歷史 2019.02.13 【Nuget】版本更新到2.0.2 【匯入】修復單列匯入的Bug，單元測驗“OneColumnImporter_Test”。問題見（https://github.com/dotnetcore/Magicodes.IE/is ......

    uj5u.com 2020-09-09 22:08:05 more
  • 在webform中使用ajax

    如果你用過Asp.net webform， 說明你也算是.NET 開發的老兵了。WEBform應該是2011 2013左右，當時還用visual studio 2005、 visual studio 2008。后來基本都用的是MVC。 如果是新開發的專案，估計沒人會用webform技術。但是有些舊版 ......

    uj5u.com 2020-09-09 22:08:50 more
  • iis添加asp.net網站，訪問提示：由于擴展配置問題而無法提供您請求的

    今天在iis服務器配置asp.net網站，遇到一個問題，記錄一下： 問題：由于擴展配置問題而無法提供您請求的頁面。如果該頁面是腳本，請添加處理程式。如果應下載檔案，請添加 MIME 映射。 WindowServer2012服務器，添加角色安裝完.netframework和iis之后，運行aspx頁面 ......

    uj5u.com 2020-09-09 22:10:00 more
  • WebAPI-處理架構

    帶著問題去思考，大家好！ 問題1：HTTP請求和回傳相應的HTTP回應資訊之間發生了什么？ 1：首先是最底層，托管層，位于WebAPI和底層HTTP堆疊之間 2：其次是 訊息處理程式管道層，這里比如日志和快取。OWIN的參考是將訊息處理程式管道的一些功能下移到堆疊下端的OWIN中間件了。 3：控制器處理 ......

    uj5u.com 2020-09-09 22:11:13 more
  • 微信門戶開發框架-使用指導說明書

    微信門戶應用管理系統，采用基于 MVC + Bootstrap + Ajax + Enterprise Library的技術路線，界面層采用Boostrap + Metronic組合的前端框架，資料訪問層支持Oracle、SQLServer、MySQL、PostgreSQL等資料庫。框架以MVC5，... ......

    uj5u.com 2020-09-09 22:15:18 more
  • WebAPI-HTTP編程模型

    帶著問題去思考，大家好！它是什么？它包含什么？它能干什么？ 訊息 HTTP編程模型的核心就是訊息抽象，表示為：HttPRequestMessage,HttpResponseMessage.用于客戶端和服務端之間交換請求和回應訊息。 HttpMethod類包含了一組靜態屬性： private stat ......

    uj5u.com 2020-09-09 22:15:23 more
  • 部署WebApi隨筆

    一、跨域 NuGet參考Microsoft.AspNet.WebApi.Cors WebApiConfig.cs中配置： // Web API 配置和服務 config.EnableCors(new EnableCorsAttribute("*", "*", "*")); 二、清除默認回傳XML格式 ......

    uj5u.com 2020-09-09 22:15:48 more
最新发布
  • C#多執行緒學習(二) 如何操縱一個執行緒

    <a href="https://www.cnblogs.com/x-zhi/" target="_blank"><img width="48" height="48" class="pfs" src="https://pic.cnblogs.com/face/2943582/20220801082530.png" alt="" /></...

    uj5u.com 2023-04-19 09:17:20 more
  • C#多執行緒學習(二) 如何操縱一個執行緒

    C#多執行緒學習(二) 如何操縱一個執行緒 執行緒學習第一篇：C#多執行緒學習(一) 多執行緒的相關概念 下面我們就動手來創建一個執行緒，使用Thread類創建執行緒時，只需提供執行緒入口即可。（執行緒入口使程式知道該讓這個執行緒干什么事） 在C#中，執行緒入口是通過ThreadStart代理（delegate）來提供的 ......

    uj5u.com 2023-04-19 09:16:49 more
  • 記一次 .NET某醫療器械清洗系統 卡死分析

    <a href="https://www.cnblogs.com/huangxincheng/" target="_blank"><img width="48" height="48" class="pfs" src="https://pic.cnblogs.com/face/214741/20200614104537.png" alt="" /&g...

    uj5u.com 2023-04-18 08:39:04 more
  • 記一次 .NET某醫療器械清洗系統 卡死分析

    一：背景 1. 講故事 前段時間協助訓練營里的一位朋友分析了一個程式卡死的問題，回過頭來看這個案例比較經典，這篇稍微整理一下供后來者少踩坑吧。 二：WinDbg 分析 1. 為什么會卡死 因為是表單程式，理所當然就是看主執行緒此時正在做什么? 可以用 ~0s ; k 看一下便知。 0:000> k # ......

    uj5u.com 2023-04-18 08:33:10 more
  • SignalR, No Connection with that ID，IIS

    <a href="https://www.cnblogs.com/smartstar/" target="_blank"><img width="48" height="48" class="pfs" src="https://pic.cnblogs.com/face/u36196.jpg" alt="" /></a>...

    uj5u.com 2023-03-30 17:21:52 more
  • 一次對pool的誤用導致的.net頻繁gc的診斷分析

    <a href="https://www.cnblogs.com/dotnet-diagnostic/" target="_blank"><img width="48" height="48" class="pfs" src="https://pic.cnblogs.com/face/3115652/20230225090434.png" alt=""...

    uj5u.com 2023-03-28 10:15:33 more
  • 一次對pool的誤用導致的.net頻繁gc的診斷分析

    <a href="https://www.cnblogs.com/dotnet-diagnostic/" target="_blank"><img width="48" height="48" class="pfs" src="https://pic.cnblogs.com/face/3115652/20230225090434.png" alt=""...

    uj5u.com 2023-03-28 10:13:31 more
  • C#遍歷指定檔案夾中所有檔案的3種方法

    <a href="https://www.cnblogs.com/xbhp/" target="_blank"><img width="48" height="48" class="pfs" src="https://pic.cnblogs.com/face/957602/20230310105611.png" alt="" /></a&...

    uj5u.com 2023-03-27 14:46:55 more
  • C#/VB.NET：如何將PDF轉為PDF/A

    <a href="https://www.cnblogs.com/Carina-baby/" target="_blank"><img width="48" height="48" class="pfs" src="https://pic.cnblogs.com/face/2859233/20220427162558.png" alt="" />...

    uj5u.com 2023-03-27 14:46:35 more
  • 武裝你的WEBAPI-OData聚合查詢

    <a href="https://www.cnblogs.com/podolski/" target="_blank"><img width="48" height="48" class="pfs" src="https://pic.cnblogs.com/face/616093/20140323000327.png" alt="" /><...

    uj5u.com 2023-03-27 14:46:16 more

Copyright © 2010-2020 有解無憂