資料庫通用串連類

來源:互聯網
上載者:User
資料|資料庫 using System;
using System.Data ;
using System.Data.SqlClient ;
using System.Data.SqlTypes ;
using System.Windows.Forms ;
using System.Collections;
namespace Database
{
/// <summary>
/// Database 的摘要說明。
/// </summary>
public class Database
{
/// <summary>
/// 屬性
/// </summary>
// public DataSet dataSet
// {
// get
// {
// return m_DataSet;
// }
//
// }
public Database()
{
//
// TODO: 在此處添加建構函式邏輯
//
XmlRead ConStr=new XmlRead();
if (ConStr.ReadAllConnectNode())
{
constr= ConStr.connstring ;
// try
// {
//
// Open();
// }
// catch(Exception Ex)
// {
// MessageBox.Show("資料庫連接錯誤"+Ex.ToString () );
//
// }

}
else
{
constr="-1";
//throw new SqlErrorCollection();

}


}
// public bool Open()
// {
//
// mcn.ConnectionString = constr;
// try
// {
// mcn.Open();
//
// }
// catch( Exception)
// {
// return false;
// }
// return true;
// }
/// <summary>
/// 預設擷取DataSet
/// </summary>
/// <param name="pMyTableName"></param>
/// <param name="tmpMyComputerName"></param>
/// <returns></returns>
// public virtual int getData (string pMyTableName ,string tmpMyComputerName)
// {
// return -1;
//
// }
#region ExecuteNonQuery

/// <summary>
/// 執行一個SQL Command(使用ConnectString)
/// </summary>
/// <param name="connString">ConnectString(Sql連接字串)</param>
/// <param name="cmdType">Command類型</param>
/// <param name="cmdText">Command的語句(SQL語句)</param>
/// <param name="cmdParms">Command的參數(SqlParameter[]數群組類型)</param>
/// <returns>Command的傳回值(受影響的行數)</returns>

public int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();

using (SqlConnection conn = new SqlConnection(connString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// 執行一個SQL Command(使用隱含的ConnectString)
/// </summary>
/// <param name="cmdType">Command類型</param>
/// <param name="cmdText">Command的語句(SQL語句)</param>
/// <param name="cmdParms">Command的參數(SqlParameter[]數群組類型)</param>
/// <returns>Command的傳回值(受影響的行數)</returns>
public int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();

using (SqlConnection conn = new SqlConnection(constr))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}

// public static int ExecuteNonQuery(string cmdText)
// {
// }

/// <summary>
/// 執行一個簡單的查詢, 只需要輸入SQL語句, 一般用於更新或者刪除
/// </summary>
/// <param name="sqlText"></param>
/// <returns></returns>
public int ExecuteNonQuery(string sqlText)
{
return ExecuteNonQuery(CommandType.Text,sqlText);
}

/// <summary>
/// 執行一個SQL Command(使用SqlTransaction)
/// </summary>
/// <param name="trans">使用的SqlTransaction</param>
/// <param name="cmdType">Command類型</param>
/// <param name="cmdText">Command的語句(SQL語句)</param>
/// <param name="cmdParms">Command的參數(SqlParameter[]數群組類型)</param>
/// <returns>Command的傳回值(受影響的行數)</returns>
public int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType,string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}

/// <summary>
/// 根據指定DsCommandType類型,自動產生cmd執行dataset的更新
/// </summary>
/// <param name="connString">ConnectString(Sql連接字串)</param>
/// <param name="cmdType">Command類型</param>
/// <param name="dsCommandType">Enum類型</param>
/// <param name="cmdText">Command的語句(SQL語句)</param>
/// <param name="dataset">dataset</param>
/// <param name="tablename">表名</param>
/// <param name="cmdParms">Command的參數(SqlParameter[]數群組類型)</param>
/// <returns>是否更新成功</returns>
public bool ExecuteNonQuery(string connString,CommandType cmdType,CommandEnum.DsCommandType dsCommandType,string cmdText,DataSet dataset,string tablename,params SqlParameter[] cmdParms)
{
SqlDataAdapter dsCommand = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection(connString))
{
if (conn.State != ConnectionState.Open)
conn.Open();

cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
switch(dsCommandType)
{
case CommandEnum.DsCommandType.InsertCommand:
dsCommand.InsertCommand = cmd;
break;
case CommandEnum.DsCommandType.UpdateCommand:
dsCommand.UpdateCommand = cmd;
break;
case CommandEnum.DsCommandType.DeleteCommand:
dsCommand.DeleteCommand = cmd;
break;
default:break;
}
dsCommand.Update(dataset,tablename);
if ( dataset.HasErrors )
{
dataset.Tables[tablename].GetErrors()[0].ClearErrors();
return false;
}
else
{
dataset.AcceptChanges();
return true;
}
}

}
/// <summary>
/// 更新一個記錄集(使用connString)
/// </summary>
/// <param name="connString">ConnectString(Sql連接字串)</param>
/// <param name="cmdInsertType">commandInsert類型</param>
/// <param name="cmdInsertText">SQL語句(Insert)</param>
/// <param name="cmdUpdateType">commandUpdate類型</param>
/// <param name="cmdUpdateText">SQL語句(Update)</param>
/// <param name="cmdInsertType">commandDelete類型</param>
/// <param name="cmdDeleteText">SQL語句(Delete)</param>
/// <param name="cmdInsertParms">InsertCommand參數</param>
/// <param name="cmdUpdateParms">UpdateCommand參數</param>
/// <param name="cmdDeleteParms">DeleteCommand參數</param>
/// <param name="dataset">dataset</param>
/// <param name="tablename">表名</param>
/// <returns>是否更新成功</returns>
public bool UpdateDataset(string connString,CommandType cmdInsertType,string cmdInsertText,CommandType cmdUpdateType,string cmdUpdateText,CommandType cmdDeleteType,string cmdDeleteText,SqlParameter[] cmdInsertParms,SqlParameter[] cmdUpdateParms,SqlParameter[] cmdDeleteParms,DataSet dataset,string tablename)
{
SqlDataAdapter dsCommand = new SqlDataAdapter();
using (SqlConnection conn = new SqlConnection(connString))
{
if (conn.State != ConnectionState.Open)
conn.Open();
if(cmdInsertText != String.Empty)
{
SqlCommand cmdInsert = new SqlCommand();
cmdInsert.Connection = conn;
cmdInsert.CommandText = cmdInsertText;
cmdInsert.CommandType = cmdInsertType;
if (cmdInsertParms != null)
{
foreach (SqlParameter parm in cmdInsertParms)
cmdInsert.Parameters.Add(parm);
}
dsCommand.InsertCommand = cmdInsert;
}
if(cmdUpdateText != String.Empty)
{
SqlCommand cmdUpdate = new SqlCommand();
cmdUpdate.Connection = conn;
cmdUpdate.CommandText = cmdUpdateText;
cmdUpdate.CommandType = cmdUpdateType;
if (cmdUpdateParms != null)
{
foreach (SqlParameter parm in cmdUpdateParms)
cmdUpdate.Parameters.Add(parm);
}
dsCommand.UpdateCommand = cmdUpdate;
}
if(cmdDeleteText != String.Empty)
{
SqlCommand cmdDelete = new SqlCommand();
cmdDelete.Connection = conn;
cmdDelete.CommandText = cmdDeleteText;
cmdDelete.CommandType = cmdDeleteType;
if (cmdDeleteParms != null)
{
foreach (SqlParameter parm in cmdDeleteParms)
cmdDelete.Parameters.Add(parm);
}
dsCommand.DeleteCommand = cmdDelete;
}
if(cmdInsertText == String.Empty && cmdUpdateText == String.Empty && cmdDeleteText == String.Empty)
{
SqlCommandBuilder scb = new SqlCommandBuilder(dsCommand);
return false;
}
dsCommand.Update(dataset,tablename);
if ( dataset.HasErrors )
{
dataset.Tables[tablename].GetErrors()[0].ClearErrors();
return false;
}
else
{
dataset.AcceptChanges();
return true;
}
}

}
#endregion
#region ExecuteReader
/// <summary>
/// 擷取一個SqlDataReader(使用connString)
/// </summary>
/// <param name="connString">ConnectString</param>
/// <param name="cmdType">類型</param>
/// <param name="cmdText">Command的語句(select語句)</param>
/// <param name="cmdParms">Command的參數</param>
/// <returns>所需要的SqlDataReader</returns>
public SqlDataReader ExecuteReader(string connString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connString);

try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}

}

