1：添加命名空間System.Data.SqlClient中的SQL Server訪問類； 2：與SQL Server數(shù)據(jù)庫建立連接，ADO.NET提供Connection對象用于建立與SQL Server數(shù)據(jù)庫的連接 string connectionStr = "Data source=服務(wù)器名;Initial Catalog=數(shù)據(jù)庫名稱; uid=用戶名;pwd=密碼()"; // 定義連接字符串 // Integrated Security=True 集成身份驗證 //uid=xxx;Pwd=xxx 用戶名密碼登陸 SqlConnection connection1 = new SqlConnection(connectionStr); ///實例化Connection對象用于連接數(shù)據(jù)源 …………… connection1.Close(); ///關(guān)閉數(shù)據(jù)庫連接 |
3：與SQL Server數(shù)據(jù)庫建立連接后，使用命令對象SqlCommand類直接對數(shù)據(jù)庫進行操作
（1）增加、刪除、修改操作
SqlConnection connection1 = new SqlConnection(connectionStr); //建立連接 connection1.Open(); //打開數(shù)據(jù)庫連接 string sqlStr = "（SQL執(zhí)行語句，例如 insert into A values('abc',1)）"; //定義相關(guān)的執(zhí)行語句，相當于寫好命令 SqlCommand command1 = new SqlCommand(sqlStr, connection1); //構(gòu)造函數(shù)指定命令對象所使用的連接對象connection1以及命令文本sqlStr ，相當于讓系統(tǒng)接受命令。 command1.ExecuteNonQuery(); //ExecuteNonQuery()方法返回值為一整數(shù)，代表操作所影響到的行數(shù),注意ExecuteNonQuery()方法一般用于執(zhí)行 // UPDATE、INSERT、DELETE等非查詢語句，可以理解為讓系統(tǒng)執(zhí)行命令 connection1.Close(); ///關(guān)閉數(shù)據(jù)庫連接 |
示例1：刪除的Course表中課程編號為003的記錄：
string connectionStr = "Data source=.;Initial Catalog=Student; Integrated Security=True"; SqlConnection connections = new SqlConnection(connectionStr); string sqlstr = "delete from Course where Cno='006' "; SqlCommand command1 = new SqlCommand(sqlstr, connectionss); conn.Open(); if (command1.ExecuteNonQuery() > 0) { MessageBox.Show("刪除課程成功！"); }; connections .Close(); |
示例2：向Course表中增加一門課程，課程信息由前臺輸入
string connectionStr = "Data source=.;Initial Catalog=Student;Integrated Security=True"; SqlConnection connection = new SqlConnection(connectionStr); int Credit = Convert.ToInt32(txtCredit.Text); /TextBox.text是string類型，需要用到強制轉(zhuǎn)換方法“Convert.ToInt32”將string類型轉(zhuǎn)化為int類型 string sqlStr = "insert into Course values('" + txtCno.Text + "','" + txtCname.Text + "'," + Credit + ")";//因為字符串的組成部分為需要從前臺讀取的變量，所以在這里需要用到字符串拼接， //拼接字符：‘ “+字符串變量+” ’，拼接數(shù)字：“+數(shù)字變量+” SqlCommand command1= new SqlCommand(sqlStr, connection); connection.Open(); if (command1.ExecuteNonQuery() > 0) { MessageBox.Show("課程添加成功！"); }; connection.Close(); |
示例3：把課程“線性代數(shù)”的學(xué)分修改為5分
string connectionStr = "Data source=.;Initial Catalog=Student; Integrated Security=True"; SqlConnection connection = new SqlConnection(connectionStr); string sqlStr = "update Course set Ccredit=5 where Cname='線性代數(shù)'"; SqlCommand command1= new SqlCommand(sqlStr, connection); connection .Open(); if (command1.ExecuteNonQuery() > 0) { MessageBox.Show("學(xué)分修改成功！"); }; connection .Close(); |
（2）查詢數(shù)據(jù)庫,用ExecuteScalar（）方法，返回單個值(Object)（查詢結(jié)果第一行第一列的值）
示例4：從Student表中查詢學(xué)號為201244111學(xué)生的姓名：
string connectionStr = "Data source=.;Initial Catalog=Student; Integrated Security=True"; SqlConnection connection = new SqlConnection(connectionStr); string sqlstr = "select Sname from student where Sno='201244111' "; SqlCommand command1 = new SqlCommand(sqlstr, connection); connection.Open(); string studentName = command1.ExecuteScalar().ToString(); MessageBox.Show(studentName); connection.Close(); |
使用DataReader讀取多行數(shù)據(jù),逐行讀取,每次讀一行
示例5：運用DataReader逐行讀出student表中的第一列數(shù)據(jù)
string connectionStr = "Data source=.;Initial Catalog=Student; Integrated Security=True"; SqlConnection connection = new SqlConnection(connectionStr); string sqlstr = "select *from student"; SqlCommand command1 = new SqlCommand(sqlstr, connection); connection.Open(); SqlDataReader dataReader1 = command1.ExecuteReader(); // DataReader類沒有構(gòu)造函數(shù)，不能實例化，需要通過調(diào)用Command對象的command1的ExecuteReader()方法 while (dataReader1.Read()) ///DataReader的Read()方法用于讀取數(shù)據(jù)，每執(zhí)行一次該語句，DataReader就向前讀取一行數(shù)據(jù)；如果遇到末尾，就返回False，否則為True { MessageBox.Show(dataReader1[0].ToString()); } connection.Close(); |
4.使用SqlDataAdapter數(shù)據(jù)適配器類訪問數(shù)據(jù)庫 ，注意：它既可以將數(shù)據(jù)庫中數(shù)據(jù)傳給數(shù)據(jù)集中的表，又可將數(shù)據(jù)集中的表傳到數(shù)據(jù)庫中。簡言之，數(shù)據(jù)適配器類用于數(shù)據(jù)源與數(shù)據(jù)集間交換數(shù)據(jù)
（鏈接語句略）
connection1.Open(); ///打開數(shù)據(jù)庫連接
string sqlStr = "SELECT * FROM A"; ///從A表中選擇所有數(shù)據(jù)的SQL語句
SqlDataAdapter dataAdapter1 = new dataAdapter(sqlStr, connection1); ///構(gòu)造名為dataAdapter1的數(shù)據(jù)適配器對象， 并指定連接對象connection1以及SELECT語句
DataSet dataSet1 = new DataSet(); ///構(gòu)造名為dataSet1的數(shù)據(jù)集對象 dataAdapter1.Fill(dataSet1);
………………………………
///使用SqlDataAdapter類中的Fill()方法將數(shù)據(jù)填充到數(shù)據(jù)集中，注意：SqlDataAdapter類中的Fill()方法和Update()方法可用于將數(shù)據(jù)填充到單個數(shù)據(jù)表或數(shù)據(jù)集中
connection1.Close();
示例6：將Student表中的數(shù)據(jù)全部查詢出來
string connectionStr = "Data source=.;Initial Catalog=Student; Integrated Security=True"; SqlConnection connection = new SqlConnection(connectionStr); string sqlstr = "select *from student"; connection.Open(); SqlDataAdapter dataAdapter1 = new SqlDataAdapter(sqlstr, connection); DataSet dataSet1 = new DataSet(); dataAdapter1.Fill(dataSet1); ///使用SqlDataAdapter類中的Fill()方法將數(shù)據(jù)填充到數(shù)據(jù)集中，相當于程序的臨時數(shù)據(jù)庫 DataTable dt1 = dataSet1.Tables[0]; ///獲取數(shù)據(jù)集的第一張表 this.dataGridView1.DataSource = dt1; connection.Close(); |