Common Asp. Net SQL server operations

SQL Server-based data generic

Using system; <br/> using system. collections. generic; <br/> using system. LINQ; <br/> using system. text; <br/> using system. data; <br/> using system. data. sqlclient; <br/> using system. configuration; </P> <p> namespace Dal <br/>{< br/> /// <summary> <br/> // Description: Common Data Access class, for internal Dal call <br/> /// </Summary> <br/> partial class sqldbhelper <br/>{< br/> private string connectionstring; </P> <p> // <summary> <br/> // set Set the database connection string <br/> /// </Summary> <br/> Public String connectionstring <br/>{< br/> get {return connectionstring ;} <br/> set {connectionstring = value ;}< br/>}</P> <p> Public sqldbhelper () </P> <p >{< br/> connectionstring = configurationmanager. connectionstrings ["con"]. connectionstring; </P> <p >}</P> <p> // <summary> <br/> // constructor <br/> // </Summary> <br/> // <Param name = "connectstring"> database connection string </Param> <br/> Public sqldbhelper (string connectstring) <br/>{< br/> This. connectionstring = connectionstring; <br/>}</P> <p> /// <summary> <br/> /// execute a query, and return the result set <br/> /// </Summary> <br/> /// <Param name = "SQL"> SQL text query command to be executed </Param> <br/> // <returns> return query result set </returns> <br/> Public datatable executedatatable (string SQL) <br/>{< br/> return executedatatable (SQL, commandtype. text, null); <br/>}< Br/> /// <summary> <br/> // execute a query, return the query result <br/> /// </Summary> <br/> /// <Param name = "SQL"> SQL statement to be executed </param> <br/> // <Param name = "commandtype"> type of the query statement to be executed, for example, stored procedures or SQL text commands </param> <br/> // <returns> return the query result set </returns> <br/> Public datatable executedatatable (string SQL, commandtype) <br/>{< br/> return executedatatable (SQL, commandtype, null ); <br/>}< br/> /// <summary> <br/> // Execute One query, return the query result <br/> /// </Summary> <br/> /// <Param name = "SQL"> SQL statement to be executed </param> <br/> // <Param name = "commandtype"> type of the query statement to be executed, for example, stored procedures or SQL text commands </param> <br/> // <Param name = "Parameters"> parameter arrays of transact-SQL statements or stored procedures </param> <br/> // <returns> </returns> <br/> Public datatable executedatatable (string SQL, commandtype, sqlparameter [] parameters) <br/>{< br/> datatable DATA = new datatable (); // Instantiate the datatable for loading the query result set <br/> using (sqlconnection connection = new sqlconnection (connectionstring )) <br/>{< br/> using (sqlcommand command = new sqlcommand (SQL, connection) <br/>{< br/> command. commandtype = commandtype; // set the commandtype of command to the specified commandtype. <br/> // If parameters are input at the same time, add these parameters. <br/> If (parameters! = NULL) <br/>{< br/> foreach (sqlparameter parameter in parameters) <br/>{< br/> command. parameters. add (parameter ); <br/>}< br/> // instantiate sqldataadapter by using the sqlcommand instance that contains the query SQL statement <br/> sqldataadapter adapter = new sqldataadapter (command ); </P> <p> adapter. fill (data); // fill in the datatable <br/>}< br/> return data; <br/>}< br/> /// <summary> <br/> /// </Summary> <br/> /// <Param name =" SQL "> query SQL text commands to be executed </param> <br/> // <returns> </returns> <br/> Public sqldatareader executereader (string SQL) <br/>{< br/> return executereader (SQL, commandtype. text, null ); <br/>}< br/> /// <summary> <br/> /// </Summary> <br/> /// <Param name = "SQL"> SQL statement to be executed </param> <br/> // <Param name = "commandtype"> type of the query statement to be executed, such as stored procedures or SQL text commands </param> <br/> // <returns> </returns> <br/> Public sqldat Areader executereader (string SQL, commandtype) <br/>{< br/> return executereader (SQL, commandtype, null ); <br/>}< br/> /// <summary> <br/> /// </Summary> <br/> /// <Param name = "SQL"> SQL statement to be executed </param> <br/> // <Param name = "commandtype"> type of the query statement to be executed, for example, stored procedures or SQL text commands </param> <br/> // <Param name = "Parameters"> parameter arrays of transact-SQL statements or stored procedures </param> <br/> // <returns> </returns> <br/> Public sqldatareader executereader (string SQL, commandtype, sqlparameter [] parameters) <br/>{< br/> sqlconnection connection = new sqlconnection (connectionstring ); <br/> sqlcommand command = new sqlcommand (SQL, connection); <br/> // if both parameters are input, add these parameters. <br/> If (parameters! = NULL) <br/>{< br/> foreach (sqlparameter parameter in parameters) <br/>{< br/> command. parameters. add (parameter); <br/>}< br/> connection. open (); <br/> // commandbehavior. the closeconnection parameter indicates that the connection object associated with the reader is closed when the reader object is closed <br/> return command. executereader (commandbehavior. closeconnection ); <br/>}< br/> /// <summary> <br/> /// </Summary> <br/> /// <Param name = "SQL"> query SQL text to be executed </Param> <br/> // <returns> </returns> <br/> Public object executescalar (string SQL) <br/>{< br/> return executescalar (SQL, commandtype. text, null ); <br/>}< br/> /// <summary> <br/> /// </Summary> <br/> /// <Param name = "SQL"> SQL statement to be executed </param> <br/> // <Param name = "commandtype"> type of the query statement to be executed, such as stored procedures or SQL text commands </param> <br/> // <returns> </returns> <br/> Public object executescalar (string SQ L, commandtype) <br/>{< br/> return executescalar (SQL, commandtype, null ); <br/>}< br/> /// <summary> <br/> /// </Summary> <br/> /// <Param name = "SQL"> SQL statement to be executed </param> <br/> // <Param name = "commandtype"> type of the query statement to be executed, for example, stored procedures or SQL text commands </param> <br/> // <Param name = "Parameters"> parameter arrays of transact-SQL statements or stored procedures </param> <br/> // <returns> </returns> <br/> Public object executescalar (Str Ing SQL, commandtype, sqlparameter [] parameters) <br/>{< br/> Object result = NULL; <br/> using (sqlconnection connection = new sqlconnection (connectionstring )) <br/>{< br/> using (sqlcommand command = new sqlcommand (SQL, connection) <br/>{< br/> command. commandtype = commandtype; // set the commandtype of command to the specified commandtype. <br/> // If parameters are input at the same time, add these parameters. <br/> If (parameters! = NULL) <br/>{< br/> foreach (sqlparameter parameter in parameters) <br/>{< br/> command. parameters. add (parameter); <br/>}< br/> connection. open (); // open the database connection <br/> result = command. executescalar (); <br/>}< br/> return result; // return the first column of the first row of the query result, ignore other rows and columns <br/>}< br/> // <summary> <br/> // add, delete, and modify a database <br/> /// </Summary> <br/> /// <Param name = "SQL"> SQL text query command to be executed </param> <br/> // <r Eturns> </returns> <br/> Public int executenonquery (string SQL) <br/> {<br/> return executenonquery (SQL, commandtype. text, null ); <br/>}</P> <p> // <summary> <br/> // add, delete, and modify a database <br/> // </ summary> <br/> /// <Param name = "SQL"> SQL statement to be executed </param> <br/> /// <Param name = "commandtype"> type of the query statement to be executed, such as stored procedures or SQL text commands </param> <br/> // <returns> </returns> <br/> Public int executenonquery (string SQL, comma Ndtype commandtype) <br/>{< br/> return executenonquery (SQL, commandtype, null ); <br/>}< br/> /// <summary> <br/> // add, delete, and modify a database <br/> /// </Summary> <br/> // <Param name = "SQL"> SQL statement to be executed </param> <br/> // <Param name = "commandtype"> the type of the query statement, for example, stored procedures or SQL text commands </param> <br/> // <Param name = "Parameters"> parameter arrays of transact-SQL statements or stored procedures </param> <br/> // <returns> </returns> <br/> Public int executenonquer Y (string SQL, commandtype, sqlparameter [] parameters) <br/>{< br/> int COUNT = 0; <br/> using (sqlconnection connection = new sqlconnection (connectionstring) <br/>{< br/> using (sqlcommand command = new sqlcommand (SQL, connection )) <br/>{< br/> command. commandtype = commandtype; // set the commandtype of command to the specified commandtype. <br/> // If parameters are input at the same time, add these parameters. <br/> If (parameters! = NULL) <br/>{< br/> foreach (sqlparameter parameter in parameters) <br/>{< br/> command. parameters. add (parameter); <br/>}< br/> connection. open (); // open the database connection <br/> COUNT = command. executenonquery (); <br/>}< br/> return count; // After performing the add, delete, modify, and delete operations, number of affected rows in the database <br/>}< br/> /// <summary> <br/> // return all databases created by users in the currently connected database <br/> // </Summary> <br/> // <returns> </returns> <br/> Public datatable gettables () <br/>{< br/> datatable DATA = NULL; <br/> using (sqlconnection connection = new sqlconnection (connectionstring) <br/>{< br/> connection. open (); // open the database connection <br/> DATA = connection. getschema ("tables"); <br/>}< br/> return data; <br/>}</P> <p >}< br/>}</P> <p>

 

 

The following is a simple code for adding, deleting, modifying, and querying tapes.

Using System; <br/> using System. collections. generic; <br/> using System. linq; <br/> using System. text; <br/> using Model; <br/> using System. data; <br/> using System. data. sqlClient; </p> <p> namespace DAL <br/> {<br/> // <summary> <br/> // add, delete, modify, and query classes for data <br/> // </summary> <br/> public class StudentDal <br/> {<br/> SqlDbHelper Dbhelp = new SqlDbHelper (); <br/> /// <summary> <br/> // number of returned records <br/> /// </summary> <br/> // <returns> </returns> <br/> public int Count () <br/> {<br/> string SQL = "select count (1) from Student"; </p> <p> return int. parse (Dbhelp. executeScalar (SQL ). toString ()); <br/>}</p> <p> // <summary> <br/> // create Student Information <br/> /// </summary> <br/> // <param name = "st"> Student </param> <br/> // <returns> </returns> <br/> public bool Create (Student st) <br/>{</p> <p> string SQL = "insert into Student (name, age, school) values (@ name, @ age, @ school )"; <br/> SqlParameter [] paramters = new SqlParameter [] <br/> {<br/> new SqlParameter ("@ name", st. name), <br/> new SqlParameter ("@ age", st. age), <br/> new SqlParameter ("@ school", st. school) <br/>}; <br/> return Dbhelp. executeNonQuery (SQL, CommandType. text, paramters)> 0; </p> <p >}</p> <p> // <summary> <br/> // retrieve the user entity by ID <br/> /// </summary> <br/> /// <param name = "id"> Student ID </param> <br/> /// <returns> </returns> <br/> public Student Read (int id) <br/>{< br/> string SQL = string. format ("select * from Student where id = {0}", id); <br/> DataTable table = Dbhelp. executeDataTable (SQL); <br/> if (table. rows. count> 0) <br/>{< br/> DataRow row = table. rows [0]; <br/> Student st = new Student () <br/>{< br/> Id = int. parse (row ["id"]. toString (), <br/> Name = row ["name"]. toString (), <br/> Age = int. parse (row ["age"]. toString (), <br/> School = row ["school"]. toString () <br/>}; </p> <p> return st; <br/>}< br/> else <br/>{< br/> return null; <br/>}</p> <p> // <summary> <br/> // modify Student Information <br/> /// </summary> <br/> /// <param name = "st"> </param> <br/> /// <returns> </returns> <br/> public bool uuudate (Student st) <br/> {<br/> string SQL = "update student set [name] = @ name, age = @ age, school = @ school where id = @ id "; <br/> SqlParameter [] paramters = new SqlParameter [] <br/> {<br/> new SqlParameter ("@ name", st. name), <br/> new SqlParameter ("@ age", st. age), <br/> new SqlParameter ("@ school", st. school), <br/> new SqlParameter ("@ id", st. id) <br/>}; </p> <p> return Dbhelp. executeNonQuery (SQL, CommandType. text, paramters)> 0; </p> <p >}</p> <p> // <summary> <br/> // delete information based on the student ID <br/> /// </summary> <br/> /// <param name = "Id"> </param> <br/> /// <returns> </returns> <br/> public bool Delete (int Id) <br/> {<br/> string SQL = "delete from student where id =" + Id; <br/> return Dbhelp. executeNonQuery (SQL)> 0; <br/>}</p> <p> public DataTable GetInform () <br/>{< br/> string SQL = "select * from student"; <br/> return Dbhelp. executeDataTable (SQL); <br/>}</p> <p >}< br/>