/// <summary>
/// 擷取一個SqlDataReader(使用connString), 使用預設的ConnectionString
/// </summary>
/// <param name="cmdType">類型</param>
/// <param name="cmdText">Command的語句(select語句)</param>
/// <param name="cmdParms">Command的參數</param>
/// <returns>SqlDataReader</returns>
public SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(constr);

try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
}

/// <summary>
/// 擷取一個SqlDataReader, 使用預設的ConnectionString
/// </summary>
/// <param name="cmdtxt">語句命令</param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string cmdtxt)
{

SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(constr);

try
{
cmd=new SqlCommand(cmdtxt,conn);
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

return rdr;
}
catch
{
conn.Close();
throw;
}
}
#endregion

#region private函數
/// <summary>
/// 準備一個Command(使用SqlParameter[]數組)
/// </summary>
private void PrepareCommand (SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
{
try
{
conn.Open();
}
catch(Exception Ex)
{
throw Ex;
//string a = Ex.ToString();
//return;
}

}
cmd.Connection = conn;
cmd.CommandText = cmdText;

if (trans != null)
cmd.Transaction = trans;

cmd.CommandType = cmdType;

if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}

private void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameterCollection cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();

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

if (trans != null)
cmd.Transaction = trans;

cmd.CommandType = cmdType;

