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() ; } }

|