Method of Connecting Database with C

The following content gives you c # connection database method related introduction, this article has very reference value, the specific details are as follows.

The ASP. NET connection technique to the database is called ADO. NET, which is a 1-heap class for submitting sql statements to the database. Here is connected to the Sql Server 2008 database, other database usage is similar, is called the class name is not 1.

First configure the database connection string on Web. config (add the application configuration file App. config locally, and add the System. Configuration. dll assembly locally), adding the following node code:

<connectionStrings>
 <add name="connstr" connectionString="Data Source=.;Initial Catalog=Milk ;User Id=sa;Password=521521521;"></add>
 </connectionStrings>

name is the link string name, which can be taken at will. connectionString is the link string. Data Source database server IP address, here is the local write ".". Initial Catalog is the database name. User Id is a database user, in which sa is the highest authority administrator account, which needs to be used carefully, but a special restricted account for the database is established. Password is the password.

When the program uses the database, it first extracts the connection string of the configuration file and assigns it to a variable. The code is as follows:

public static readonly string connstr =
ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;

ConfigurationManager is a static class that provides access to client application configuration files.

Then open the database connection, and automatically release the connection with using after use:

SqlConnection is a sealed class that represents one open connection to the SQL Server database. Then, execute the SQL statement. First, define the SqlCommand class used to send instructions to the database. After definition, determine that the connection object executed by the statement is conn, and then determine the SQL statement to be executed. Usage examples are as follows:

//SqlConnection Object that establishes a connection to the database 
using(SqlConnection conn = new SqlConnection(connstr))
{
conn.Open();// Open a connection 
// Create by Connection 1 Send commands to the database ( Command ) SqlCommand
using(SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = " Insert into T_Student*(Name,Age) values( ' XXX' , 18) " ;// CommandText To be executed SQL Statement 
cmd.ExecuteNonQuery();// Execute 
}
}

ExecuteNonQuery () 1 is generally used to execute Update, Delete, Insert statements

ExecuteScalar () is used for a 1-row, 1-column result execution that returns an object type. Examples are as follows:

using(SqlConnection conn = new SqlConnection(connstr))
{
conn.Open();// Open a connection 
// Create by Connection 1 Send commands to the database ( Command ) SqlCommand
using(SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = " select Name from T_Student where Name= " XXX " ;// CommandText To be executed SQL Statement 
cmd.ExecuteScalar();// Execute 
}
}

When the return value has multiple lines of results, use ExecuteReader (), and the return type SqlDataReader needs to be released. For example:

