Many times we are not only to operate on a table in the database, but also simultaneously operate multiple tables, and there must be transactions, so that the data is complete and correct.
For this situation, DataSet is a good choice. There may be some effects in efficiency, as long as the amount of data is not massive, I want to be impossible to have a big problem. By expanding the DataSet, we can add several tables to the DataSet, after modifying the data, uploaded to the database
For example: extended DataSet is: CustomDataSet
DS = New CustomDataSet ("Table1", ""
DS.ADDDATABLE ("Table2", "");
DataRow Dr = DS.GetNewrow ("Table1", TRUE);
DR [1] = "ha";
DR = DS.GetNewRow ("Table2", True);
DR [1] = "ha";
DS.UPDATA ();
The extended DataSet is as follows:
///
/// Custom DataSet, package to the database ///
Public Class CustomDataSet: DataSet
{
Private SQLServer SQLServer;
Private arraylist tablenames;
Private arraylist sqldataadapters;
Public CustomDataSet () {init ();} ///
/// Object instantiation ///
///
Table Name
///
condition
Public CustomDataSet (String Tablename, String Condition)
{
INIT ();
AddDataTable (TableName, Condition);
}
Private void init () {Tablenames = new arraylist (); sqldataadapters = new arraylist (); sqlserver = sqlser.instance ();
///
/// Add a data sheet ///
///
Table Name
///
condition
Public void adddtatable (String Tablename, String Condition)
{
String Sqlstring = "SELECT *" TABLENAME;
IF (Condition! = Null && Condition! = "" && condition.trim ()! = "")
Sqlstring = "where" condition;
SqlDataAdapter SqldataAdapter = New SqlDataAdapter (Sqlstring, SqlServer.Connection);
SQLCommandbuilder Custcb = New Sqlcommandbuilder (SqlDataAdapter);
sqlServer.Connection.Open (); try {sqlDataAdapter.FillSchema (this, SchemaType.Source, TableName); sqlDataAdapter.Fill (this, TableName);} finally {sqlServer.Connection.Close ();} tableNames.Add (TableName) Sqldataadapters.add (sqldataadapter);
///
/// Submit ///
Public void updata ()
{
UpdataTodatabase (false);
}
///
/// Submit ///
Public void Updata (Bool Flagorder)
{
UpdataTodatabase (Flagorder);
}
Private Void Updataodatabase (Bool Flagorder) {Try {SQLServer.connection.Open (); SQLServer.Begintractions (); if (flagorder) {for (int i = TableNames.count-1; i> = 0; i -) { SqlDataAdapter sqlDataAdapter = (SqlDataAdapter) sqlDataAdapters [i]; sqlDataAdapter.SelectCommand.Transaction = sqlServer.SqlTran; sqlDataAdapter.Update (this.Tables [i]);}} else {for (int i = 0; i
{
SqlDataAdapter SqldataAdapter = (SqlDataAdapter) SqlDataAdapters [i];
SqlDataAdapter.selectCommand.transaction = SQLSERVER.SQLTRAN;
SqldataAdapter.Update (this.tables [i]);
}
}
SQLSERVER.committransaction ();
}
Catch
{
SQLSERVER. ROLLBACKTRANSACTION ();
Throw;
}
Finally
{
SQLSERVER.CONNECTION.CLOSE ();
}
}
///
/// Get a new line ///
///
Table Name
///
The flag, True means that the new row is added to the table, and false is reversed.
///
New row reference
Public DataRow GetNewRow (String Tablename, Bool Flagadd)
{
DataRow Dr = this.tables [TableName] .newrow ();
IF (FlagAdd)
THIS.TABLES [TABLENAME] .ROWS.ADD (DR);
Return DR;
}
///
/// Get a new line ///
///
Table index
///
The flag, True means that the new row is added to the table, and false is reversed.
///
New row reference
Public DataRow GetNewRow (int index, bool flagadd)
{
DataRow Dr = this.tables [index] .newrow ();
IF (FlagAdd)
THIS.TABLES [INDEX]. ROWS.ADD (DR);
Return DR;
}
///
/// Add new row ///
///
Table Name
/// New line
Public Void AddNewRow (String Tablename, DataRow DR)
{
THIS.TABLES [TABLENAME] .ROWS.ADD (DR);
}
///
/// Add new row ///
///
Table index
///
New line
Public Void AddNewRow (Int Index, DataRow DR)
{
THIS.TABLES [INDEX]. ROWS.ADD (DR);
}
///
/// Get a row ///
///
Table Name
///
index
///
Row reference
Public DataRow GetRow (String Tablename, INDEX)
{
Return this.tables [TABLENAME] .ROWS [INDEX];
}
///
/// Get a row ///
///
Table index
///
Line index
///
Row reference
Public DataRow GetRow (int Tindex, int rindex)
{
Return this.tables [TINDEX]. Rows [rindex];
}
///
/// Get a line set ///
///
Table Name
///
condition
///
Row
Public DataRow [] GetRows (String Tablename, String Condition)
{
Return this.tables [TableName] .Select (Condition, Null, DataViewRowState.currentrows);
}
///
/// Get a line set ///
///
Table index
///
condition
///
Row
Public DataRow [] GetRows (int index, string condition)
{
Return this.tables [Index] .Select (Condition, Null, DataViewRowState.currentrows);
}
///
/// Delete one page ///
///
Table Name
///
index
Public void deleterow (String Tablename, INDEX)
{
THIS.TABLES [TABLENAME] .ROWS [INDEX] .delete ();
}
///
/// Delete one page ///
///
Table index
///
Line index
Public void deleterow (int TINDEX, INT RINDEX)
{
THIS.TABLES [TINDEX] .ROWS [RINDEX] .delete ();
}
///
/// Delete rowset ///
///
Table Name
///
condition
Public void deleterows (String Tablename, String Condition)
{
Foreach (DataRow Dr in GetRows (TableName, Condition)
Dr.delete ();
}
///
/// Delete rowset ///
///
Table index
///
condition
Public void deleterows (int index, string condition)
{
Foreach (DataRow Dr in GetRows (Index, Condition)
Dr.delete ();
}
///
/// Remove all records in the table ///
///
Table Name
Public void deletedTATABLE (String Tablename)
{
Foreach (Datarow Dr in this.tables ") [TableName] .Select (Null, Null, DataViewRowState.currentrows))
Dr.delete ();
}
///
/// Remove all records in the table ///
///
Table index
Public void deletedATABLE (int index) {
Foreach (DataRow Dr in this.tables [index] .select (null, null, dataviewrowstate.currentrows))
Dr.delete ();
}
///
/// Delete all tables ///
Public void deleteall ()
{
For (int i = 0; i
{
Foreach (DataRow Dr in this.tables [i] .select (null, null, dataviewrowstate.currentrows))
Dr.delete ();
}
}
///
/// Set the primary key ///
///
Table Name
///
key
Public void setprimaryKey (String Tablename, params string [] keys)
{
Datacolumn [] dc = new datacolumn [keys.Length];
For (int i = 0; i
{
DC [i] = this.tables [tablename] .columns [keys [i]];
}
THIS.TABLES [TABLENAME] .primaryKey = DC;
}
///
/// Set the primary key ///
///
Table index
///
key
Public void setprimarykeyKey (int index, params string [] keys)
{
Datacolumn [] dc = new datacolumn [keys.Length];
For (int i = 0; i
{
DC [I] = this.tables [index] .columns [keys [i]];
}
THIS.TABLES [INDEX] .primaryKey = DC;
}
}
