Access | data public class OPERATEDB
{
private static string Constr;
public static OleDbConnection CNN;
OleDbDataAdapter da;
OleDbCommandBuilder CB;
OleDbCommand cmd;
Constructors
#region Initialize
Public Operatedb ()
{
//
TODO: Add constructor logic here
//
Cnn=new OleDbConnection ();
Da=new OleDbDataAdapter ();
You must have the following row when you do not need to update the OleDbCommand object to the database
Cb=new OleDbCommandBuilder (DA);
Cmd=new OleDbCommand ();
}
#endregion Initialize
Connection string
#region get&setconnectionstring
<summary>
Get connection string
</summary>
public string Myconstr
{
get {return constr;}
set {constr = value;}
}
#endregion get&setconnectionstring
Get the name of the table
#region Acquiretablenames
<summary>
Gets the collection of table names in the database
</summary>
<returns></returns>
Public DataTable tablescollection ()
{
DataTable tbl=new DataTable ();
Try
{
Cnn. CONNECTIONSTRING=CONSTR;
Cnn. Open ();
TBL = CNN. GetOleDbSchemaTable (OleDbSchemaGuid.Tables,
New object[] {null, NULL, NULL, "TABLE"});
}
catch (Exception CE)
{
Console.WriteLine ("Generate Error: \n{0}", CE. message);
}
Finally
{
Cnn. Close ();
}
return TBL;
}
#endregion Acquiretablenames
Populating data
#region filltable
<summary>
Queries that populate a DataTable
</summary>
<param name= "Tblname" > Datasheet (you must enter a name or view that exists in the database) </param>
<param name= "sqlstr" >sql statement </param>
<returns> record number of bars </returns>
public int Select (DataTable tblname,string sqlstr)
{
int i=0;
Try
// {
//
Tblname.clear ();
Da. Dispose ();
if (CNN). connectionstring== "")
Cnn. CONNECTIONSTRING=CONSTR;
if (CNN). State!=connectionstate.open)
Cnn. Open ();
OleDbCommand cmd=new OleDbCommand ("select * from" +tblname.tablename+ "where" +sqlstr,cnn);
Cmd. CONNECTION=CNN;
Cmd.commandtype=commandtype.text;
cmd.commandtext= "SELECT * from" +tblname.tablename+ "where" +SQLSTR;
Da. Selectcommand=cmd;
I=da. Fill (tblname);
//
//
// }
catch (Exception CE)
// {
Console.WriteLine ("Generate Error: \n{0}", CE. message);
// }
Finally
// {
This.da.Dispose ();
Cnn. Close ();
// }
return i;
}
#endregion filltable
Insert Record
#region Insert (use CommandBuilder)
<summary>
Insert record (with OleDbCommandBuilder)
</summary>
<param name= "Tblname" > Datasheet </param>
<param name= "NewRow" > New row corresponding to the field in the table </param>
Number of rows affected by <returns> </returns>
public int Insert (DataTable tblname,datarow newrow)
{
Cnn. Open ();
int i=0;
//
Try
// {
How to tell if OleDbDataAdapter has been dispose
If you do not generate a new OleDbDataAdapter, OleDbCommandBuilder, OleDbCommand below,
With the original global da,cb,cmd, only one table can be updated in one operation
OleDbDataAdapter dain=new OleDbDataAdapter ();
OleDbCommandBuilder cbin=new OleDbCommandBuilder (Dain);
OleDbCommand cmdin=new OleDbCommand ("select * from" +TBLNAME.TABLENAME,CNN);
Dain.selectcommand=cmdin;
foreach (DataTable dt in da.) TableMappings)
// {
if (dt. Tablename!=tblname.tablename)
Dt. Clear ();
// }
TBLNAME.ROWS.ADD (NewRow);
I=dain.update (tblname);
//
// }
catch (Exception CE)
// {
Console.WriteLine ("Generate Error: \n{0}", CE. message);
// }
Finally
// {
Cnn. Close ();
// }
Cnn. Close ();
return i;
}
#endregion Insert (use CommandBuilder)
Insert Record
#region Insert (use insidetransaction,datatable[])
public string Insert (datatable[] tbls,datarow[] newrows)
{
Int[] Num=new Int[tbls. Length];
int sum=0;
BOOL Judge=false;
String Str= "";
if (TBLs. Length==newrows.length)
{
Cnn. Open ();
OleDbTransaction tran=cnn. BeginTransaction ();
for (int i=0;i<tbls. length;i++)
{
This.select (Tbls[i], "1=1", Tran);
Da. Insertcommand=insertcmd (Tbls[i], "operation number");
Tbls[i]. Rows.Add (Newrows[i]);
Da. Insertcommand.transaction=tran;
Try
{
Num[i]=da. Update (Tbls[i]);
Sum+=num[i];
}
Catch
{
Sum=-1;
}
if (num[i]==0)
Judge=true;
}
if (judge)
{
Tran. Rollback ();
Str= "Update Failed";
sum=0;
}
Else
{
Tran.commit ();
str= "Update Successful";
}
}
Cnn. Close ();
Return str+ ", affected the" +sum. ToString () + "record";
}
#endregion insert (use insidetransaction,datatable[])
Insert Record
#region Insert (use outsidetransaction)
<summary>
Populating a DataTable (for transaction processing)
</summary>
<param name= "tblname" > Table </param>
<param name= "sqlstr" >sql statement </param>
<param name= "TRS" >transaction object </param>
<returns> Number of lines </returns>
public int Select (DataTable tblname,string sqlstr,oledbtransaction TRS)
{
int i=0;
Try
// {
//
Tblname.clear ();
Da. Dispose ();
if (CNN). connectionstring== "")
Cnn. CONNECTIONSTRING=CONSTR;
if (CNN). State!=connectionstate.open)
Cnn. Open ();
OleDbCommand cmd=new OleDbCommand ("select * from" +tblname.tablename+ "where" +sqlstr,cnn);
Cmd. CONNECTION=CNN;
Cmd.commandtype=commandtype.text;
cmd.commandtext= "SELECT * from" +tblname.tablename+ "where" +SQLSTR;
Da. Selectcommand=cmd;
Cmd. Transaction=trs;
I=da. Fill (tblname);
return i;
}
<summary>
Insert record (using Oledbdataadapter.update method and OleDbTransaction)
</summary>
<param name= "Tblname" > Datasheet </param>
<param name= "NewRow" > New line </param>
<param name= "TRS" > Transaction Object </param>
<returns></returns>
public int Insert (DataTable tblname,datarow newrow,oledbtransaction TRS)
{
Da. Insertcommand=insertcmd (Tblname, "Noo");
int num=0;
Try
{
TBLNAME.ROWS.ADD (NewRow);
Da. Insertcommand.transaction=trs;
Num=da. Update (tblname);
}
Catch
{
}
return num;
}
#endregion Insert (use outsidetransaction)
Constructs an inserted command
#region InsertCommand
<summary>
Structural InsertCommand
</summary>
<param name= "DTL" > Datasheet </param>
<param name= The name of the "Identitycol" >identity column </param>
<returns></returns>
private static OleDbCommand Insertcmd (DataTable dtl,string identitycol)
{
OleDbCommand incmd=new OleDbCommand ();
INCMD.CONNECTION=CNN;
String Sqlstr= "";
String Strvalue= "";
Sqlstr = "INSERT" + DTL. Tablename.tostring () + "(";
strvalue = ") Values (";
for (int I=0;I<DTL. columns.count;i++)
{
No assignment for identity columns
if (DTL. Columns[i]. ToString ()!= identitycol)
{
Sqlstr + = "[" + DTL. Columns[i]. ToString () + "],";
strvalue + = "?,";
OleDbParameter Mypara = new OleDbParameter ();
Mypara.parametername = "@" + DTL. Columns[i]. ToString ();
Mypara.oledbtype = Getoledbtype (DTL. Columns[i]. Datatype.tostring ());
Mypara.direction = ParameterDirection.Input;
Mypara.sourcecolumn = DTL. Columns[i]. ToString ();
Mypara.sourceversion = DataRowVersion.Current;
INCMD.PARAMETERS.ADD (Mypara);
}
}
Sqlstr=sqlstr.substring (0,sqlstr.length-2);
Strvalue=strvalue.substring (0,strvalue.length-1);
Sqlstr + = strvalue + ")";
Incmd.commandtext = Sqlstr;
return incmd;
}
#endregion InsertCommand
Modify
#region Update
<summary>
Modify Records
</summary>
<param name= "Tblname" > Datasheet </param>
<param name= "Strup" >sql statement </param>
Number of rows affected by <returns> </returns>
public int update (DataTable tblname,string Strup)
{
Cnn. Close ();
return i;
}
#endregion Update
Delete
#region del (use CommandBuilder)
<summary>
Delete a record
</summary>
<param name= "Tblname" > Datasheet </param>
<param name= "Strdel" >sql statement </param>
Number of rows affected by <returns> </returns>
public int Delete (DataTable tblname,string strdel)//strdel is delete condition
{
int rows=0;
Automatic updating with the Oledbdataadapter.update method a primary key or unique value must exist in the where
Try
// {
//
Cnn. Open ();
Rows=tblname.rows.count;
for (int i=0;i< tblname.rows.count;i++)
{
Tblname.rows[i]. Delete ();
}
Note, as in Da. The following AcceptChanges method was used before update because the record was deleted--failed to update to the database
Tblname.acceptchanges ();
Da. Update (tblname);
//
// }
catch (Exception CE)
// {
Console.WriteLine ("Generate Error: \n{0}", CE. message);
// }
Finally
// {
Cnn. Close ();
// }
///
Update directly with OleDbCommand
Try
// {
String str= "Delete from" +tblname.tablename+ "where" +strdel;
Cnn. Open ();
OleDbCommand cmdd=new OleDbCommand (STR,CNN);
Cmdd.commandtype=commandtype.text;
Rows=cmdd.executenonquery ();
// }
//
catch (Exception CE)
// {
Console.WriteLine ("Generate Error: \n{0}", CE. message);
// }
Finally
// {
Cnn. Close ();
// }
return rows;
}
#endregion del (use CommandBuilder)
Constructs a deleted command
#region Delcommand (Create Oledbdataadapter.deletecommand)
public int Delete (DataTable tblname)
{
int rows=0;
Da. Deletecommand=delcmd (tblname);
for (int i=0;i< tblname.rows.count;i++)
{
Tblname.rows[i]. Delete ();
}
Rows=da. Update (tblname);
return rows;
}
private static OleDbCommand Delcmd (DataTable DTL)
{
OleDbCommand delcmd=new OleDbCommand ();
DELCMD.CONNECTION=CNN;
String Sqlstr= "";
sqlstr = "Delete from" + DTL. Tablename.tostring () + "where";
for (int I=0;I<DTL. columns.count;i++)
{
SQLSTR = "([" + DTL. Columns[i]. ToString () + "] =? OR? is NULL and ["+dtl. Columns[i]. ToString () + "" is NULL) and ";
OleDbParameter Mypara = new OleDbParameter ();
Mypara.parametername = "or1_" + DTL. Columns[i]. ToString ();
Mypara.oledbtype = Getoledbtype (DTL. Columns[i]. Datatype.tostring ());
Mypara.direction = ParameterDirection.Input;
Mypara.sourcecolumn = DTL. Columns[i]. ToString ();
Mypara.sourceversion = datarowversion.original;
DELCMD.PARAMETERS.ADD (Mypara);
int j=delcmd.parameters.count;
BOOL B=dtl. Columns[i]. AllowDBNull;
if (b)
{
OleDbParameter myPara1 = new OleDbParameter ();
Mypara1.parametername = "or2_" + DTL. Columns[i]. ToString ();
Mypara1.oledbtype = Getoledbtype (DTL. Columns[i]. Datatype.tostring ());
Mypara1.direction = ParameterDirection.Input;
Mypara1.sourcecolumn = DTL. Columns[i]. ToString ();
Mypara1.sourceversion = datarowversion.original;
DELCMD.PARAMETERS.ADD (MYPARA1);
J=delcmd.parameters.count;
}
}
Sqlstr=sqlstr.substring (0,sqlstr.length-3);
Delcmd.commandtext = Sqlstr;
return delcmd;
}
#endregion Delcommand (Create Oledbdataadapter.deletecommand)
#region Amenddatabase
public void AddColumn (DataTable tblname,string Strup)//modify table structure, updating to database
{
Cnn. Open ();
OleDbCommand cmds=new OleDbCommand ("select * from" +TBLNAME.TABLENAME,CNN);
Da. Selectcommand=cmds;
OleDbCommandBuilder cb=new OleDbCommandBuilder (DA);
DataColumn Colitem = new DataColumn (Strup,type.gettype ("System.String"));
//
TBLNAME.COLUMNS.ADD (Colitem);
Why does the above method not work, can only use the SQL statement directly?
Da. Fill (tblname);
Da. Update (tblname);
}
#endregion amenddatabase
Calling stored procedures
#region Execproc (return dataTable)
<summary>
Executing stored procedures
</summary>
<param name= "procname" > Stored procedure name </param>
<param name= The value of "Paravalue" > Parameters </param>
<param name= "Paraname" > Parameter name </param>
<param name= "paratype" > Parameter type </param>
<returns></returns>
Public DataTable Execproc (string procname,string[] paravalue,string[] paraname,string[] paratype)
{
OleDbCommand cmdp=new OleDbCommand ();
CMDP. CONNECTION=CNN;
Cmdp.commandtype=commandtype.storedprocedure;
Cmdp.commandtext=procname;
for (int i=0;i<paraname.length;i++)
{
OleDbParameter pt=new OleDbParameter ();
paraname[i]= "@" +paraname[i];
Parameter name
Pt. Parametername=paraname[i];
Pt. Sourcecolumn=paraname[i];
Pt. Oledbtype=getoledbtype (Paratype[i]);
Pt. Value=paravalue[i];
CMDP. Parameters.Add (PT);
}
DataTable dtl=new DataTable ();
Cnn. Open ();
Da. SELECTCOMMAND=CMDP;
Da. Fill (DTL);
Cnn. Close ();
return DTL;
}
<summary>
Sets the DbType of the OleDbParameter object (the string into the corresponding OleDbType type)
</summary>
<param name= "Type" > string of incoming arguments </param>
<returns></returns>
private static OleDbType Getoledbtype (String type)
{
Try
// {
Return (OleDbType) Enum.parse (typeof (OleDbType), type, true);
// }
Catch
// {
return OleDbType.VarChar;
// }
Switch (type)
{
Case "Date":
return oledbtype.dbdate;
Break
Case "num":
return OleDbType.Integer;
Break
Default
return OleDbType.VarChar;
}
}
#endregion Execproc (return dataTable)
}
