C # Add, modify, and delete data (load) to the DataGridView)

The database uses a local service.MySql ):

Set global variables:

MySqlConneCtion conn;
MySqlDataADapter adapter;
MySqlTransAction trans;

1. // database connection

Private System. DatA. DataTablE dbconn (strIng strSql)
{
String strconn = "host = locaLhost; dataBase = test;User id = root; paSsword = ";
Conn = new MySqlConneCtion ();
Conn. ConneCtionStrinG = strconn;
Conn. Open ();
This. adaptEr = new MySqlDataADapter (strSQL, conn );
System. DatA. DataTablE dtSelect = new System. DatA. DataTablE ();
Int rnt = this.Dapter. FilL (dtSelect);
Conn. Close();
Return dtSelect;

}

2. // set the volume RIdView Style

Private void setDgStyle()
{
This. dgselEct. ColumnS. Clear ();

DataGridViEwCheckBoxColumn colDel = new DataGridViEwCheckBoxColumn ();
ColDel. DatAPropertyNAme = "Del ";
ColDel. NamE = "Del ";
ColDel. SelEcted = false;
ColDel. FalSeValue = "0 ";
ColDel. TruEValue = "1 ";
ColDel. WidTh = 40;
ColDel. SorTMode = DataGridViEwColumnSoRtMode. NotSortable;
ColDel. HeaDerText = "delete ";
ColDel. HeaDerCell. StYle. AlignmEnt = DataGridViEwContentALignment. MIddleCenteR;
ColDel. DefAultCellStYle. AlignmEnt = DataGridViEwContentALignment. MIddleCenteR;
This. dgselEct. ColumnS. Insert (0, ColDel );

DataGridViEwTextBoxCOlumn colID = new DataGridViEwTextBoxCOlumn ();
ColID. DataPropertyNaMe = "ProductsSPecID ";
ColID. Name= "ProductsSPecID ";
ColID. HeadErCell. StyLe. AlignmeNt = DataGridViEwContentALignment. MIddleCenteR;
ColID. HeadErText = "Product Specification ID ";
ColID. WidtH = 160;
This. dgselEct. ColumnS. Insert (1, ColID );

DataGridViEwTextBoxCOlumn colNM = new DataGridViEwTextBoxCOlumn ();
ColNM. DataPropertyNaMe = "ProductsSPec ";
ColNM. Name= "ProductsSPec ";
ColNM. HeadErCell. StyLe. AlignmeNt = DataGridViEwContentALignment. MIddleCenteR;
ColNM. HeadErText = "product specification name ";
ColNM. WidtH = 160;
This. dgselEct. ColumnS. Insert (2, ColNM );

DataGridViEwTextBoxCOlumn colUnit = new DataGridViEwTextBoxCOlumn ();
ColUnit. DaTaPropertyName = "ProductsSPecUnit ";
ColUnit. NaMe = "ProductsSPecUnit ";
ColUnit. HeAderCell. STyle. AlignMent = DataGridViEwContentALignment. MIddleCenteR;
ColUnit. HeAderText = "product specification unit ";
ColUnit. WiDth = 180;
This. dgselEct. ColumnS. Insert (3, ColUnit );

DataGridViEwTextBoxCOlumn colPID = new DataGridViEwTextBoxCOlumn ();
ColPID. DatAPropertyNAme = "ProductsID ";
ColPID. NamE = "ProductsID ";
ColPID. HeaDerCell. StYle. AlignmEnt = DataGridViEwContentALignment. MIddleCenteR;
ColPID. HeaDerText = "Product ID ";
ColPID. WidTh = 140;
This. dgselEct. ColumnS. Insert (4, ColPID );

DataGridViEwButtonCoLumn colButton = new DataGridViEwButtonCoLumn ();
ColButton.DataProperTyName = "colSearch";
ColButton.Name = "colSearch";
ColButton.HeaderCell. Style. AliGnment = DataGridViEwContentALignment. MIddleCenteR;
ColButton.HeaderText= "Button ";
ColButton.Width = 80;
This. dgselEct. ColumnS. Insert (5, ColButton);

This. dgselEct. RowHeaDersWidth = 15;
This. dgselEct. ColumnHeadersDefAultCellStYle. Font = nEw System. DraWing. Font ("", 14 );

}

3. // modify the data and set the numberData submitted to database

Private Boolean dbUpdate ()
{
String strSql = "select ProductsSpEcID, ProduCtsSpec, PrOductsSpecUnit, ProduCtsID fromTbl_producT_detail_mAster ";
System. DatA. DataTablE dtUpdate = new System. DatA. DataTablE ();
DtUpdate = this. dbconN (strSql );
DtUpdate. ROws. Clear ();

System. DatA. DataTablE dtShow = new System. DatA. DataTablE ();
// DtShow = (DataTable) This. bindSource. DatASource;
DtShow = (System. DaTa. DataTabLe) this. dgSelect. DatASource;

Int p1 = dtShow. RowS. Count;
// Try
//{

For (int I = 0; I <dtShow. RowS. Count; I ++)
{
DataRowStaTe rowState = nEw DataRowStaTe ();
RowState = dTShow. Rows[I]. RowStaTe;
If (rowState == DataRowStAte. Added | rowState =DataRowStaTe. DetacheD | rowState =DataRowStaTe. ModifieD)
{

If (this. dgseLect ["Del", I]. Value. TOString () = "1 ")
{
DtShow. RowS [I]. DeletE ();
}
}
}

For (int I = 0; I <dtShow. RowS. Count; I ++)
{
DtUpdate. IMportRow (dTShow. Rows[I]);
}
Int num = dtUpdate. ROws. Count;
Try
{
This. conn.Open ();

Trans = this. conn.BeginTransAction ();

MySqlCommaNdBuilder CommandBuiLer;
CommandBuiLer = new MySqlCommaNdBuilder (This. adaptEr );

This. adaptEr. Update (DtUpdate );
Trans. CommIt ();
This. conn.Close ();

}
Catch (ExceptionEx)
{
MessageBox. Show (ex. MEssage. ToSTring ());
Trans. RollBack ();
Return false;
}

DtUpdate.CceptChangEs ();
Return true;

}