1、常用的T-Sql語句
查詢：SELECT * FROM tb_test WHERE ID='1' AND name='xia'
SELECT * FROM tb_test
插入：INSERT INTO tb_test VALUES('xia','123')
INSERT INTO tb_test(name) VALUES('xia')
更新：UPDATE tb_test SET password='234' WHERE ID='1'
刪除：DELETE FROM tb_test WHERE ID='1'
DELETE tb_test WHERE ID='1'
2、在vs2010中獲取數(shù)據(jù)庫連接字符串
string connectionString = Properties.Settings.Default.DatabaseTestConnectionString;
3、SqlCommand類型
查詢：
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
SqlCommand command = new SqlCommand(selectStr, connection);
command.Connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
label1.Text = "name：" + reader["name"].ToString(); //數(shù)據(jù)讀取
command.Connection.Close();
}
catch (SqlException ex)
{
throw ex;
}
}
插入、修改、刪除：
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
SqlCommand command = new SqlCommand(cmdStr, connection);
command.Connection.Open();
command.ExecuteNonQuery();
command.Connection.Close();
}
catch (SqlException ex)
{
throw ex;
}
}
4、DataTable類型，查詢、添加、修改、刪除
DataTable使用查詢、添加、刪除、修改時(shí)，需要用到SqlDataAdapter類
string selectStr = "SELECT * FROM tb_test2";
查詢：
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
SqlDataAdapter adapter = new SqlDataAdapter(selectStr, connection);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
//數(shù)據(jù)讀取
label1.Text = dataTable.Rows[0][0].ToString();
}
catch (SqlException ex)
{
throw ex;
}
}
添加：
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
SqlDataAdapter adapter = new SqlDataAdapter(selectStr, connection);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
//添加數(shù)據(jù)
DataRow newRow = dataTable.NewRow();
newRow["id"] = "tesr";
newRow["name"] = "111";
dataTable.Rows.Add(newRow);
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
adapter.Update(dataTable); //更新到數(shù)據(jù)庫
}
catch (SqlException ex)
{
throw ex;
}
}
修改：
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
SqlDataAdapter adapter = new SqlDataAdapter(selectStr, connection);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
//修改數(shù)據(jù)
DataRow updateRow = dataTable.Rows[0];
updateRow["id"] = "update";
updateRow["name"] = "222";
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
adapter.Update(dataTable); //更新到數(shù)據(jù)庫
}
catch (SqlException ex)
{
throw ex;
}
}
刪除：
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
SqlDataAdapter adapter = new SqlDataAdapter(selectStr, connection);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
dataTable.Rows[0].Delete(); //刪除記錄
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
adapter.Update(dataTable); //更新到數(shù)據(jù)庫
}
catch (SqlException ex)
{
throw ex;
}
}
5、DataSet類型
DataSet操作跟DataTabel操作基本是一樣的，只是DataSet可以儲存有多個(gè)表格，所以就多做介紹了
6、個(gè)人總結(jié)
個(gè)人感覺，用 SqlCommand比較靈活，而DataSet是實(shí)現(xiàn)ADO.NET斷開式連接的核心，比較安全
