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() ;
}
}
菜鳥學堂：