using(SqlConnection conn = new SqlConnection(connstr))
{
conn.Open();// Open a connection 
// Create by Connection 1 Send commands to the database ( Command ) SqlCommand
using(SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = " select * from T_Student where Age<18 " ;
using(SqlDataReader reader=cmd.ExecuteReader()
{
  while(reader.Read())
{
 string name=reader.GetString(1);// Get the first 1 The value of the column 
 int age=reader.GetIn32(2); // Get the first 2 The value of the column 
 Console.WriteLine(name);
 Console.WriteLine(age.ToString());
}
}
}
}

Among them, Read method returns bool type, and the query result is put into the database, not into the client. Before the initial pointer points to the first piece of data, the Reader pointer moves down one piece every time it is called. As long as it does not move to the last piece of data, it directly returns true. GetString\ GetInt32 and other methods of reader only accept integer parameters, that is, serial numbers, and use GetOrdinal method to dynamically obtain serial numbers according to column names.

0列	第1列	第2列	第3列
Id	Name	Age	Hobby
1	XXX	18	勾搭妹子
2	王旭	30	勾搭妹子

Figure 1 Database T_Student Table

In order to avoid database injection vulnerabilities, Microsoft has set query parameters, such as the following:

cmd. CommandText = "select * from T_Student where Age < @ Age ";

cmd. Parameters. Add (new SqlParameter ("@ Age", 19));

Here, @ Age is set as a query parameter, but @ parameter cannot be used to replace keywords such as table name, field name, select, etc.

SqlDataReader is connection-related, the query results in SqlDataReader are not placed in the program, but in the database server, and SqlDataReader is just equivalent to a cursor, which refers to where to read.

ADO. NET provides a dataset mechanism, DataSet, which exists in local memory and contains several DataTable, and DataTable contains several rows of DataRow. Usage:

DataSet dataset=new DataSet (); 
SqlDataAdapter adapter=new SqlDataAdapter(cmd);
adapter.Fill(dataset);

SqlDataAdapter is a class that helps us populate SqlCommand query results into DataSet, and DataSet is equivalent to the local list collection (small database). The traversal method is as follows:

DataTable table=dataset.Tables[0];//1 Under normal circumstances, only 1 A Tables When multiple select Statement when there are multiple Tables . 

DataRowCollection rows=table.Rows;
for(int i=0;i<rows.Count;i++)
{
  DataRow row=rows[i];
int age=(int)row[ " Age " ];// Traversal age 
}

Basically all the steps are: open the link-create the command-execute-process the execution result. Therefore, you can write a public class for your own use to avoid duplicating the code. The detailed code is as follows:

public static class SqlHelper
  {
    public static readonly string connstr =
      ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
    public static SqlConnection OpenConnection()// Establish a connection 
    {
      SqlConnection conn = new SqlConnection(connstr);
      conn.Open();
      return conn;
    }
    public static int ExecuteNonQuery(string cmdText,
      params SqlParameter[] parameters)// Note that it is simplified by using variable length parameters 
    {
      using (SqlConnection conn = new SqlConnection(connstr))
      {
        conn.Open();
        return ExecuteNonQuery(conn, cmdText, parameters);
      }
    }
    public static object ExecuteScalar(string cmdText,
      params SqlParameter[] parameters)
    {
      using (SqlConnection conn = new SqlConnection(connstr))
      {
        conn.Open();
        return ExecuteScalar(conn, cmdText, parameters);
      }
    }
    public static DataTable ExecuteDataTable(string cmdText,
      params SqlParameter[] parameters)
    {
      using (SqlConnection conn = new SqlConnection(connstr))
      {
        conn.Open();
        return ExecuteDataTable(conn, cmdText, parameters);
      }
    }
    public static int ExecuteNonQuery(SqlConnection conn,string cmdText,
      params SqlParameter[] parameters)
    {
      using (SqlCommand cmd = conn.CreateCommand())
      {
        cmd.CommandText = cmdText;
        cmd.Parameters.AddRange(parameters);
        return cmd.ExecuteNonQuery();// Returns how many rows were executed 
      }
    }
    public static object ExecuteScalar(SqlConnection conn, string cmdText,
      params SqlParameter[] parameters)
    {
      using (SqlCommand cmd = conn.CreateCommand())
      {
        cmd.CommandText = cmdText;
        cmd.Parameters.AddRange(parameters);
        return cmd.ExecuteScalar();
      }
    }
    public static DataTable ExecuteDataTable(SqlConnection conn, string cmdText,
      params SqlParameter[] parameters)
    {
      using (SqlCommand cmd = conn.CreateCommand())
      {
        cmd.CommandText = cmdText;
        cmd.Parameters.AddRange(parameters);
        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
        {
          DataTable dt = new DataTable();
          adapter.Fill(dt);
          return dt;
        }
      }
    }
    public static object ToDBValue(this object value)
    {
      return value == null ? DBNull.Value : value;
    }
    public static object FromDBValue(this object dbValue)
    {
      return dbValue == DBNull.Value ? null : dbValue;
    }
  }

The principle of encapsulation method: put the invariant into the method, put the change into the parameter, the return value in SqlHelper class is 1 row and 1 column with ExecuteScaler, ExecuteNonQuery1 is used to execute Update\ Delete\ Insert statement, ExecuteDataTable is only used to execute sql with less query results, and the return value is DataTable.

In the database, NULL and "" are not the same, and NULL and 0 are not the same. NULL in the database means not "knowing". If one table has a nullable field Name, if several instances Name are NULL,

select * from T_Student where Name=NULL is not available.
select * from T_Student where Name is NULL can find all Name filled with NULL data.

Requirements: If no name is entered, the name should be NULL, and if no age is entered, the age should be NULL.

Problem: In SqlParameter, if the parameter value is null, it means that no parameter value is provided and an error will be reported.

Solution:. NET provides DBNull. Value to represent NULL in the database. It is thought that DBNull. Value is object type. So you need to use this:

object objName;
string name=tbName.Text;
if(name.Length<0)
{
  objName=DBNull.Value;
}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 also DBNull. Value. Therefore, after reading, it is necessary to judge and assign values. The usage is as follows:

if ( row[ " Name " ]==DBNull.Value ) 
{
 name=null;
}
else
{
 name=(string)row[ " Name " ];
}

But there is also a problem, if it is int type, it cannot be null, so use int when defining it? age.

We must strictly distinguish between 0, NULL and "", otherwise it is difficult to find problems.

The two functions in SqlHelper mentioned last time are the encapsulation of the above two usages, which can be directly used in SqlParameter parameters.

Used when nullable data is entered into the database

public static readonly string connstr =
ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;

0

Used when outputting

public static readonly string connstr =
ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;

1

This is mainly used in SqlParameter to realize the input and output of nullable data.

The above content is about the method of connecting C # database introduced by this article. I hope you like it.