C # database universal connection class

xiaoxiao2021-03-06 43

using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using System.Windows.Forms; using System.Collections; namespace Database {///

/// Database Summary Description . /// public class database {////

/// {// {/// {// Get // {//} M_DataSet; //} //}

Public Database () {// // Todo: Add Constructor Logic to here

Xmlread constr = new xmlread (); if (constr.readallconnectNode ()) {constr = constr.connstring; // try // {// open (); //} // catch (exception ex) // {// Messagebox.show ("Database Connection Error" EX.TOSTRING ()); //}

} Else {constr = "- 1"; // throw new sqlerrorcollection;

}

// public bool open () // {// mcn.connectionstring = constr; // try // {// mcn.open (); //} // Catch (Exception) // {// Return FALSE; / /} // return true; ///}

///

/// Default acquisition DataSet /// /// /// / //

// public virtual int getdata (String TmpMomputername) // {// Return-1; //}

#Region ExecutenonQuery

///

// / Perform a SQL Command / /// /// ConnectString (SQL connection string) /// < PARAM Name = "cmdtype"> Command Type /// Command's statement (SQL statement) /// Command parameters ( SQLParameter [] Array Type) /// Command's return value (affected row) 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;}}

///

// / Perform a SQL Command (using an implicit ConnectString) /// /// Command type /// Command statement (SQL statement) /// Command parameters (SQLParameter [] type type) /// Command's return value (the number of rows affected) public int ExecuteNonQuery (CommandType cmdType, string cmdText, params SqlParameter [] cmdParms) {SqlCommand cmd = new SqlCommand (); using (SqlConnection conn = new SqlConnection (constr)) {PrepareCommand (cmd, conn, null, cmdparms); int val = cmd.executenonQuery (); cmd.Parameters.clear (); return val;}} // public static int executenonQuery (String cmdtext) // {/ /} ///

// / Implement a simple query, just enter the SQL statement, generally used to update or delete /// /// ///

Public int executenonquery (string sqltext) {return executenonQuery (commandtype.text, sqltext);}

///

/// Execute a SQL Command /// /// using SQLTransaction /// Command's statement (SQL statement) /// Command parameters (SQLParameter [] array type) /// Command return value (the number of rows affected) 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;} ///

/ / / Automatically generate CMD execution data to execute DataSet /// /// ConnectString (SQL connection string) /// Command type /// ENUM type /// Command's statement (SQL statement) /// dataset /// table name /// Command parameters (SQLParameter [] type type) /// is updated successfully

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 (conn.State! = connectionState.Open) Conn (); 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; if (dataset.haserror) {dataset.tables [TableName] .GeterrorS () [0] .clearerrrs (); Return False;} else {dataset.acceptchanges (); return;} }

///

/// Update a recordset (using connString) /// /// ConnectString (SQL connection string) /// < Param name = "cmdinserttype"> CommandInsert type /// SQL statement (insert) /// CommandUpdate type /// SQL statement (update) /// SQL statement (Delete) /// INSERTCOMMAND parameter UpdateCommand parameter /// DeleteCommand parameter /// dataset /// table name /// is updated successfully Public Bool UpdatedataSet (String ConnString, CommandType CmdInsertType, String CmdInserttext, CommandTy CmdupdateType, String Cmdupdatetext, CommandType CmddeleType, 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.u PDATECOMMAND = 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.tables "{dataset.tables [Tablename] .geterrors () [0] .clearerrrs (); return false;} else {dataset.acceptchanges (); return true;}}}

#ndregion #Region ExecuteReader

///

/// Get a SqldataReader (using connString) /// connectString /// Type /// Command's statement (SELECT statement) /// Command parameter //// SqlDataReader required

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 }}

///

/// Get a SqlDataReader (using connString), using the default connectionstring //// /// type /// Command's statement (SELECT statement) /// Command parameter /// SqlDataReader public sqldataareader ExecuteReader CommandType cmdtype, string cmdtext, params sqlparameter [] cmdparms) {sqlcommand cmd = new sqlcommand (); sqlConnection conn = new SQLConnection (constr);

///

/// Get a SqlDataReader, use the default connectionString /// /// statement command ///

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

#ndregion #Region Private function

///

/// Prepare a Command (using SQLParameter [] array) ///

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

///

/// Add a param ///

Private sqlparameter newfieldparam (string fld) {sqlparameter param = new sqlparameter (); param.ParameterName = "@" fld; param.sourceColumn = fld; return param;}

///

/// Decision character is in a collection //////mmary> /// /// < / param> /// Private Bool Incolleciton (String Str, IList Excludefields) {ips (S.TOUPPER () == Str.Toupper ()) Return True; } Return False;} #endregion #region populates DataSet

///

/// Pack the data into the DataSet (no connString) /// type /// Command's statement /// Table name /// Command parameters

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"; USING (SqlConnection Conn = New SqlConnection (constr)) {PrepareCommand (CMD, CMDText, cmdparms); dscommand.fill (Dataset, TableName);}}

///

/// Pack data into the DataSet (using connString SQLParameterCollection) /// /// connectionString /// Type /// COMMAND statement /// Table name /// parameter (SqlParameterCollection) Command's 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 ();} }

转载请注明原文地址:https://www.9cbs.com/read-112422.html

9cbs

New Post(0)