ADO. NET connection method

Use Command, DataReader, and DataSet to bind data.

Method 1: use Command and DataReader

 SqlConnection con = new SqlConnection("server=.;database=Department;uid=sa;pwd=123456");             con.Open();             string sqlStr = "select * from emp";              SqlCommand  sqlCmd = new SqlCommand(sqlStr, con);             SqlDataReader  reader = sqlCmd.ExecuteReader();             GridView1.DataSource = reader;             GridView1.DataBind(); SqlConnection con = new SqlConnection("server=.;database=Department;uid=sa;pwd=123456");            con.Open();            string sqlStr = "select * from emp";            SqlCommand  sqlCmd = new SqlCommand(sqlStr, con);            SqlDataReader  reader = sqlCmd.ExecuteReader();            GridView1.DataSource = reader;            GridView1.DataBind();

Method 2: Use the DataSet (DataAdapter does not need to Open (con. Open () method), you can implement it by yourself)

 
 

SqlDataAdapter sda = new SqlDataAdapter(); SqlConnection con = DB.createCon(); SqlCommand cmd = new SqlCommand(); string sqlStr = "select * from emp"; sda.SelectCommand = new SqlCommand(sqlStr, con); DataSet ds = new DataSet(); sda.Fill(ds, "employee"); GridView1.DataSource = ds.Tables["employee"].DefaultView; GridView1.DataBind();             SqlDataAdapter sda = new SqlDataAdapter();            SqlConnection con = DB.createCon();            SqlCommand cmd = new SqlCommand();            string sqlStr = "select * from emp";            sda.SelectCommand = new SqlCommand(sqlStr, con);            DataSet ds = new DataSet();            sda.Fill(ds, "employee");            GridView1.DataSource = ds.Tables["employee"].DefaultView;            GridView1.DataBind(); 

The Fill method implicitly executes the SQL query in SelectCommand of DataAdapter. The query result is used to define the structure of the DataSet table and Fill the table with data.

 

Difference between SqlCommand and SqlDataAdapter

 

SqlCommand corresponds to DataReader SqlDataAdapter corresponds to DataSet DataAdapter represents a set of SQL commands connected to a database, they are used to fill the DataSet and update the data source

. There are two ways to read data from the database in. NET. One is the connection-based method, just like the method of your program, and the other is the non-connection-based method, that is, you use DataSet to save the result set.

When reading data in connection mode, the result is placed in the DataReader stream (memory stream). DataReader is only forward and read-only. In this code, the data of DataReader is read and placed in a generic set, when a generic set is returned to the method caller, the method caller can perform various operations on the generic set.

When reading data in non-connection mode, you need to assign the query statement to the SelectCommand method of DataAdapter, and Fill the data in the database to DataSet through the Fill method of DataAdapter, then DataSet can be bound to the Data Display Control for user interaction.

 

 

Method In SQLHelper: (method 2 is actually the same as this method)

 

SqlDataAdapter is a data adapter, while SqlCommand is a command object, SqlDataAdapterda = new SqlDataAdapter (cmd); is to execute your SQL.

 

CommandType. Text indicates that the SQL statement is executed.

CommandType. StoreProcedure indicates that the stored procedure is executed.

CommandType indicates the type to be executed

 

 

 
 

Public Function ExecSelect (ByVal Same text As String, ByVal same type As CommandType, ByVal sqlParams As SqlParameter ()) as DataTable Dim sqlAdapter As SqlDataAdapter Dim dt As New DataTable Dim ds As New DataSet 'or assign cmd to cmd. commandText = plain text cmd. commandType = commantype cmd. connection = conn cmd. parameters. addRange (sqlParams) 'parameter add sqlAdapter = New SqlDataAdapter (cmd)' instantiate the adapter Try sqlAdapter. fill (ds) 'uses the adapter to Fill the dataSet with dt = ds. tables (0) 'able able is the first table cmd of dataSet. parameters. clear () 'clear the Catch ex As Exception MsgBox ("query failed", CType (vbOKOnly + MsgBoxStyle. exclamation, MsgBoxStyle), "warning") Finally 'at last be sure to destroy cmd Call CloseCmd (cmd) End Try Return dt End Function Public Function ExecSelect (ByVal plain text As String, byVal parameter type As CommandType, ByVal sqlParams As SqlParameter () As DataTable Dim sqlAdapter As SqlDataAdapter Dim dt As New DataTable Dim ds As New DataSet 'or assign cmd to cmd. commandText = plain text cmd. commandType = commantype cmd. connection = conn cmd. parameters. addRange (sqlParams) 'parameter add sqlAdapter = New SqlDataAdapter (cmd)' instantiate the adapter Try sqlAdapter. fill (ds) 'uses the adapter to Fill the dataSet with dt = ds. tables (0) 'able able is the first table cmd of dataSet. parameters. clear () 'clear the Catch ex As Exception MsgBox ("query failed", CType (vbOKOnly + MsgBoxStyle. exclamation, MsgBoxStyle), "warning") Finally 'at last be sure to destroy cmd Call CloseCmd (cmd) End Try Return dt End Function