C # Database Operations

Using system; using system. collections. generic; using system. LINQ; using system. text; // This package using system must be introduced for SQL database operations. data. sqlclient; // This package using system must be introduced when dataset class is used. data; namespace consoleapplication1 {class program {private string sqlconnectioncommand = "Server = localhost; database = testdb; uid = sa; Pwd = sa"; // "Server = localhost; database = testdb; Integrated Security = sspi "; // Windows authentication method private string sqlselecttable = "Select ID, name from Table1"; private string sqldelcommand = "delete from Table1 where id = 1 "; private string sqlupdatecommand = "Update Table1 set name = '99' where id = 2"; static void main (string [] ARGs) {program p1 = new program (); p1.testdbsetfun ();} public void dosqlselect () {sqlconnection = new sqlconnection (sqlconnectioncommand); // If the initialization constructor does not contain information about the connected database, you can use the property of the created object to associate the number of connections. Database Information // sqlconnection = new sqlconnection (); // sqlconnection. connectionstring = sqlconnectioncommand; sqlcommand = new sqlcommand (sqlselecttable, sqlconnection ); // create an object that accepts SQL statements and can execute returned results // If the initialization does not contain the SQL statement to be executed and the associated database connection, you can use the attributes of the created object. join // sqlcommand = new sqlcommand (); // create an object for executing the SQL statement class // sqlcommand. connection = sqlconnection; // The connected database object // sqlcommand. commandtext = sqls Electtable1; // associated SQL statement // before executing the SQL statement, you must open the connection to sqlconnection. open (); // if a non-query statement (Update, insert, etc.) is executed using the sqlcommand class executenonquery (); method, an int type // sqlcommand is returned. executenonquery (); // execute the query statement using the sqlcommand class executereader (); method. The sqldatareader class object is used to receive execution results so that sqldatareader = sqlcommand can be easily obtained. executereader (); // start looping over while (sqldatareader. read () {int id = (INT) sqldatareader ["ID"]; // (INT) sqldatareader [0]; // You can also set the number 0 of the query result column to string name = (string) sqldatareader ["name"]; // (string) sqldatareader ["name"]; console. writeline ("ID =" + ID + ", name =" + name);} system. console. writeline ("press any key to exit. "); system. CO Nsole. readkey (); sqldatareader. close (); sqlcommand. dispose (); sqlconnection. close ();} void testdbsetfun () {sqlconnection = new sqlconnection (sqlconnectioncommand); sqlcommand = new sqlcommand (sqlselecttable, sqlconnection); sqldataadapter = new sqldataadapter (); // create the sqldataadapter object for operations on the virtual table. selectcommand = sqlcommand; // associate sqldataadapter with a sqlcommand with query statements Object sqlconnection. open (); dataset dsdataset = new dataset (); // create a virtual table object sqldataadapter. fill (dsdataset, "MERs"); // use the sqldataadapter object to assign the newly associated query results to a virtual table, and assign a specified table name "customers" (any) sqlcommand. dispose (); sqlconnection. close (); dsdataset. tables ["MERs"]. rows [0]. delete (); // specify the index row to be deleted from the specified virtual table. The table name can also be replaced with the index value dsdataset stored in the dsdataset table. tables ["MERs"]. acceptchanges (); // you can call this operation to delete the specified index row of a specified virtual table. All changes made since acceptchanges () is called .) Sqldataadapter. update (dsdataset, "MERs"); // call the update method to update the corresponding table of the database with the data of the table specified in dsdataset but put it in acceptchanges () method, int a1 = dsdataset is not executed. tables [0]. rows. count; // get the total number of rows in the specified virtual table int a2 = dsdataset. tables ["MERs"]. columns. count; // obtain the total number of columns in the specified virtual table. Int a3 = dsdataset. tables. count; // obtain the total number of columns in the specified virtual table string A4 = dsdataset. tables ["MERs"]. columns [0]. tostring (); // obtain the column name (field name) of the 0 column of the index of the specified virtual table // obtain the value of string val = dsdataset for the specified index row of the specified virtual table. tables ["MERs"]. rows [1] ["name"]. tostring (); // You can also use the index of this column in the virtual table. // string B = dsdataset. tables ["MERs"]. rows [1] [1]. tostring (); console. writeline ("rowscount =" + A1 + ", val =" + val); system. console. writeline ("press any key to exit. "); system. console. readkey ();}}}