Tips for Operating SQL server databases in asp.net

1. Pass database statement Parameters

You can use the stored procedure to pass parameters to database operation statements. The following two simple and convenient methods are provided:

You can directly input parameters to SQL statement variables through string operations in C #. For example:

String s = "Davolio ";

String SQL = "select * from employees where LastName =" + "'" + s + "'"

Equivalent to writing SQL statements:

Select * from employees where LastName = 'davolio'
You can also use the thisCommand. Parameters. Add () method, as shown below:

String s = "Davolio ";

SqlConnection thisConnection = new SqlConnection

("Data Source = (local); Initial Catalog = Northwind; UID = sa; PWD = ");

ThisConnection. Open ();

SqlCommand thisCommand = thisConnection. CreateCommand ();

ThisCommand. CommandText =

"Select * from employees where LastName = @ charname ";

ThisCommand. Parameters. Add ("@ charname", s );

As you can see, string s passes the parameter "Ddbolio" to the charname in the database operation statement.

2. read data from different tables in the database into the DataSet.

The Fill Method of SqlDataAdapter can Fill a known dataset and create a temporary table for each Fill item. You can access the table to read the relevant data in the dataset. The related operations are as follows:

SqlConnection thisConnection = new SqlConnection

("Data Source = (local); Initial Catalog = Northwind; UID = sa; PWD = ");

Try

{

ThisConnection. Open ();

}

Catch (Exception ex)

{

ThisConnection. Close ();

}

 

String sql1 = "select * from employees ";

String sql2 = "select * from MERs ";

SqlDataAdapter sda = new SqlDataAdapter (sql1, thisConnection );

DataSet ds = new DataSet ();

Sda. Fill (ds, "myemployees ");

Sda. Dispose ();

 

SqlDataAdapter sda1 = new SqlDataAdapter (sql2, thisConnection );

Sda1.Fill (ds, "myCustomers ");

Sda1.Dispose ();

String t1 = ds. Tables ["myemployees"]. Rows [0] ["Hiredate"]. ToString ();

String t2 = ds. Tables ["myCustomers"]. Rows [0] ["ContactTitle"]. ToString ();

 

Page. registerStartupScript ("aa", "<script language = javascript> alert ('T1 =" + t1 + ", t2 =" + t2 + "'); </script> ");

We can see that two temporary tables "myemployees" and "myCustomers" are generated in the data set ds ". To verify that the data in these two tables is indeed read into the data set ds, the first row of the "myemployees" attribute "Hiredate" in the table is assigned to the variable t1, assign the first row of the table "myCustomers" corresponding to the attribute "ContactTitle" to the primary variable t2, and display these variables in the pop-up window using the JavaStript function "alert. Page. the RegisterStartupScript method is used to issue client script blocks. The first parameter is the flag, which can be selected by the user. The second parameter is a JavaScript script. The alert function is used to pop up the MessageBox dialog box, we pass the parameters t1 and t2 into the script to display them in MessageBox.

Ps: because the network speed is too slow, it is a pity that you cannot upload related charts to the server. I still don't know the style and format of the Code, which makes the code very messy.