The following content gives you a related introduction to the method of connecting to the C# database. This article is of great reference value, and the specific details are as follows.
The technology of connecting to a database is called, it is a bunch of classes used to submit SQL statements to the database. The Sql Server 2008 database is connected here. The usage of other databases is similar, but the class names called are different.
First, configure the database connection string on (adding application configuration files locally, and adding assembly locally), and add the following node code:
<connectionStrings> <add name="connstr" connectionString="Data Source=.;Initial Catalog=Milk ;User Id=sa;Password=521521521;"></add> </connectionStrings>
name is the name of the link string and can be taken as you like. connectionString is a link string. The IP address of the server where the Data Source database is located, here is the local write "." Initial Catalog is the database name. User Id is a database user, where sa is the highest privilege administrator account and needs to be used with caution, but rather a dedicated restricted account for establishing a database for the database. Password is the password.
When the program uses the database, first extract the connection string of the configuration file and assign it to a variable. The code is as follows:
public static readonly string connstr = ["connstr"].ConnectionString;
ConfigurationManager is a static class that provides access to client application configuration files.
Then open the database connection and use using automatically release the connection after use:
SqlConnection is a sealed class that represents an open connection to the SQL Server database. Next, execute the SQL statement. First define the SqlCommand class used to send instructions to the database. After defining, determine the connection object executed by the statement is conn, and then determine the SQL statement to be executed. The usage is as follows:
//SqlConnection is an object to establish a database connectionusing(SqlConnection conn = new SqlConnection(connstr))
{
();//Open the connection//Create an object that issues commands to the database through connection SqlCommandusing(SqlCommand cmd = ())
{
=”Insert into T_Student*(Name,Age) values(‘XXX'，18)”;// SQL statement to be executed by CommandText();//implement}
}
ExecuteNonQuery() is generally used to execute Update, Delete, and Insert statements.
ExecuteScalar() for a result that returns a value of one row and one column, which returns the object type. As an example:
using(SqlConnection conn = new SqlConnection(connstr))
{
();//Open the connection//Create an object that issues commands to the database through connection SqlCommandusing(SqlCommand cmd = ())
{
=”select Name from T_Student where Name=”XXX””;// SQL statement to be executed by CommandText();//implement}
}
When the return value has multiple rows, you need to use ExecuteReader(). The return type SqlDataReader needs to be released. Example usage:
using(SqlConnection conn = new SqlConnection(connstr))
{
();//Open the connection//Create an object that issues commands to the database through connection SqlCommandusing(SqlCommand cmd = ())
{
=”select * from T_Student where Age<18”;
using(SqlDataReader reader=()
{
while(())
{
string name=(1);//Get the value of the first column int age=reader.GetIn32(2); //Get the value of column 2 (name);
(());
}
}
}
}
The Read method returns the bool type, and the query result is placed in the database, but not in the client. Before the initial pointer points to the first piece of data, the Reader pointer is moved down one after each call, and as long as it has not moved to the last piece, it will directly return true. The GetString\GetInt32 and other methods of the reader only accept integer parameters, that is, sequence numbers, and use the GetOrdinal method to dynamically obtain the sequence number based on the column name.
|
0 columns |
The first column |
Second column |
The third column |
|
Id |
Name |
Age |
Hobby |
|
1 |
XXX |
18 |
Hook up girls |
|
2 |
Wang Xu |
30 |
Hook up girls |
Figure 1 Database T_Student table
In order to avoid database injection vulnerabilities, Microsoft has set up query parameters, such as the following:
=”select * from T_Student where Age<@Age”;
(new SqlParameter(“@Age”,19));
Here, @Age is set as a query parameter, but @ parameter cannot replace keywords such as table name, field name, select, etc.
SqlDataReader is related to connection. The query results in SqlDataReader are not placed in the program, but in the database server. SqlDataReader is just equivalent to a cursor, reading wherever it points.
Provides a data set mechanism, DataSet, which exists local memory, which contains several DataTables, and DataTable contains several rows of DataRow. How to use:
DataSet dataset=new DataSet（）； SqlDataAdapter adapter=new SqlDataAdapter(cmd); (dataset);
SqlDataAdapter is a class that helps us populate the SqlCommand query results into DataSet. DataSet is equivalent to a local list collection (small database). The traversal method is as follows:
DataTable table=[0];//Usually, there is only one Tables, and when multiple select statements are executed at the same time, there are multiple Tables.
DataRowCollection rows=;
for(int i=0;i<;i++)
{
DataRow row=rows[i];
int age=(int)row[“Age”];//Transfer age}
Basically all the steps are: Open the link - create the command - execute - process the execution result. Therefore, you can write a public class to use it yourself to avoid duplicate code. The detailed code is as follows:
public static class SqlHelper
{
public static readonly string connstr =
["connstr"].ConnectionString;
public static SqlConnection OpenConnection()//Create a connection {
SqlConnection conn = new SqlConnection(connstr);
();
return conn;
}
public static int ExecuteNonQuery(string cmdText,
params SqlParameter[] parameters)//Note that the use of variable length parameters has been simplified {
using (SqlConnection conn = new SqlConnection(connstr))
{
();
return ExecuteNonQuery(conn, cmdText, parameters);
}
}
public static object ExecuteScalar(string cmdText,
params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
();
return ExecuteScalar(conn, cmdText, parameters);
}
}
public static DataTable ExecuteDataTable(string cmdText,
params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
();
return ExecuteDataTable(conn, cmdText, parameters);
}
}
public static int ExecuteNonQuery(SqlConnection conn,string cmdText,
params SqlParameter[] parameters)
{
using (SqlCommand cmd = ())
{
= cmdText;
(parameters);
return ();//Return how many rows have been executed }
}
public static object ExecuteScalar(SqlConnection conn, string cmdText,
params SqlParameter[] parameters)
{
using (SqlCommand cmd = ())
{
= cmdText;
(parameters);
return ();
}
}
public static DataTable ExecuteDataTable(SqlConnection conn, string cmdText,
params SqlParameter[] parameters)
{
using (SqlCommand cmd = ())
{
= cmdText;
(parameters);
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
(dt);
return dt;
}
}
}
public static object ToDBValue(this object value)
{
return value == null ? : value;
}
public static object FromDBValue(this object dbValue)
{
return dbValue == ? null : dbValue;
}
}
Principle of encapsulation method: put the unchanged into the method and the changing into the parameters. The return value in the SqlHelper class is to use ExecuteScaler in rows and columns. ExecuteNonQuery is generally used to execute Update\Delete\Insert statements. ExecuteDataTable is only used to execute SQL with fewer query results, and the return value is DataTable.
NULL is different from "" in the database, and NULL is different from 0. NULL in the database means that it does not "know". If there is a nullable field Name in a table, if there are several instances Name is NULL,
select * from T_Student where Name=NULL No data can be found.
select * from T_Student where Name is NULL You can find all the data filled in NULL in Name.
Requirements: If no name is entered, the name should be NULL. If no age is entered, the age should be NULL.
Problem: If the parameter value in SqlParameter is null, it means that no parameter value is provided, and an error will be reported.
Workaround: .NET provides NULLs used to represent databases. I thought it was object type. So you need to use it like this:
object objName;
string name=;
if(<0)
{
objName=;
}else
{
objName=name;
}
Then the SqlParameter parameter is changed to objName.
When reading the database at the same time, Null exists, and the value returned to .NET is the same. Therefore, after reading, you must judge and assign the value. The usage is as follows:
if（row[“Name”]==）
{
name=null;
}
else
{
name=(string)row[“Name”];
}
But there is also a problem. If it is of type int, it cannot be null. When it is time to define it, use int? age.
It is necessary to strictly distinguish between 0, NULL and "", otherwise it will be difficult to find any problems.
The two functions in SqlHelper mentioned last time are encapsulations of the above two usages and can be directly used in the SqlParameter parameters.
Used when entering the database with empty data
public static object ToDBValue(this object value)
{
return value == null ? : value;
}
Used when outputting
public static object FromDBValue(this object dbValue)
{
return dbValue == ? null : dbValue;
}
This is mainly used in SqlParameter to realize the input and output of nullable data.
The above content is about the C# method to connect to the database introduced to you in this article. I hope you like it.