Tips for manipulating SQL Server databases in asp.net _ Practical tips

1. Pass to database statement parameters

Passing parameters to a database operation statement can be implemented by a stored procedure, and here are two other easy ways to do this:

You can pass parameters directly into a SQL statement variable in C # by using string manipulation, for example:

String s= "Davolio";

String Sql= "SELECT * FROM Employees where lastname=" + "" "+s+" "

Equivalent to writing to SQL statements:

SELECT * FROM Employees where lastname= ' Davolio '
It can also be implemented through the THISCOMMAND.PARAMETERS.ADD () method, as follows:

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, the string s passes the parameter "Ddbolio" to the parameter Charname in the database action statement.

2. Read data from different tables in the database into dataset datasets

The SqlDataAdapter Fill method fills a known dataset and creates a temporary table for each fill item that can be read from the data set by access to that table. The related actions 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 Customers";

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> ");

As you can see, in the DataSet DS, new students become two temporary tables "myemployees" and "mycustomers". To verify that the data in the two tables are indeed read into the DataSet DS, assign the first row of the table "MyEmployees" in the Tables "HireDate" to the character variable T1 with the data read operation, and the table "Mycustomers" corresponds to the property "ContactTitle" The first line is assigned to the character variable T2, and the variables are displayed in the pop-up window by the Javastript function "alert ()". The Page.registerstartupscript method is used to emit a client script block whose first parameter is a flag bit, the user can select any, and the second parameter is a JavaScript script, where the alert function pops up the MessageBox dialog box, and we pass the parameters T1 and T2 into the script So that it is displayed in the MessageBox.

PS: Because the network speed is too slow, the relevant display chart can not be uploaded to the server, really a great regret. There's also the style and format of not knowing how to write code that makes the code look messy.