C # .NET database operations (transfer)

// error message display, client display specified error page

// Log in the role, except for Windows, "Forms", "Passport", "NONE"

->

< / Database> // or above is two A method of adding an element in XML. By both methods can access the database information inside. However, the access method is different.

connection code: using System.Data.SqlClient; using System.Collections.Specialized; // operating namespacestring strConn xml required = (String) ((NameValueCollection) Context.GetConfig ( "system.web / database") ["strConn"]; // The first method of accessing the database information in XML string strConn = system.configuration.configurationSettings.appsettings ["nwind"]. TOSTRING (); // Corresponding second method operation code: 1 The database is operated through the stored procedure in the database, and the engineering is ideal through the intermediate layer. Can improve safety and overall performance.

using (SqlConnection conn = new SqlConnection (strConn)) {SqlCommand cmd = new SqlCommand ( "sp_SP", conn); cmd.CommandType = CommandType.StoredProcedure; // declaration is to call a stored procedure cmd.Parameters.Add ( "@ SNO" Sqldbtype.varchar, 9); cmd.parameters.add ("@ td", sqldbtype.smalldatetime); cmd.parameters.add ("@ s_note", sqldbtype.varchar, 100); // Add variable parameter cmd.Parameters ["@Sno"]. Value = sno.text; cmd.parameters ["@ TD"]. Value = checkbase.text; cmd.parameters ["@ S_Note"]. Value = snote.text; // give parameter payment Value conn.open (); if (cmd.executenon query ()! = 0) // Execute no return set okmsg.text = "Login completion"; else okmsg.text = "Login failed";} 2, directly output SQL statement String strsql = "update nwind.nuser.data set sno = '" sno.text; // SQL Server needs to indicate the database name of the table and the user name IF (CheckBase.Text! = "") strsql = "', Month ='" checkbase.text "/ 01"; strsql = "'where sno ='" sno.text "'"; conn.open (); sqlcommand cmd = new SQLCommand (strsql, conn); IF (cmd.executenonQuery ()! = 0) okmsg.text = "Login"; else okmsg.text = "Fail";} 3. Retriever SQLDataAdapter and DataSet Joint use, generally used to obtain special operations for obtaining data sets, private dataset getdata () ((NameValueCollection) ("System.Web / Database") ["StrConn") ["strconn" ]; using (SqlConnection conn = new SqlConnection (strConn)) {SqlCommand cmd = new SqlCommand ( "sp_CO", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add ( "@ place", SqlDbType.VarChar, 2);

CMD.Parameters ["@ Place"]. value = place.text; conn.open (); sqldataadapter Da = new sqldataadapter (); da.selectcommand = cmd; dataset ds = new dataset (); da.fill (DS, "VCO"); // You can store multiple data sheets like a local database, implement the features you want to achieve. Specifically, the processing of the table has a column deletion, and the processing between multiple tables. COUNT.TEXT = "A total of:" DS.TABLES ["VCO"]. Rount.count.tostring () "pieces"; return ds;}

} 4. When only use to read data, it is recommended to use SqlDataReader, read the speed, a lot of private void DataLoader () (NameValueCollection) context.getconfig ("System.Web / Database" )) [ "strConn"]; using (SqlConnection conn = new SqlConnection (strConn)) {SqlCommand cmd = new SqlCommand ( "sp_list", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add ( "@ no ", Sqldbtype.varchar, 6); cmd.parameters [" @ no "]. Value = no.text; conn.open (); using (sqlDataReader DR = cmd.executeReader ()) {if (Dr.Read () ) {C.Text = DR ["c"]. TOSTRING (); // Get data M.Text = DR ["MTC"]. ToString (); //} else okmsg.text = "No data "}

}

} Attention, using () is used, because I have a very poor memory, so I like this method very much. I wrote wrong last time, thank you Flasheelf pointed out. First come here. Although use StoredProcedure to improve efficiency and improve security. However, relatively flexibility is not enough to discuss the detailed usage of the direct SQL statement, for example, there is a advanced search function, there are multiple conditions that can be entered. It may also only use one condition. If this is made with procedure, many judgment (n group) will be string strsql = ""; string strsql = ""; strsql = "select *"; strsql = "from [db]. [User]. [Table] WHERE con1 = '1' and con2 = '" DDL1.SelectedValue "' "; if (condition3.text! =") Strsql = "and con3 = '" condition3.text "'"; if (cName.Text ! = "") strsql = "and c_name like '%" cname.text "%'"; if (bName.Text! = ") strsql =" and b_name like '% " BName.Text "%' " ; protected DataSet DBSelect (string strSQL) {string strConn = System.Configuration.ConfigurationSettings.AppSettings [ "NWind"] ToString ();. using (SqlConnection conn = new SqlConnection (strConn)) {SqlDataAdapter da = new SqlDataAdapter (strSQL, conn ); DataSet DS = new dataset (); da.fill (ds); count.text = "Total:" DS.TABLES [0] .Rows.count.tostring () "Part"; Return DS;}} protected bool DBUpdate (string strSQL) // strSQL to Update, Insert, Delete phrase {string strConn = System.Configuration.ConfigurationSettings.AppSettings [ "NWind"] ToString ();. using (SqlConnection conn = new SqlConnection (strConn)) {Conn.open (); Sqlcommand cmd = new sqlcommand (strsql, conn); try {cmd.executenonQuery (); return true;} catch {return false;}}}

Flashelf published

2005-02-02 4:48 PM

Using is Dispose is not a Close

If you have a Close, you can use it in Open.

After Dispose, it will not seem

You can automate all classes that implements IDisposable excuses.

Dispose

Connection object

Implemented idisposable.dispose

So good

And in dispose If the connection is open, you will first close and then

Release other related things.

Ohiolee published

2005-02-03 3:48 PM

Thanks to the correction. You don't say, I thought it was a close.

thanks, thanks. . . . . . . . . . . . . . . . Grateful

Correct

Scorpion007 published in

2005-03-08 3:57 PM