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() ;
}
}
菜鸟学堂：
