Simple and practical DataSet Update database Class + summary (C #)

Previously used to update the database with SQL statements (update), it is not very convenient to use, especially in the case of large amount of data (such as data table) is very troublesome ~ ~ later felt that using dataset to update the database is a good choice. So I was anxious to write a class that updates the database with Ataset as follows: (Use the following instructions, summary)

Using System;

Using System.Data;

Using System.Data.SqlClient;

Using System.Windows.Forms;

Namespace Winapplication

{

public class Sqlaccess

{

Connection string settings with SQL Server

private string _connstring;

private string _strsql;

Private SqlCommandBuilder Sqlcmdbuilder;

Private DataSet ds = new DataSet ();

Private SqlDataAdapter da;

Public sqlaccess (String connstring,string strSQL)

{

this._connstring=connstring;

}

Private SqlConnection Getconn ()

{

Try

{

SqlConnection Connection = new SqlConnection (this._connstring);

Connection.Open ();

return Connection;

}

catch (Exception ex)

{

MessageBox.Show (ex. Message, "Database connection failed");

Throw

}

}

Retrieving database data based on an input SQL statement

Public DataSet selectdb (string strsql,string strtablename)

{

Try

{

This._strsql = strSQL;

This.da = New SqlDataAdapter (This._strsql,this. Getconn ());

This.ds.Clear ();

This.da.Fill (Ds,strtablename);

Return ds;//Returns a dataset filled with data, where the datasheet is named after the string given by strTableName

}

catch (Exception ex)

{

MessageBox.Show (ex. Message, "Database operation failed");

Throw

}

}

Database data updates (objects that pass datasets and DataTable)

Public DataSet Updateds (DataSet changedds,string tablename)

{

Try

{

This.da = New SqlDataAdapter (This._strsql,this. Getconn ());

This.sqlcmdbuilder = new SqlCommandBuilder (DA);

This.da.Update (Changedds,tablename);

Changedds.acceptchanges ();

Return changedds;//returns an updated database table

}

catch (Exception ex)

{

MessageBox.Show (ex. Message, "Database update failed");

Throw

}

}

Summary of Usage instructions:

1. The Getconn method creates a database connection and returns SqlConnection.

2. The select command you use must contain a primary key, as you all know!

3. This.da.Fill (Ds,strtablename) fills the dataset

4. When constructing the CommandBuilder object, pass the DataAdapter object as the constructor parameter:

This.sqlcmdbuilder = new SqlCommandBuilder (DA);

5. Before calling Updateds () to update the database, check to see if Changedds has been updated with Changedds. [TableName] GetChanges ()!= null;

6. Update the data using the This.da.Update (Changedds,tablename) method, and then call Changedds.acceptchanges () to really update the database, calling Changedds.rejectchanges () cancel the update.