[ADO.NET] 如何 使用SQLCommand
如何利用 Visual Studio 查詢資料庫
1.對資料庫按右鍵→新增查詢
2.選擇欲查詢的資料表
3.勾選資料行
4.可以看到準則視窗，可以由這裡來決定要對這些資料做什麼樣的動作。
5.對資料表按右鍵→變更類型
便可變更我們要查詢的動作，以下用選取來做範例
6.執行SQL，即可得知查詢語法，及查詢結果
以下為語法，可以拿這個結果拿去貼在程式碼
以下為結果
PS.查詢視窗分為四部份，如下圖
以下範例需引用 System.Data.SqlClient命名空間
如何插入資料行
1.引用SqlConnection物件連接資料庫
using (SqlConnection cn = new SqlConnection(cs))
2.開啟資料庫
cn.Open();
3.引用SqlCommand物件
using (SqlCommand cmd = new SqlCommand(queryString, cn))
4.執行SQL語法
cmd.ExecuteNonQuery();
C#
private void button1_Click(object sender, EventArgs e)
{
string region = textBox1.Text.ToString();
string name = textBox2.Text.ToString();
string cs = "";
string queryString = "";
if (((Button)(sender)).Text == "Insert Northwind")
{
cs = "server=localhost\\sqlexpress;database=Northwind;integrated security=SSPI;";
queryString = "insert into mytable (myregion,myname) values(N' " + region + "',N'" + name + "' )";
//queryString = "INSERT INTO MyTable (MyRegion, MyName) VALUES(N'火', N'風')";
}
else if (((Button)(sender)).Text == "Insert ADONETDB")
{
cs = "server=localhost\\sqlexpress;database=ADONETDB;integrated security=SSPI;";
queryString = "insert into dept (deptname,note) values('" + region + "','" + name + "' )";
}
//1.引用SqlConnection物件連接資料庫
using (SqlConnection cn = new SqlConnection(cs))
{
//2.開啟資料庫
cn.Open();
//3.引用SqlCommand物件
using (SqlCommand cmd = new SqlCommand(queryString, cn))
{
//4.執行SQL語法
cmd.ExecuteNonQuery();
}
}
}
VB
Private Sub button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles button1.Click, button2.Click
Dim region As String = textBox1.Text.ToString()
Dim name As String = textBox2.Text.ToString()
Dim cs As String = ""
Dim queryString As String = ""
If DirectCast((sender), Button).Text = "Insert Northwind" Then
cs = "server=localhost\sqlexpress;database=Northwind;integrated security=SSPI;"
queryString = ("insert into mytable (myregion,myname) values(N' " & region & "',N'") + name & "' )"
ElseIf DirectCast((sender), Button).Text = "Insert ADONETDB" Then
cs = "server=localhost\sqlexpress;database=ADONETDB;integrated security=SSPI;"
queryString = ("insert into dept (deptname,note) values('" & region & "','") + name & "' )"
End If
'1.引用SqlConnection物件連接資料庫
Using cn As New SqlConnection(cs)
'2.開啟資料庫
cn.Open()
'3.引用SqlCommand物件
Using cmd As New SqlCommand(queryString, cn)
'4.執行SQL語法
cmd.ExecuteNonQuery()
End Using
End Using
End Sub
如何查詢資料
1.引用SqlConnection物件連接資料庫
using (SqlConnection cn = new SqlConnection(cs))
2.開啟資料庫
cn.Open();
3.引用SqlCommand物件
using (SqlCommand command = new SqlCommand(qs, cn))
4.使用SqlDataReader讀取SqlCommand物件
SqlDataReader reader = command.ExecuteReader();
C#
private void button3_Click(object sender, EventArgs e)
{
listBox1.Items.Clear();
string cs="";
string qs = "";
if (((Button)(sender)).Text == "Select Northwind")
{
cs = "server=localhost\\sqlexpress;database=Northwind;integrated security=SSPI;";
qs = "SELECT myregion,myname FROM mytable;";
}
else if (((Button)(sender)).Text == "Select ADONETDB")
{
cs = "server=localhost\\sqlexpress;database=ADONETDB;integrated security=SSPI;";
qs = "SELECT deptname,note FROM dept;";
}
//1.引用SqlConnection物件連接資料庫
using (SqlConnection cn = new SqlConnection(cs))
{
//2.開啟資料庫
cn.Open();
//3.引用SqlCommand物件
using (SqlCommand command = new SqlCommand(qs, cn))
{
//4.使用SqlDataReader讀取SqlCommand物件
SqlDataReader reader = command.ExecuteReader();
try
{
while (reader.Read())
{
Console.WriteLine(String.Format("{0},{1}", reader[0], reader[1]));
string temp = reader[0].ToString() + "," + reader[1].ToString();
listBox1.Items.Add(temp);
}
}
finally
{
reader.Close();
}
}
}
}
VB
Private Sub button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles button3.Click, button4.Click
listBox1.Items.Clear()
Dim cs As String = ""
Dim qs As String = ""
If DirectCast((sender), Button).Text = "Select Northwind" Then
cs = "server=localhost\sqlexpress;database=Northwind;integrated security=SSPI;"
qs = "SELECT myregion,myname FROM mytable;"
ElseIf DirectCast((sender), Button).Text = "Select ADONETDB" Then
cs = "server=localhost\sqlexpress;database=ADONETDB;integrated security=SSPI;"
qs = "SELECT deptname,note FROM dept;"
End If
'1.引用SqlConnection物件連接資料庫
Using cn As New SqlConnection(cs)
'2.開啟資料庫
cn.Open()
'3.引用SqlCommand物件
Using command As New SqlCommand(qs, cn)
'4.使用SqlDataReader讀取SqlCommand物件
Dim reader As SqlDataReader = command.ExecuteReader()
Try
While reader.Read()
Console.WriteLine([String].Format("{0},{1}", reader(0), reader(1)))
Dim temp As String = (reader(0).ToString() & ",") + reader(1).ToString()
listBox1.Items.Add(temp)
End While
Finally
reader.Close()
End Try
End Using
End Using
End Sub
別忘了將資料庫加到SQL Server Management Studio Express 阿 ….
後記：這次在寫範例的時候碰到了一個問題：兩個不同的資料庫，下相同的插入指令，會產生不同的結果，可能是資料庫是在不同的SQL版本建立的，最後是加了N才解決。
範例下載：http://www.4shared.com/file/111000431/f26074f0/SQLCOMMAND.html
因為檔案包含了資料庫所以有點大，放在別的免空裡。
若有謬誤，煩請告知，新手發帖請多包涵
Microsoft MVP Award 2010~2017 C# 第四季
Microsoft MVP Award 2018~2022 .NET
