Database Access helper class SqlHelper, database sqlhelper

Database Access helper class SqlHelper, database sqlhelper

The operations required for a program to access the database include creating a connection to a specified database,

Then open the created connection and create execution commands (that is, SQL Execution Code ),

Finally, execute the command to close the created connection and release the resource.

Ado.net is an object-oriented class library used to interact with data sources.

We can operate databases very well.

To facilitate database access, we can write a library access helper class, which will

We often encapsulate operations such as adding, deleting, modifying, and querying databases.

SqlHelper helper class:

1 // database access helper class 2 public static class SqlHelper 3 {4 // database connection string 5 private static string ConnStr = ConfigurationManager. connectionStrings ["ConnStr"]. connectionString; 6 7 // add, delete, and modify 8 public static int ExecuteNonQuery (string SQL, params SqlParameter [] parameters) 9 {10 using (SqlConnection conn = new SqlConnection (ConnStr )) 11 {12 conn. open (); 13 using (SqlCommand cmd = conn. createCommand () 14 {15 cmd. commandText = SQL; 16 cmd. parameters. addRange (parameters); 17 return cmd. executeNonQuery (); 18} 19} 20} 21 22 // query 23 public static DataTable ExecuteDataTable (string SQL, params SqlParameter [] parameters) 24 {25 using (SqlConnection conn = new SqlConnection (ConnStr) 26 {27 conn. open (); 28 using (SqlCommand cmd = conn. createCommand () 29 {30 cmd. commandText = SQL; 31 cmd. parameters. addRange (parameters); 32 33 DataSet dataSet = new DataSet (); // DataSet 34 SqlDataAdapter adapter = new SqlDataAdapter (cmd); 35 adapter. fill (dataSet); 36 return dataSet. tables [0]; 37} 38 39} 40} 41 42 // return the content of the first column of the First line 43 public static object ExecuteScalar (string SQL, params SqlParameter [] parameters) 44 {45 using (SqlConnection conn = new SqlConnection (ConnStr) 46 {47 conn. open (); 48 using (SqlCommand cmd = conn. createCommand () 49 {50 cmd. commandText = SQL; 51 cmd. parameters. addRange (parameters); 52 return cmd. executeScalar (); 53} 54} 55}

 

Appendix: database connection configuration file

<? Xml version = "1.0" encoding = "UTF-8"?>

<Configuration>

<ConnectionStrings>

<Add name = "dbConnStr" connectionString = "Data Source =.; Initial Catalog = mydb; User ID = sa; Password = 123456"/>

</ConnectionStrings>

</Configuration>

// The file name seems to require App. config, and then use the connection string in the program

1: Reference

Solution -- reference -- Right-click to add --. NET -- System. Configuration

Then you can use the ConfigurationManager class in System. Configuration.

2: Use

String conStr = ConfigurationManager. ConnectionStrings ["dbConnStr"]. ConnectionString;

// ConfigurationManager needs to parse and obtain the namespace. Because there may be multiple connection strings, you must use ConnectionStrings. Because it has multiple attributes, all of them must use ConnectionString.

The value of conStr is"Data Source =.; Initial Catalog = mydb; User ID = sa; Password = 123456"