private DataTable RunSqlString()
{
using (SqlConnection conn = new SqlConnection(textBox1.Text))
{
DataTable dt = null;
String strCmd = textBox2.Text;// "Select md_date,md_room from meeting_date where md_date=@md_date and md_room=@md_room ";
using (SqlCommand cmd = new SqlCommand(strCmd, conn))
{
//cmd.Parameters.AddWithValue("@md_date", strdate);
try
{
conn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr.HasRows)
{
dt = new DataTable("tmp");
for (int i = 0; i < dr.FieldCount; i++)
{
dt.Columns.Add(dr.GetName(i));
}
while (dr.Read())
{
DataRow drw = dt.NewRow();
for (int i = 0; i < dr.FieldCount; i++)
{
drw[i] = dr[i].ToString();
}
dt.Rows.Add(drw);
}
}
else
{
MessageBox.Show("沒有資料!!!");
}
}
}
catch (Exception err)
{
MessageBox.Show(err.Message);
throw;
} //要先開啟連線
finally
{
conn.Close();
}
}
return dt;
}
}//結尾
=======================================================================
protected bool check_meetingroom_pk(string strdate ,string strroom ) {
bool b = false;
//步驟一給予適當的名稱空間namespace using System.Data.SqlClient;
//步驟二建立連線物件
String strConn = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
using (SqlConnection conn = new SqlConnection(strConn))
{
//步驟三建立Command物件讀取資料庫的資料
String strCmd = "Select md_date,md_room from meeting_date where md_date=@md_date and md_room=@md_room ";
using (SqlCommand cmd = new SqlCommand(strCmd, conn))
{
cmd.Parameters.AddWithValue("@md_date", strdate);
cmd.Parameters.AddWithValue("@md_room", strroom);
//步驟四建立DataReader物件處理讀出來的資料
//透過Command物件所提供的ExecuteReader的方法來產生Command
//要先開啟連線
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
//步驟五在瀏覽器上顯示資料
//讀取資料之前要先呼叫read()的方法
while (dr.Read())
{
b = true; // dr[0].ToString();
}
conn.Close();
}
}
return b;
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient; //SqlDataReader
using System.Configuration; //
public partial class M4_datar : System.Web.UI.Page
{
String strConn = "data source=.;user id=aa;password=aa;initial Catalog=northwind";
// String strConn = ConfigurationManager.ConnectionStrings["App_Data_ConnectionString"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
//步驟一給予適當的名稱空間namespace using System.Data.SqlClient;
//步驟二建立連線物件
// String strConn = "data source=.; Integrated Security = SSPI;initial Catalog=northwind";
// String strConn = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
using (SqlConnection conn = new SqlConnection(strConn))
{
//步驟三建立Command物件讀取資料庫的資料
String strCmd = "Select ProductID,ProductName from Products where categoryID=@categoryID";
using (SqlCommand cmd = new SqlCommand(strCmd, conn))
{
cmd.Parameters.AddWithValue("@categoryID", 1);
//步驟四建立DataReader物件處理讀出來的資料
//透過Command物件所提供的ExecuteReader的方法來產生Command
//要先開啟連線
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
//步驟五在瀏覽器上顯示資料
//讀取資料之前要先呼叫read()的方法
String str = "<ul>";
while (dr.Read())
{
str += "<li>" + dr[0].ToString() + "," + dr["ProductName"].ToString() + "</li>";
}
str += "</ul>";
Literal1.Text = str;
conn.Close();
}
};
//using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=Northwind;User Id=aa;Password=aa;")) //"Data Source=.;Initial Catalog=Northwind;User Id=aa;Password=aa;"
//{
// using (SqlCommand cmd = new SqlCommand("select * from Orders", conn))
// {
// conn.Open();
// using (SqlDataReader dr = cmd.ExecuteReader())
// {
// while (dr.Read()) //
// {
// Response.Write(dr[0].ToString() + "," + dr["CustomerID"].ToString() + "<br>");
// }
// }
// }
//}
}
protected void ButtonAdd_Click(object sender, EventArgs e)
{
//步驟一給予適當的名稱空間namespace using System.Data.SqlClient;
//步驟二建立連線物件
using (SqlConnection conn = new SqlConnection(strConn))
{
//步驟三建立Command物件讀取資料庫的資料
String strCmd = "Insert into Products (ProductName,CategoryID) values (@ProductName,@CategoryID)";
using (SqlCommand cmd = new SqlCommand(strCmd, conn))
{
cmd.Parameters.AddWithValue("@CategoryID", 1);
cmd.Parameters.AddWithValue("@ProductName", "iPhone 5S");
//步驟四將資料寫進資料庫
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
protected void ButtonUpdate_Click(object sender, EventArgs e)
{
//步驟一給予適當的名稱空間namespace using System.Data.SqlClient;
//步驟二建立連線物件
using (SqlConnection conn = new SqlConnection(strConn))
{
//步驟三建立Command物件讀取資料庫的資料
String strCmd = "Update Products set ProductName=@ProductName where ProductID=@ProductID ";
using (SqlCommand cmd = new SqlCommand(strCmd, conn))
{
cmd.Parameters.AddWithValue("@ProductID", 80);
cmd.Parameters.AddWithValue("@ProductName", "iPhone_5s");
//步驟四修改資料
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
protected void ButtonDelete_Click(object sender, EventArgs e)
{
//步驟一給予適當的名稱空間namespace using System.Data.SqlClient;
//步驟二建立連線物件
using (SqlConnection conn = new SqlConnection(strConn))
{
//步驟三建立Command物件讀取資料庫的資料
String strCmd = "Delete Products where ProductID=@ProductID ";
using (SqlCommand cmd = new SqlCommand(strCmd, conn))
{
cmd.Parameters.AddWithValue("@ProductID", 80);
//步驟四刪除資料
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
}
沒有留言:
張貼留言