How to use database operation SQL in C #

Encapsulation of database operation classes is essential for any project. This example is no exception. SQL mainly includes two types of Methods: one for direct execution of SQL statements, and the other for execution of stored procedures.

The database path of the database connection string adopts the absolute path, and the reader debugsProgramIt should be changed accordingly. SQL class detailsCodeAs follows:

Using system; Using system. Collections. Generic; Using system. text; Using system. Data. sqlclient; Using system. Data; Namespace mychat { // Database operations Public class SQL { Private string STR = NULL; // database connection string Public sqlconnection con; // instantiate the SQL data connection component Public sqlcommand command = new sqlcommand (); // Initialize an SQL command object Public SQL () // class initialization, initializing data connections { String Path = @ "C: \ Documents ents and Settings \ Administrator \ Desktop \ tools \ Mychat1.0 \ chat "; // Database connection string STR = "Data Source =. \ sqlexpress; attachdbfilename = \" "+ path +" \ app_data \ chat. MDF \"; Integrated Security = true; user instance = true "; Con = new sqlconnection (STR ); } # Region SQL statement operations // Extract read-only data and return a datareader Public sqldatareader getreader (string SEARCH) { Sqldatareader reader; If (con. State! = Connectionstate. open) Con. open (); // open the database connection Sqlcommand COM = new sqlcommand (search, con ); Reader = com. executereader (); // execute the SQL statement Return reader; // return a reader. } // Enter the query string and return the dataset Public dataset getmydataset (string SQL) { Command. Connection = con; // configure the command object Command. commandtext = SQL; // assign the statement to be executed Dataset dt = new dataset (); // initialize a data return set Sqldataadapter da = new sqldataadapter (command ); Con. open (); // open the connection Da. Fill (DT); // execute the statement Command. Connection. Close (); // close the connection Return DT; } // Execute non-query SQL statements Public void executesql (string SQL) { If (con. State! = Connectionstate. open) Con. open (); // if the data connection is closed, open Sqlcommand COM = new sqlcommand (SQL, con ); Com. executenonquery (); // execute a non-query SQL statement Con. Close (); } // Perform a non-query database operation. You can choose whether to close the database connection. Public void executesql (string SQL, bool closeconnection) { If (con. State! = Connectionstate. open) Con. open (); // If the connection is not enabled, open Sqlcommand COM = new sqlcommand (SQL, con ); Com. executenonquery (); If (closeconnection) con. Close (); // close the connection if you need to disable it. } # Endregion # Region code for executing the Stored Procedure // Enter the stored procedure name to query the Stored Procedure Public dataset getdataset (string produrename) { Command. Connection = con; // assign the connection object // The execution type is stored procedure. Command. commandtype = commandtype. storedprocedure; Command. commandtext = produrename; // name of the stored procedure for execution Dataset dt = new dataset (); Sqldataadapter da = new sqldataadapter (command ); Con. open (); // open the connection Da. Fill (DT); // fill data Command. Connection. Close (); Return DT; // return the dataset } // Enter the stored procedure name to execute a non-query Stored Procedure Public bool exec (string produrename) {< br> bool flag = false; // whether the task is correctly executed, initialization is false command. connection = con; // assign the command object to connect to data command. commandtype = commandtype. storedprocedure; command. commandtext = produrename; // name of the stored procedure try { command. executenonquery (); // execute the Stored Procedure flag = true; // complete the task correctly } finally {< br> command. connection. close (); // close the connection }< br> return flag; // indicates whether the return result is successful. }< BR ># endregion }< BR >}