C # Operations Access database

<pre name= "code" class= "CSharp" ><span style= "font-family:arial, Helvetica, Sans-serif; font-size:12px; Background-color:rgb (255, 255, 255); " >                                                                                                       </span><span style= "font-family:arial, Helvetica, Sans-serif; font-weight:normal; Background-colo R:rgb (255, 255, 255); " ><span style= "FONT-SIZE:24PX;" >c# Manipulating Access Databases </span></span>

This two-day project requires that data be stored in an Access database and displayed in the DataGridView Control. The syntax of an Access database differs from SQL in that it summarizes the database connection and additions and deletions to the basic operation. at the beginning of the program, I was going to use some database operation statements to create a database, but it didn't seem to be very successful. And if you want to create the database manually, you create the table when you start the program, to determine that there is no table. 1. Connect to the database first:The SEWWORKSTATION.ACCDB is the database to be used in the project.

  public static string DbPath = System.Windows.Forms.Application.StartupPath + "\\SewWorkStation.accdb";        public string dbName = "Provider=microsoft.ace.oledb.12.0;data source=" +dbpath+ ";";        public OleDbConnection oledbconn = null;        Public Dboperate ()        {            oledbconn = new OleDbConnection (dbName);        }

2. Create a table with a statement:  

public void createrobottable ()        {            try            {                oledbconn.open ();                String excutestr = "Create Table t_robot (rId int, rname text,rip text,rport text)";                OleDbCommand Oledbcomm = new OleDbCommand (EXCUTESTR, oledbconn);                Oledbcomm.executenonquery ();            }            catch (Exception e)            {                MessageBox.Show (e.message);            }            Finally            {                oledbconn.close ();            }        }

of course, you can set up a good database on the outside to build the table, and then only in the program to do the pruning operation, so there is no need to check whether the table exists. you might see me here. The properties of the table are created, inconsistent with the properties of the table at the bottom of the query. Ha ~, actually I created an empty table outside, this is just the syntax is recorded.
on the Internet to find a piece of code, to determine whether there is a table in the database, but I executed a few times, just created a table can not find, dttable is empty, if you have any good way to remind me,.

public bool Verifytableinaccess (string TableName) {BOOL flag = false;                try {oledbconn.open ();                DataTable dttable = oledbconn.getoledbschematable (OleDbSchemaGuid.Tables, new object[] {null, NULL, NULL, TableName});                    if (dttable = = null) {flag = false;                return flag; } foreach (DataRow drow in dttable.rows) {if (drow["table_name"]. ToString (). Trim (). ToUpper () = = Tablename.trim ().                        ToUpper ()) {flag = true;                    Break                }}} catch (Exception e) {MessageBox.Show (e.message);            Flag = false;            } finally {oledbconn.close ();        } return flag; }

3. Preview Table 

public void Showtable (String Tablename,datagridview DataGridView)        {            try            {                oledbconn.open ();                DataSet DataSet = new DataSet ();                OleDbDataAdapter adapter = new OleDbDataAdapter ();                OleDbCommand command = new OleDbCommand ("SELECT * from" + tableName, oledbconn);                Adapter. SelectCommand = command;                Adapter. Fill (DataSet);                Datagridview.datasource = Dataset.tables[0];            }            catch (Exception e)            {                MessageBox.Show (e.message);            }            Finally            {                oledbconn.close ();            }        }

This is used in the adapter OleDbDataAdapter, or can be read using OleDbDataReader. 4. Add Data

  public void Insert (string table, Object obj) {string insertstr = "";                try {oledbconn.open ();                OleDbCommand oledbcomm = null;                        Switch (table) {case "T_robot": Mrobot Robot = (mrobot) obj;                        Insertstr = "Insert into T_robot (robot number, robot name, robot IP, Robot port) Values (?,?,?,?)";                        Oledbcomm = new OleDbCommand (INSERTSTR, oledbconn); OleDbComm.Parameters.AddWithValue ("Robot number", Robot.                        ID); OleDbComm.Parameters.AddWithValue ("Robot name", Robot.                        Name); OleDbComm.Parameters.AddWithValue ("Robot IP", Robot.                        IP); OleDbComm.Parameters.AddWithValue ("Robotic port", robot.                        Port);                    Break                Default:break; } if (! "".        Equals (INSERTSTR)) {            Oledbcomm.executenonquery ();            }} catch (Exception e) {MessageBox.Show (e.message);            } finally {oledbconn.close ();        }

to add data here, you need to parameterize the query, using the Oledbcommand.parameters attribute.
5. Delete DataDelete the data by ID, start with the automatic ID in Access, but found that even after deleting a piece of data, the ID of the subsequent data is not automatically updated, if you add a new data, the ID is max (ID) +1, this should be noted.

 public            void Delete (string table,int ID) {String delstr = "";            String paramname = "";                          try {switch (table) {case "T_robot":                          DELSTR = "Delete * from T_robot where robot number =?";                          paramname = "RId";                    Break                Default:break; } if (! "".                    Equals (DELSTR)) {Oledbconn.open ();                    OleDbCommand Oledbcomm = new OleDbCommand (DELSTR, oledbconn);                    OleDbComm.Parameters.AddWithValue (paramname, id);                Oledbcomm.executenonquery ();            }} catch (Exception e) {MessageBox.Show (e.message); }

6. Update ID

 public void UpdateID (string table, int id) {String updatestr = "";                try {oledbconn.open ();                OleDbCommand oledbcomm = null; Switch (table) {case "T_robot": updatestr = "Update T_robot set                        Robot number = robot number-1 where robot number > @index ";                    Break                Default:break; } if (! "".                    Equals (UPDATESTR)) {Oledbcomm = new OleDbCommand (UPDATESTR, oledbconn);                    OleDbComm.Parameters.AddWithValue ("@index", id);                Oledbcomm.executenonquery ();            }} catch (Exception e) {MessageBox.Show (e.message);            } finally {oledbconn.close (); }               }

Note that the UPDATE statement above is updated tableName set id = id-1 where ID > delid, and you see that there is no Select...,access UPDATE statement that differs from the SQL UPDATE statement. 7. Modify the Data

   public void Update (string table, Object obj) {string updatestr = "";                try {oledbconn.open ();                OleDbCommand oledbcomm = null;                        Switch (table) {case "T_robot": Mrobot Robot = (mrobot) obj; UPDATESTR = "Update T_robot set robot name =?, Robot ip=?, Robot port =?"                        where robot number =? ";                        Oledbcomm = new OleDbCommand (UPDATESTR, oledbconn); OleDbComm.Parameters.AddWithValue ("Robot name", Robot.                        Name); OleDbComm.Parameters.AddWithValue ("Robot IP", Robot.                        IP); OleDbComm.Parameters.AddWithValue ("Robotic port", robot.                        Port); OleDbComm.Parameters.AddWithValue ("Robot number", Robot.                        ID);                    Break                Default:break; } if (! "". Equals (UPDATESTR)) {OLEDBCoMM.                ExecuteNonQuery ();            }} catch (Exception e) {MessageBox.Show (e.message);            } finally {oledbconn.close (); }               }

C # Operations Access database