One, the local database connected with VS2013
using System.Data.SqlClient;
First define a connection string
Public Static string " server= (localdb) \\projects;initial catalog=test;integrated security=true ";
Where: server is the name
ilitial Catalog is the database name
Integrated Security is an integrated validation that uses Windows authentication to connect to the database server. There is no need to write a user name and password in the connection string, which in some way improves security. (When this parameter is False or when the item is omitted, the connection is followed by UserID and password, which is equivalent to true when set to SSPI.) )
using(SqlConnection conn =NewSqlConnection (connectstring)) { stringsql ="Select Password,level from [Test]. [dbo]. [User] where userid= '"+ Username +"'"; using(SqlCommand cmd =NewSqlCommand (SQL, conn)) {Conn. Open (); using(SqlDataReader SDR =cmd. ExecuteReader ()) {if(SDR). Read ()) {//The first field under the user's name is assigned to the string pwd, even if the password (the first field of the Select), and all passwords are read backwards//Trim () method to remove whitespace before and after a string stringPWD = SDR. GetString (0). Trim (); //reader SDR gets 2 columns of data 1th column The password index is 0 The 2nd column is indexed to 1 is the user type stringUtype = SDR. GetString (1). Trim (); if(pwd = =password) { //get the user ID after successful loginUid =username; Usertype=Utype; if(usertype=="Normal User") { This. Hide (); User Muser=NewUser (); Muser. ShowDialog (); } Else if(usertype=="Advanced users") { This. Hide (); Vipuser Mvuser=NewVipuser (); Mvuser. ShowDialog (); } Else if(usertype=="Administrator") { This. Hide (); Admimistor Madministor=NewAdmimistor (); Madministor. ShowDialog (); } }
SqlConnection: Database Connection Class
SqlCommand: Database Operation class
SqlDataReader: Read
second, the data in the database to increase, delete, check, change (in DataGridView)
SqlDataAdapter ADP =NULL; DataSet DS=NULL; Private voidAdmimistor_load (Objectsender, EventArgs e) {ADP=NewSqlDataAdapter ("select * FROM [Test]. [dbo]. [User]", login.connectstring);//instantiating a bridgeDS =NewDataSet ();//instantiating datasetsADP. Fill (DS); Datagridview1.datasource= ds. tables[0]; } //edit or add data to a table Private voidButton1_Click (Objectsender, EventArgs e) {SqlCommandBuilder SCB=NewSqlCommandBuilder (ADP); //Update Data Try { //Here's the key .ADP. Update (DS); } Catch(SqlException ex) {MessageBox.Show (ex). Message); } } //Delete a selected row in a table Private voidButton3_Click (Objectsender, EventArgs e) { foreach(DataGridViewRow Drinchdatagridview1.selectedrows) {MessageBox.Show ("are you sure you want to delete? "); DataGridView1.Rows.Remove (DR);//Delete a record } }
SqlDataAdapter is a bridge between the dataset and SQL Server that is used to retrieve and save data. SqlDataAdapter maps Fill by using the appropriate Transact-SQL statements on the data source, which changes the data in the dataset to match the data in the data source, and Update (which changes the data in the data source to match DataSet) to provide this bridge. When SqlDataAdapter fills a dataset, it creates the necessary tables and columns for the returned data if they do not already exist.
A DataSet is an in-memory database, and a dataset is a separate collection of data that is not dependent on the database. The so-called independence, that is, even if the data link is disconnected, or shut down the database, the dataset is still available, the dataset is internally XML to describe the data, because XML is a platform-independent, language-independent data Description language, and can describe the complex relationship of data, For example, the data for a parent-child relationship, so the dataset can actually accommodate data with complex relationships and is no longer dependent on the database link.
Partial references from:
Summary of usage of SqlDataAdapter in 1.c#-csdn Blog
http://blog.csdn.net/nicholaseenzhu/article/details/70417407
2.360 Encyclopedia
connections to databases in C#winform