The DataSet update is automatically submitted to the database tool class for implementation.

In the project, you need to submit the DataSet changes to the database. Therefore, a tool class is implemented to complete this task.

Design Philosophy: each row in each able in the DataSet has a state (RowState), and The DataTable provides a GetChanges (DataRowState state) method to obtain the changed data set, returns a new able object. Therefore, the Insert, Update, and Delete operations are handled in the order of data submission, and the SQL statements are automatically constructed based on the ing with the database table to complete data submission using the SqlDataAdapter.

Because. net basic data type and SQL Server data type have many-to-one relationship, and when using the data adapter (DataAdapter, to construct an SQL parameter object, you must specify the length of each parameter object and more information (when the ing data has a version). Therefore, it is difficult to derive the corresponding SqlDbType Based on the DataColumn type, therefore, a new class is implemented as needed to save the ing and parameters of each column.

The following is the original implementation code. There are many areas for improvement. I hope you can provide better comments and suggestions. Thank you!
Using System;
Using System. Data;
Using System. Data. Common;
Using System. Data. SqlClient;
Using System. Text;

Namespace kim. Data
{
/// <Summary>
/// Summary description for SqlDbAccess.
/// </Summary>
Public class SqlDbAccess
{
Static SqlDbAccess ()
{
}

/// <Summary>
/// Process the input database table name and add [] to avoid errors because the table name is a keyword in the database.
/// Add the owner name when the owner name does not exist.
/// </Summary>
/// <Param name = "dbTableName"> name of the database table to be processed. </Param>
/// <Returns> name of the processed database table. </Returns>
Private static string PrepareDbTableName (string dbTableName)
{
String [] tableName = dbTableName. Split ('.');
If (tableName. Length = 1) // only the table name is available.
{
If (tableName [0] [0] = '[')
Return tableName [0]; // [tableName] format
Else
Return string. Concat ("dbo. [", dbTableName, "]"); // tableName format
}
Else
{
StringBuilder text = new StringBuilder (100 );
For (int I = 0; I <tableName. Length; I ++)
{
If (tableName [0] = '[') // [xx] format
Text. Append (tableName [0] + '.');
Else // xx format
Text. Append ("[" + tableName + "].");
}
Text. Remove (text. Length-1, 1 );

Return text. ToString ();
}
}

/// <Summary>
/// Update the able changes to the database.
/// Concurrency control adopts "Last Update effective"
/// </Summary>
/// <Param name = "table"> valid DataTable object to be submitted </param>
/// <Param name = "sqlConn"> valid data is the connection object. </param>
/// <Param name = "dbTableName"> valid database table name </param>
/// <Param name = "dataColumnMappings"> A valid array of columns and parameter ing objects to be submitted </param>
/// <Param name = "keyColumnMappings"> an array of valid condition columns and parameter ing objects </param>
/// <Param name = "needTransaction"> whether a transaction is required </param>
Public static void UpdateData (DataTable table, SqlConnection sqlConn, string dbTableName,
ParameterColumnMapping [] dataColumnMappings, ParameterColumnMapping [] keyColumnMappings, bool needTransaction)
{
If (table = null) throw new ArgumentNullException ("table ");
If (sqlConn = null) throw new ArgumentNullException ("sqlConn ");
If (dbTableName = null | dbTableName. Length = 0) throw new ArgumentNullException ("dbTableName ");
If (dataColumnMappings = null | dataColumnMappings. Length = 0) throw new ArgumentNullException ("dataColumnMappings ");
If (keyColumnMappings = null | keyColumnMappings. Length = 0) throw new ArgumentNullException ("keyColumnMappings ");
If (table. Rows. Count = 0) return;

DbTableName = PrepareDbTableName (dbTableName );

// Set whether the connection should be closed after completion.
// If the connection object is closed when it is passed in, it should also be closed after it is used up. That is, keep the status before the connection is passed in.
Bool connNeedClose = (sqlConn. State = ConnectionState. Closed );

SqlDataAdapter sqlAdapter = new SqlDataAdapter ();

// Add Table ing and column ing
DataTableMapping tableMapping = sqlAdapter. TableMappings. Add (dbTableName, table. TableName );
For (int I = 0; I <dataColumnMappings. Length; I ++)
{
TableMapping. ColumnMappings. Add (dataColumnMappings. SourceColumn, dataColumnMappings. DataTableColumn );
}

// Construct the WHERE condition Section
StringBuilder sqlWhere = new StringBuilder (256 );
SqlWhere = new StringBuilder (256 );
SqlWhere. Append ("WHERE ");
For (int I = 0; I <keyColumnMappings. Length; I ++)
{
If (I = 0)
{
SqlWhere. Append (string. Format ("[{0}] =@{ 0}", keyColumnMappings. SourceColumn ));
}
Else
{
SqlWhere. Append (string. Format ("AND [{0}] =@{ 0}", keyColumnMappings. SourceColumn ));
}
}

StringBuilder sqlText = new StringBuilder (1024 );
SqlTransaction sqlTran = null;
DataTable tempTable = null;
DataRow [] rows = new DataRow [1];

// Start data submission
Try
{
If (connNeedClose) sqlConn. Open ();

If (needTransaction) sqlTran = sqlConn. BeginTransaction ();
SqlCommand sqlCmd = new SqlCommand ("", sqlConn, sqlTran );

// Process Insert
TempTable = table. GetChanges (DataRowState. Added );
If (tempTable! = Null)
{
SqlAdapter. InsertCommand = sqlCmd;
StringBuilder valueText = new StringBuilder (256 );
// Loop each row and process only columns not Null
Foreach (DataRow dr in tempTable. Rows)
{
SqlCmd. Parameters. Clear ();
SqlText. Length = 0;
SqlText. Append ("insert into" + dbTableName + "(");
For (int I = 0; I <dataColumnMappings. Length; I ++)
{
If (dr [dataColumnMappings. DataTableColumn]! = DBNull. Value)
{
SqlText. Append (string. Format ("[{0}],", dataColumnMappings. SourceColumn ));
ValueText. Append (string. Format ("@ {0},", dataColumnMappings. SourceColumn ));
SqlCmd. Parameters. Add (dataColumnMappings. DbParameter );
}
}

// Remove ',' from the end ','
SqlText. Remove (sqlText. Length-1, 1 );
ValueText. Remove (valueText. Length-1, 1 );

// Construct and submit the Insert statement
SqlText. Append (string. Format (") VALUES ({0})", valueText. ToString ()));
SqlCmd. CommandText = sqlText. ToString ();
Rows [0] = dr;
SqlAdapter. Update (rows );
}
}

// Process Update
// Update only the columns that have changed
TempTable = table. GetChanges (DataRowState. Modified );
If (tempTable! = Null)
{
Int changedColumns = 0; // used to record the number of changed Columns
SqlAdapter. UpdateCommand = sqlCmd;
Foreach (DataRow dr in tempTable. Rows)
{
SqlCmd. Parameters. Clear ();
SqlText. Length = 0;
SqlText. Append (string. Format ("UPDATE {0} SET", dbTableName ));
ChangedColumns = 0;
For (int I = 0; I <dataColumnMappings. Length; I ++)
{
// The current version is different from the original version.
If (! Dr [dataColumnMappings. DataTableColumn, DataRowVersion. Current]. Equals (
Dr [dataColumnMappings. DataTableColumn, DataRowVersion. Original])
{
SqlText. Append (string. Format ("[{0}] =@{ 0},", dataColumnMappings. SourceColumn ));
SqlCmd. Parameters. Add (dataColumnMappings. DbParameter );
ChangedColumns ++;
}
}

// This operation is performed only when a column is changed to prevent the row status from changing but the value is not changed.
If (changedColumns> 0)
{
SqlText. Remove (sqlText. Length-1, 1); // Remove ','
SqlText. Append ("" + sqlWhere. ToString ());

// Add Condition Parameters
For (int j = 0; j <keyColumnMappings. Length; j ++)
{
SqlCmd. Parameters. Add (keyColumnMappings [j]. DbParameter );
}

// Submit data
SqlCmd. CommandText = sqlText. ToString ();
Rows [0] = dr;
SqlAdapter. Update (rows );
}
}
}

// Process Delete
TempTable = table. GetChanges (DataRowState. Deleted );
If (tempTable! = Null & tempTable. Rows. Count> 0)
{
SqlText. Length = 0;
SqlText. Append ("delete from" + dbTableName + sqlWhere. ToString ());

SqlCmd. CommandText = sqlText. ToString ();
SqlCmd. Parameters. Clear ();
// Add Condition Parameters
For (int j = 0; j <keyColumnMappings. Length; j ++)
{
SqlCmd. Parameters. Add (keyColumnMappings [j]. DbParameter );
}

SqlAdapter. DeleteCommand = sqlCmd;
SqlAdapter. Update (tempTable );
}

If (sqlTran! = Null) sqlTran. Commit ();
}
Catch (Exception ex)
{
If (sqlTran! = Null)
{
SqlTran. Rollback ();
}
Throw ex;
}
Finally
{
If (connNeedClose) sqlConn. Close ();
}
}

/// <Summary>
/// Class that defines the ing between parameters and columns.
/// </Summary>
Public class ParameterColumnMapping
{
Private string m_SourceColumn;
Private string m_DataTableColumn;
Private SqlParameter m_DbParameter;

/// <Summary>
/// Constructor.
/// </Summary>
/// <Param name = "sourceColumn"> name of the database source column </param>
/// <Param name = "dataTableColumn"> column name in DataTable </param>
/// <Param name = "dbParameter"> parameter object </param>
Public ParameterColumnMapping (string sourceColumn, string ableablecolumn, SqlParameter dbParameter)
{
M_SourceColumn = sourceColumn;
M_DataTableColumn = dataTableColumn;
M_DbParameter = dbParameter;
SetParameterSourceColumn ();
}

Public ParameterColumnMapping (): this (null, null, null)
{
}

/// <Summary>
/// Obtain or set the name of the database source column.
/// </Summary>
Public string SourceColumn {
Get {
Return m_SourceColumn;
}
Set {
M_SourceColumn = value;
}
}

/// <Summary>
/// Obtain or set the column name in the DataTable object.
/// </Summary>
Public string DataTableColumn {
Get {
Return m_DataTableColumn;
}
Set {
M_DataTableColumn = value;
}
}

/// <Summary>
/// Obtain or set the SQL parameter object corresponding to the column.
/// </Summary>
Public SqlParameter DbParameter {
Get {
Return m_DbParameter;
}
Set {
M_DbParameter = value;
SetParameterSourceColumn ();
}
}

Private void SetParameterSourceColumn ()
{
If (m_DbParameter.SourceColumn = null | m_DbParameter.SourceColumn = string. Empty)
& (M_DataTableColumn! = Null & m_DataTableColumn! = String. Empty ))
{
M_DbParameter.SourceColumn = m_DataTableColumn;
}
}
}
}
}
**************************************** **********

SqlDbAccess. ParameterColumnMapping [] dataColumnMappings = new kim. Data. SqlDbAccess. ParameterColumnMapping [2];
DataColumnMappings [0] = new kim. Data. SqlDbAccess. ParameterColumnMapping ("APP_ID", myData. AppSystems. AppIDColumn. ColumnName,
New SqlParameter ("@ APP_ID", SqlDbType. NVarChar, 20, myData. AppSystems. AppIDColumn. ColumnName ));
DataColumnMappings [1] = new kim. Data. SqlDbAccess. ParameterColumnMapping ("DESCRIPTION", myData. AppSystems. DescriptionColumn. ColumnName,
New SqlParameter ("@ DESCRIPTION", SqlDbType. NVarChar, 500, myData. AppSystems. DescriptionColumn. ColumnName ));

SqlDbAccess. ParameterColumnMapping [] keyColumnMappings = new kim. Data. SqlDbAccess. ParameterColumnMapping [1];
KeyColumnMappings [0] = new kim. Data. SqlDbAccess. ParameterColumnMapping ("APP_ID", myData. AppSystems. AppIDColumn. ColumnName,
New SqlParameter ("@ APP_ID", SqlDbType. NVarChar, 20, ParameterDirection. Input, true, 0, 0, myData. AppSystems. AppIDColumn. ColumnName, DataRowVersion. Original, null ));

SqlConnection sqlConn = new SqlConnection (Configuration. D2WHP01_ConnString );
SqlDbAccess. UpdateData (myData. AppSystems, sqlConn, "AzProvider. dbo. APP", dataColumnMappings, keyColumnMappings, true );

Ps: disadvantage: Use sqlAdapter. Update because it is updated once for each piece of data (low efficiency)