C # database manipulation package

xiaoxiao2021-03-06 15

In order to facilitate the manipulation of the database, a database class is written. After multiple modifications, it becomes below:

Using system; using system.data.odbc; using system.data.soledb; using system.data.sqlclient; using system.data.common;

using System.IO; namespace com.hnrui.db2005 {///

/// HrDB: database manipulation /// public class HrDB {private static DateTime sqlMinDateTime = DateTime.MinValue; private static DateTime sqlMaxDateTime = DateTime.MinValue; public static DateTime sqlMinDateTime {get {if (sqlMinDateTime == DateTime.MinValue) sqlMinDateTime = new DateTime (1753, 1, 1); return sqlMinDateTime;}} public static DateTime sqlMaxDateTime {get {if (sqlMaxDateTime == DateTime .Minvalue) sqlmaxdatetime = new datetime (9999, 12, 31); return sqlmaxdatetime;}}

Private string dbconstring = null; private dataprovidertype m_dataProvidertype = dataProvideType.sqlclient;

Private string merRORS = ""; public string errors {get {string res = MERRORS; MERRORS = ""; // Error information After extracting Return Res;}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}}} }@} }

#Region constructor ///

/// Use SQLClient to set the default connection /// /// server /// Database /// Username /// password public hrdb (String ServerName, String DatabaseName, string userName, string password) {m_DataProviderType = DataProviderType.SQLCLIENT; dbConString = "Initial Catalog =" dataBaseName ";" "Data Source =" serverName ";" "User ID =" userName ";" "Password =" password ";" "Application Name = Hengrui" ";" "Connection Timeout = 60";

///

/// Use SQLClient to set the default connection //////////////////////////// /// database /// Username /// password /// application name public HrDB (string serverName, string dataBaseName, string userName, string password, string appName) {m_DataProviderType = DataProviderType.SQLCLIENT; dbConString = "Initial Catalog =" dataBaseName ";" "Data Source =" ServerName ";" "User ID =" username ";" "Password =" password ";" "Application Name =" AppName "; " Connection Timeout = 60 "; //

/// creates a default connection /// /// Database type: Sqlclient, OLE db, Odbc, Oracle /// connection string public HrDB (DataProviderType dataProviderType, String connectionString) {m_DataProviderType = dataProviderType; dbConString = connectionString;}

public HrDB (string dataProvider, string connectionString) {DataProviderType dp = DataProviderType.SQLCLIENT; switch (dataProvider.ToUpper ()) {case "OLEDB": dp = DataProviderType.OLEDB; break; case "ODBC": dp = DataProviderType.ODBC; Break; Case "Oracle": DP = DataProvideType.Oracle; Break; Default: DP = DataProvideType.sqlClient; Break;} m_dataProVideType = DP; DBCONSTRING = Connectionstring;} #ENDREGION

Public Bool Isconnectok {Get {DBConnection Dbcon = OpenConnection (); if (dbcon == null) Return False;

ColseConnection (dbcon); return true;}}

Public dbconnection openconnection () {dbconnection dbcon = OpenConnection (m_dataprovidertype, dbconstring);

IF (dbcon! = NULL) IF (dbcon.state == connectionState.closed) dbcon.open ();

Return dbcon;} ///

/// Connect the database and keep open //////////////////////////////> Database type: SQLClient, OLEDB, ODBC, Oracle /// connection string private DbConnection OpenConnection (DataProviderType dataProviderType, string connectionString) {DbConnection con = null; try {switch (dataProviderType) {case DataProviderType.SQLCLIENT: System.Data. SqlClient.SqlConnection sqlCon = new SqlConnection (connectionString); con = sqlCon; break; case DataProviderType.OLEDB: System.Data.OleDb.OleDbConnection oleCon = new OleDbConnection (connectionString); con = oleCon; break; case DataProviderType.ODBC: System. Data.odb C.odbcconnection odbccon = new odbcconnection; con = ODBCCON; Break; default: con = NULL; Break;}

IF (con! = null) {con?open ();}} catch (exception e) {mrrs = e.Source : " E.MESSAGE; con = null;} returnon;} ///

/// Close Database Connection /// /// Database connection public void colseconnection (dbconnection dbcon) {if (dbcon! = Null) {dbcon.close (); dbCon.Dispose (); // release resources}} #region DML public DbDataAdapter GetDataAdapter (String selectCommandText) {return GetDataAdapter (selectCommandText, m_DataProviderType, dbConString, false);} public DbDataAdapter GetDataAdapter (String selectCommandText, DbTransaction transaction) { Return getDataAdapter (SELECTCOMMANDTEXT, M_DATAPROVIDERTYPE, TRANSACTION, FALSE);}

public DbDataAdapter GetDataAdapter (String selectCommandText, bool forUpdate) {return GetDataAdapter (selectCommandText, m_DataProviderType, dbConString, forUpdate);} public DbDataAdapter GetDataAdapter (String selectCommandText, DbTransaction transation, bool forUpdate) {return GetDataAdapter (selectCommandText, m_DataProviderType, transation, forUpdate); } ///

/// acquired database adapter /// /// Query string /// database Type: Sqlclient, OLEDB, ODBC, Oracle /// connection string /// is used to update the database Private DBDataAdapter getDataAdapter (String SelectCommandtext, DataProvidertype DataProvideType, String Constr, Bool ForUpdate {if (constr == ") Return NULL;

mErrors = ""; DbDataAdapter ada = null; try {switch (dataProviderType) {case DataProviderType.SQLCLIENT: System.Data.SqlClient.SqlDataAdapter sqlAda = new SqlDataAdapter (selectCommandText, constr); // if for updating, automatically generate // query table must have time keys if (forUpdate) {SqlCommandBuilder scb = new SqlCommandBuilder (sqlAda);} ada = sqlAda; break; case DataProviderType.OLEDB:. System.Data.OleDb.OleDbDataAdapter oleAda = new OleDbDataAdapter (selectCommandText , constr); if (forUpdate) {OLEDBCommandbuilder OCB = New OLEDBCommandbuilder (Oleada); ada = oleAda; break; case DataProviderType.ODBC: System.Data.Odbc.OdbcDataAdapter odbcAda = new OdbcDataAdapter (selectCommandText, constr); if (forUpdate) {OdbcCommandBuilder odbccb = new OdbcCommandBuilder (odbcAda);} ada = odbcAda; break; default : Ada = null; Break;}} catch (exception e) {mrrs = e.Source : "

e.Message; ada = null;} return ada;} private DbDataAdapter GetDataAdapter (String selectCommandText, DataProviderType dataProviderType, DbTransaction transaction, bool forUpdate) {if (transaction == null) return null; if (transaction.Connection == null) return NULL;

mErrors = ""; DbDataAdapter ada = null; try {switch (dataProviderType) {case DataProviderType.SQLCLIENT: System.Data.SqlClient.SqlDataAdapter sqlAda = new SqlDataAdapter (selectCommandText, (SqlConnection) transaction.Connection); if (forUpdate) {SqlCommandBuilder SCB = New SqlcommandBuilder (SQLADA);

} Ada = sqlAda; break; case DataProviderType.OLEDB: System.Data.OleDb.OleDbDataAdapter oleAda = new OleDbDataAdapter (selectCommandText, (OleDbConnection) transaction.Connection); if (forUpdate) {OleDbCommandBuilder ocb = new OleDbCommandBuilder (oleAda);} ada = oleAda; break; case DataProviderType.ODBC: System.Data.Odbc.OdbcDataAdapter odbcAda = new OdbcDataAdapter (selectCommandText, (OdbcConnection) transaction.Connection); if (forUpdate) {OdbcCommandBuilder odbccb = new OdbcCommandBuilder (odbcAda);} ada = odbcAda Break; Default: ada = NULL; Break;}} catch (exception e) {mRRORS = E.Source ": E.MESSAGE; Ada = null;} Return Ada;

Public Dataset getDataSet (String SelectCommandtext) {

MERRORS = "";

INT NTRY = 0; DataSet DS = New DataSet (); r: try {dbdataadapter ada = getDataAdapter (selectcommandtext); ada.fill (DS); ada.dispose ();} catch (exception e) {f (( ) NTRY) <= 3) Goto R; Else {MERRORS = E.Source ": E.MESSAGE; DS = NULL;}} Return DS;

MERRORS = "";

INT NTRY = 0; DataSet DS = New Dataset (); r: try {dbdataadapter ada = getDataadapter (selectcommandtext, transaction); ada.fill (DS); ada.dispose ();} catch (Exception E) {IF NTRY) <= 3) Goto R; Else {MERRORS = E.Source ": E.MESSAGE; DS = NULL;}}

Return DS;

Public DataTable getDataTable (String SelectCommandtext) {MERRORS = ""

DataTable DT = New DataTable (); int NTRY = 0; R: try {dbdataadapter ada = getDataadapter (selectcommandtext); ada.fill (); ada.dispose ();} catch (exception e) {ix (( ) NTRY) <= 3) Goto R; Else {MERRORS = E.Source ": E.MESSAGE; DT = NULL;}} Return Dt;} Public DataTable getDatable (String SelectCommandtext, dbtransaction transization) {mierrors =" " ;

DataTable DT = New DataTable (); int NTRY = 0; r: try {dbdataadapter ada = getDataadapter (selectcommandtext, transization); ada.fill (DT); ada.dispose ();} catch (exception e) {IF NTRY) <= 3) Goto R; Else {MERRORS = E.Source ": E.MESSAGE; DT = NULL;}}

Return dt;}

Public DataTable getDataTable (String SelectCommandtext, Bool getKeys) {

MERRORS = "";

If (! getKeys) return getdatatable (selectcommandtext);

DataTable dt = new DataTable (); int nTry = 0; r: try {DbDataAdapter ada = GetDataAdapter (selectCommandText); ada.MissingSchemaAction = MissingSchemaAction.AddWithKey; ada.Fill (dt); ada.Dispose ();} catch (Exception E) {IF (( NTRY) <= 3) Goto R; Else {MERRORS = E.Source ": E.MESSAGE; DT = NULL;}}

Public DataRow getDataRow (String SelectCommandtext) {MERRORS = ""

IF (dt.rows.count> 0) Return Dt.Rows [0]; Else Return Null;} Public DataRow getDataRow (String SelectCommandtext, DBTRANSACTION TRANSACTION {MERRORS = ""

DataTable DT = New DataTable (); int NTRY = 0; R: try {dbdataadapter ada = getDataAdapter (selectcommandtext, transaction); ada.fill (DT); ada.dispose ();} catch (exception e) {IF NTRY) <= 3) Goto R; Else Merrors = E.Source ": E.Message;} f (dt.rows.count> 0) Return DT.ROWS [0]; else return null;}

Public int fix (DataTable DataTable, String SelectCommandtext) {int res = -1; // - 1 indicates an error INT NTRY = 0; r: try {dbdataadapter ada = getDataadapter (selectcommandtext); res = ada.fill (DataTable) Ada.dispose ();} catch (exception e) {IF (( NTRY) <= 3) goto r; else MERRORS = E.Source ": E.MESSAGE;

return res;} public int Fill (DataTable dataTable, String selectCommandText, DbTransaction transaction) {int res = -1; int nTry = 0; r: try {DbDataAdapter ada = GetDataAdapter (selectCommandText, transaction); res = ada.Fill (dataTable ); Ada.dispose ();} catch (exception e) {IF (( ntry) <= 3) goto r; Else Merrors = E.Source ": E.MESSAGE;} Return Res;}

public int Update (DataTable dataTable, String selectCommandText) {int res = -1; // - 1 represents the generation of an error try {DbDataAdapter ada = GetDataAdapter (selectCommandText, true); res = ada.Update (dataTable); ada.Dispose ( );} Catch (Exception E) {MERRORS = E.Source ":" E.MESSAGE;

return res;} public int Update (DataTable dataTable, String selectCommandText, DbTransaction tran) {int res = -1; try {DbDataAdapter ada = GetDataAdapter (selectCommandText, tran, true); res = ada.Update (dataTable); ada.Dispose (); (Exception E) {MERRORS = E.Source ":" E.MESSAGE;

Return res;}

public int Update (DataSet dataSet, String selectCommandText) {mErrors = ""; int res = -1; try {DbDataAdapter ada = GetDataAdapter (selectCommandText, true); res = ada.Update (dataSet); ada.Dispose ();} Catch (Exception E) {MERRORS = E.Source : " E.MESSAGE;} Return Res;

public int Update (DataRow dataRow, string selectCommandText) {mErrors = ""; int res = -1; try {DbDataAdapter ada = GetDataAdapter (selectCommandText, true); res = ada.Update (new DataRow [] {dataRow}); ada .Dispose ();} catch (exception e) {mRRORS = E.Source : " E.MESSAGE;

Return res;}

public int Update (DataSet dataSet, String selectCommandText, DbTransaction tran) {mErrors = ""; int res = -1; try {DbDataAdapter ada = GetDataAdapter (selectCommandText, tran, true); res = ada.Update (dataSet); ada. Dispose ();} catch (exception e) {mrrors = e.Source ": E.MESSAGE;

Return res;} public int execute (String ExecuteCumeText) {int res = -1; int NTRY = 0; dbconnection dbcon = OpenConnection (); if (dbcon == null) {MERRORS = "You cannot connect to the database."; return res; } MERRORS = "";

r: try {switch (m_DataProviderType) {case DataProviderType.SQLCLIENT: SqlCommand sc = new SqlCommand (executeCommandText, (SqlConnection) dbCon); res = sc.ExecuteNonQuery (); sc.Dispose (); break; case DataProviderType.OLEDB: OleDbCommand olc = new OleDbCommand (executeCommandText, (OleDbConnection) dbCon); res = olc.ExecuteNonQuery (); olc.Dispose (); break; case DataProviderType.ODBC: OdbcCommand oc = new OdbcCommand (executeCommandText, (OdbcConnection) dbCon); res = Oc.executenonQuery (); Oc.dispose (); Break; default: Break;}} catch (EXCEPTION E) {IF ( NTRY) <= 3) Goto R; Else Merrors = E.Source ":" E.MESSAGE;

ColseConnection (DBCON);

Return res;} public int executext, dbtransaction TRAN) {Int res = -1; int NTRY = 0; dbconnection dbcon = tr.connection; if (dbcon == null) {mRRORS = "cannot connect to the database." Return res;} MERRORS = ""

r: try {switch (m_DataProviderType) {case DataProviderType.SQLCLIENT: SqlCommand sc = new SqlCommand (executeCommandText, (SqlConnection) dbCon, (SqlTransaction) tran); res = sc.ExecuteNonQuery (); sc.Dispose (); break; case DataProviderType.OLEDB: OleDbCommand olc = new OleDbCommand (executeCommandText, (OleDbConnection) dbCon, (OleDbTransaction) tran); res = olc.ExecuteNonQuery (); olc.Dispose (); break; case DataProviderType.ODBC: OdbcCommand oc = new OdbcCommand ( ExecuteCommandText, (odbcconnection) dbcon, (odbctransaction) TRAN); res = oc.executenonQuery (); oc.dispose (); break; default: Break;}} catch (exception e) {IF ( NTRY) <= 3) goto r; else merRORS = E.Source ": E.MESSAGE;

Return res;}

///

/// Get the last IDEENDITY / // /// table name public int getlastid (String TabName) { String SQL = "SELECT IDENT_CURRENT ('" tabname ")"; DataTable DT = getDataTable (SQL); if (dt! = null) ing (dt.rows.count> 0) Return (int) (DT) DT .ROWS [0] [0]; RETURN 0;} public int getLastid (String TabName, DBTRING TABNAME, DBTRING TABNAME, DBTRING SQL = "SELECT IDENT_CURRENT ('" tabname ")"; DataTable Dt = GetDataTable (SQL, TRAN) ; IF (dt! = Null) f (int) DT.ROWS [0] [0]; return 0;} #ENDREGONPUBLIC DBTRANSATION begionTransAction () {

Dbconnection conn = OpenConnection (); if (conn == null) Return NULL;

DBTRANSACTION TRAN = conn.begintransaction ();

Return Tran;}

Public DataRow GetRowForUpdate (String SQL) {DataTable DT = getDataTable (SQL); if (DT == Null) Return NULL;

DataRow Row = NULL;

// If the line does not exist, create a row of IF (dt.rows.count == 0) {row = dt.newrow (); dt.Rows.add (row);} else {row = dt.rows [0]; } Return Row;

#Region images ///

// / /// contains the table name of the image field. The change must contain an int type primary key /// image field name /// Primary key field name / // Primary key /// byte array /// -1 means failure, other success public int UpdateImageToSQL (string tableName, string imageColumn, string keyColumn, int keyValue, byte [] image) {DbConnection dbCon = OpenConnection (); if (dbCon == null) {mErrors = ". not connected to the database"; return - 1; }

MERRORS = "";

Int bufferlen = 128; // buffer length

String SQL = "Update" Tablename "SET" ImageColumn "= 0x0" "Where" keycolumn "=" keyValue.toString ();

Int res = execute (SQL);

IF (res == -1) Return Res;

SQL = "SELECT TEXTPTR (" ImageColumn ")" "from" Tablename "Where" keycolumn "=" keyValue.toString ();

DataRow Row = GetDataRow (SQL);

IF (row == null) return -1;

BYTE [] PTR = (byte []) row [0];

SQL = "UpdateText" Tablename "." ImageColumn "@point @offset 0 @bytes"; sqlcommand appendtoimage = new sqlcommand (SQL, (SqlConnection) DBCON;

SqlParameter ptrParm = appendToImage.Parameters.Add ( "@ Pointer", SqlDbType.Binary, 16); ptrParm.Value = ptr; SqlParameter imageParm = appendToImage.Parameters.Add ( "@ Bytes", SqlDbType.Image, bufferLen); SqlParameter offsetParm = appendtoimage.Parameters.add ("@ Offset", SqldbType.It); OffsetParm.Value = 0;

BinaryReader Br = New BinaryReader (New MemoryStream (Image));

Byte [] buffer = br.readBytes (bufferlen); int offset_ctr = 0;

While (Buffer.Length> 0) {imageParm.Value = buffer; appendtoimage.executenonury (); offset_ctr = bufferlen; offsetparm.value = offset_ctr; buffer = br.readbytes (bufferlen);}

Br.close (); appendtoimage.dispose (); colseconnection (dbcon); dbcon = NULL;

Return image.length;}

///

/// Take an image field from the database /// /// contains the table name of the Image field. The change must contain an int type primary key /// image field name /// Primary key field name / // Primary key value //// Image field content BYTE array public byte [] getImagefromsql (String Tablename, String ImageColumn, String KeyColumn, int Keyvalue ) {Dbconnection dbcon = OpenConnection (); if (dbcon == null) {MERRORS = "You cannot connect to the database."; Return New Byte [0];} MERRORS = ""

SqlCommand cmd; string sql = "Select" imageColumn "From" tableName "Where" keyColumn "=" keyValue.ToString (); cmd = new SqlCommand (sql, (SqlConnection) dbCon); SqlDataReader myReader = Cmd.executeReader (Commandbehavior.SequentialAccess);

Memorystream ms = new memorystream (); binarywriter bw;

INT buffersize = 128; byte [] outbyte = new byte [buffersize]; long retval; long startIndex = 0;

Byte [] image = new byte [0]; while (myReader.Read ()) {bw = new binarywriter (ms);

StartIndex = 0;

Retval = MyReader.getbytes (0, StartIndex, Outbyte, 0, Buffers);

While (retval == buffersize) {bw.write (outbyte); bw.flush (); startIndex = buffersize; retval = myreader.getbytes (0, StartIndex, Outbyte, 0, Buffersize);}

BW.WRITE (Outbyte, 0, (int) RetVal - 1); bw.flush ();

Image = ms.toarray ();

BW.Close (); ms.close ();

MyReader.close (); MyReader.dispose (); ColseConnection (DBCON); dbcon = null; return image;} #ENDREGION

#region get values public static DateTime GetRowValue (DataRow row, String colName, DateTime dtDef) {try {return (DateTime) row [colName];} catch {return dtDef;}} public static int GetRowValue (DataRow row, String colName, int INTDEF) {Try {if (row.table.columns [color] .DATATYPE == System.Type.gettype ("System.Int16")) Return (int16) Row [colname]; Else Return (int) ROW [colName];} catch {return intDef;}} public static decimal GetRowValue (DataRow row, String colName, decimal decDef) {try {return (decimal) row [colName];} catch {return decDef;}} public static float GetRo wValue (DataRow row, String colName, float decDef) {try {return (float) row [colName];} catch {return decDef;}} public static double GetRowValue (DataRow row, String colName, double decDef) {try {return ( Double) row [colname];} catch {return decdef;}}

public static string GetRowValue (DataRow row, String colName) {try {return ( "" row [colName]) Trim ();.} catch {return "";}} #endregion} public enum DataProviderType {SQLCLIENT = 0, OLEDB = 1, ODBC = 2, Oracle = 3}}

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

9cbs

New Post(0)