ASP. NET database interaction Basics

Recently, vs2008 was installed in the notebook, and its SQL server2005 was used for learning. NET database operations, but the notebook has not been used for a long time, the keyboard has been broken, several keys did not respond, so yesterday ran out and spent 80 dollars to the notebook for a new keyboard, today, I got a database query item.

To operate a database in. Net, a collection of related classes called ADO. NET is usually used. Basic Structure, concepts skipped. The following is an instance program that queries database connections in the page_load () method of a page in ASP. NET, and then reflects the query results on the page.

The Code is as follows:

 

     public partial class _Default : System.Web.UI.Page
    {
protected void Page_Load(object sender, EventArgs e)
        {
string ConnectString=
　　　　　　　　　　　　@"server=.\sqlexpress;integrated security=SSPI;database=mytestdb";
            SqlConnection con = new SqlConnection(ConnectString);
try
            {
                con.Open();
                lbInfo.Text = "<b>Server Version : </b>" + con.ServerVersion;
                lbInfo.Text += "<br/><b>Connection is : </b>" + con.State.ToString();
string sql = "SELECT * FROM MyTestTable";
                SqlCommand cmd = new SqlCommand(sql, con);
                SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
                {
                    QueryResult.Text += reader.GetString(0) + reader.GetString(1)
　　　　　　　　　　　　　　 + "\n" +
                        reader.GetInt32(2).ToString() + "\n" + reader.GetString(3);
                }
                reader.Close();
            }
catch (Exception err)
            {
                lbInfo.Text = "Error reading the database : " + err.Message;
            }
finally
            {
                con.Close();
            }
        }                               
    }

Of course, the first thing to ensure is that a database named mytestdb exists first, and a table named mytesttable must be created in the database.

 

Pay attention to the connection string, where server is the server name, sqlserver enterprise is "(local)", sqlserverexpress is "./sqlexpress ",
Integrated Security = sspi uses window authentication,

Database indicates the database instance name;

I first started to use the data source = localhost introduced in the book; the connection failed, and then I checked it online and followed the above connection string to run properly.

When using sqldatareader to read the content, take a column in a row. This column number starts from 0 and inherits the array tradition.

Note that the sqldatareader object must be closed () after use, and the sqlconnection object is also.

 

Datareader can only forward the query results of DB tables in a one-way. It is simple and effective, similar to a cursor. Datareader has a fieldcount attribute value that stores the number of columns in the result set of this query. There is a getname () method, which can be used as a parameter to get the name of this column. Of course, note that the column number starts from 0.

Note that the following methods of using datareader to retrieve column values: getint32 (), getchar (), getdatetime (), and getstring () cannot directly process null values, when the obtained value is null, even if you use a column data type.. NET data type. Therefore, if a column in the table may contain null values, you must first obtain the value through the getvalue () method and then compare it with the constant dbnull. value is equal and then converted to the correct data type for use. Example:

 

Int? Age;

If (Reader ["Age"] = dbnull. value)

Age = NULL;

Else

Age = (Int ?) Reader ["Age"];

 

Fortunately,. Net has designed an empty type. Otherwise, this situation is really inconvenient to handle.

 

Today, I also reviewed the concept of transactions in the database and learned how to execute transactions in ADO. net. There is such a piece of advice in the book:

Use transactions in the stored procedure as much as possible, and use less ADO. net transactions. In this way, transactions can be started and compiled faster, because the database server does not need to interact with the client.

 

In fact, it is not a good coding design method to directly connect to the database in the PAGE method. The good method should be to encapsulate table data and encapsulate access methods, and then call it when you use it.

The usage of stored procedures in SQL server has a small note: Do not use sp_xxxx for naming, because SQL server will first look for the system stored procedures, resulting in low efficiency. In addition, some articles have said that the Stored Procedure names in SQL Server must all be prefixed with proc (???)......

 

The previous DB operations are based on the client's connection to the database. Ado. Net also provides the function of using dataset for non-connected dB operations.