if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}

/// <summary>
/// 加入一個以欄位名為名稱的param
/// </summary>
/// <param name="fld"></param>
/// <returns></returns>
private SqlParameter NewFieldParam(string fld)
{
SqlParameter param = new SqlParameter();
param.ParameterName = "@" + fld;
param.SourceColumn = fld;
return param;
}

/// <summary>
/// 判斷字元是否在一個集合中
/// </summary>
/// <param name="str"></param>
/// <param name="ExcludeFields"></param>
/// <returns></returns>
private bool InColleciton(string str,IList ExcludeFields)
{
foreach(string s in ExcludeFields)
{
if(s.ToUpper()==str.ToUpper())
return true;
}
return false;
}
#endregion
#region 填充DataSet


/// <summary>
/// 將資料填充到DataSet中(無connString)
/// </summary>
/// <param name="cmdType">類型</param>
/// <param name="cmdText">Command的語句</param>
/// <param name="tablename">表名</param>
/// <param name="cmdParms">Command的參數</param>
public void FillData(CommandType cmdType,string cmdText,DataSet dataset,string tablename,params SqlParameter[] cmdParms)
{
SqlDataAdapter dsCommand = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
dsCommand.SelectCommand = cmd;
//dsCommand.TableMappings.Add("Table",tablename);
using (SqlConnection conn = new SqlConnection(constr))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
dsCommand.Fill(dataset,tablename);
}
}

/// <summary>
/// 將資料填充到DataSet中(使用connString + SqlParameterCollection)
/// </summary>
/// <param name="connString">ConnectString</param>
/// <param name="cmdType">類型</param>
/// <param name="cmdText">Command的語句</param>
/// <param name="tablename">表名</param>
/// <param name="cmdParms">Command的參數(SqlParameterCollection)</param>
public void FillDataEx(string connString, CommandType cmdType,string cmdText,DataSet dataset,string tablename,SqlParameterCollection cmdParms)
{
SqlDataAdapter dsCommand = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
dsCommand.SelectCommand = cmd;
dsCommand.TableMappings.Add("Table",tablename);
using (SqlConnection conn = new SqlConnection(connString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
dsCommand.Fill(dataset);
}
}
#endregion

internal string constr= null;//= "Uid =sa ;Pwd=sa ;Server = Drago;Database =Northwind";
internal SqlConnection mcn = new SqlConnection();
internal DataSet m_DataSet =new System.Data.DataSet() ;
}

}


相關文章

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。