1. There are two ways to connect SQL Server to Visual Studio:
(1) local computer connection;
Copy codeThe Code is as follows:
String s = "Data Source = computer name; initial Catalog = database name; integrated Security = True ";
(2) windows Authentication connection;
Copy codeThe Code is as follows:
String cc = "Data Source = computer name; Initial Catalog = database name; User ID = sa; Password = your Password ";
2. Use in Visual Studio:
Example 1: Query and display the data in the database
Copy codeThe Code is as follows:
String s = "Data Source = computer name; Initial Catalog = database name; Integrated Security = True"; // use the local computer connection method here
SqlConnection conn = new SqlConnection (s); // create a connection
Conn. Open (); // Open the connection
SqlCommand cmd = conn. CreateCommand ();
Cmd. CommandText = "select * from T_User"; // use the command
SqlDataAdapter adapter = new SqlDataAdapter (cmd );
DataTable dt = new DataTable ();
Adapter. Fill (dt );
Conn. Dispose (); // release the resource
Cmd. Dispose ();
Conn. Close (); // Close the connection
String realname = "";
String username = "";
String mobile = "";
String address = "";
For (int I = 0; I <dt. Rows. Count; I ++)
{
Realname = dt. Rows [I] [3]. ToString ();
Username = dt. Rows [I] [1]. ToString ();
Mobile = dt. Rows [I] [4]. ToString ();
Address = dt. Rows [I] [5]. ToString ();
Console. WriteLine ("Name: {0}, username: {1}, mobile phone: {2}, address: {3}", realname, username, mobile, address );
}
Console. ReadKey ();
Example 2: delete table data
Copy codeThe Code is as follows:
String cc = "Data Source = computer name; Initial Catalog = database name; User ID = sa; Password = your Password"; // use windows Authentication
SqlConnection conn = new SqlConnection (s );
Conn. Open ();
SqlCommand cmd = conn. CreateCommand ();
Cmd. CommandText = "delete from T_User where Id = 5 ";
Cmd. ExecuteNonQuery ();
Cmd. Dispose ();
Conn. Close ();
Console. WriteLine ("deleted successfully ");
Console. ReadKey ();
Example 3: Modify Table Data
Copy codeThe Code is as follows:
String s = "Data Source = computer name; initial Catalog = database name; integrated Security = True ";
SqlConnection conn = new SqlConnection (s );
Conn. Open ();
SqlCommand cmd = conn. CreateCommand ();
Cmd. CommandText = "update T_User set Card = @ card where ID = 3 ";
Cmd. Parameters. AddWithValue ("@ card", "13000000000000 ");
Cmd. ExecuteNonQuery ();
Cmd. Dispose ();
Conn. Close ();
Conn. Dispose ();
Console. WriteLine ("modification successful! ");
Console. ReadKey ();
Example 4: insert data into a table
Copy codeThe Code is as follows:
String s = "data source = computer name; initial catalog = database name; integrated security = true ";
SqlConnection conn = new SqlConnection (s );
Conn. Open ();
SqlCommand cmd = conn. CreateCommand ();
Cmd. CommandText = "insert into T_User (UserName, Password, RealName, Mobile, Address) values (@ username, @ password, @ realname, @ mobile, @ address )";
Cmd. Parameters. AddWithValue ("@ username", "xingxing ");
Cmd. Parameters. AddWithValue ("@ password", "77777 ");
Cmd. Parameters. AddWithValue ("@ realname", "");
Cmd. Parameters. AddWithValue ("@ mobile", 1300000000 );
Cmd. Parameters. AddWithValue ("@ address", "Beijing, Hebei Province ");
Cmd. ExecuteNonQuery ();
Cmd. Dispose ();
Conn. Close ();
Conn. Dispose ();
Console. WriteLine ("successfully inserted a row ");
Console. ReadKey ();