操作数据库
http://www.ntdrv.cn/Blog/Article.aspx?ID=1815
代码
1 using System;
2 using System.Collections.Generic;
3 using System.Web;
4 using System.Web.UI;
5 using System.Web.UI.WebControls;
6 using System.Data;
7 using System.Data.SqlClient;
8
9 namespace test
10 {
11 public partial class _Default : System.Web.UI.Page
12 {
13 protected void Page_Load(object sender, EventArgs e)
14 {
15 SqlConnection connection = new SqlConnection("Data Source=SHNI033\\SQLEXPRESS;Initial Catalog=Ticket;Integrated Security=true;");
16 connection.Open();
17 SqlDataAdapter dataAdpater = new SqlDataAdapter("SELECT TicketNo, IndentStatus FROM Ticket", connection);
18
19 dataAdpater.UpdateCommand = new SqlCommand("UPDATE Ticket SET IndentStatus = @IndentStatus " +
20 "WHERE TicketNo = @TicketNo", connection);
21
22 dataAdpater.UpdateCommand.Parameters.Add(
23 "@IndentStatus", SqlDbType.NVarChar, 15, "IndentStatus");
24
25 SqlParameter parameter = dataAdpater.UpdateCommand.Parameters.Add("@TicketNo", SqlDbType.VarChar);
26 parameter.SourceColumn = "TicketNo";
27 parameter.SourceVersion = DataRowVersion.Original;
28
29 DataTable ticketTable = new DataTable();
30 dataAdpater.Fill(ticketTable);
31
32 DataRow ticketRow = ticketTable.Rows[0];
33 ticketRow["IndentStatus"] = "New Beverages";
34
35 dataAdpater.Update(ticketTable);
36
37 GridView1.DataSource = ticketTable;
38 GridView1.DataBind();
39 connection.Close();
40
41 }
42
43 protected void btnQuery_Click(object sender, EventArgs e)
44 {
45 SqlConnection connection = new SqlConnection("Data Source=SHNI033\\SQLEXPRESS;Initial Catalog=Ticket;Integrated Security=true;");
46 connection.Open();
47 //SqlDataAdapter dataAdpater = new SqlDataAdapter("SELECT TicketNo, IndentStatus FROM Ticket", connection);
48 //DataTable ticketTable = new DataTable();
49 //dataAdpater.Fill(ticketTable);
50 //GridView1.DataSource = ticketTable;
51 //GridView1.DataBind();
52
53 string strSelect = "select count(*) from Ticket where TicketNo='1'";
54 SqlCommand commSelect = new SqlCommand(strSelect, connection);
55
56 if (Convert.ToInt32(commSelect.ExecuteScalar()) == 0)
57 {
58 lblTip.Text = "the ticket doesn't exist";
59 }
60 else
61 {
62 lblTip.Text = "the ticket exist";
63 }
64
65 connection.Close();
66 }
67
68 protected void btnInsert_Click(object sender, EventArgs e)
69 {
70 SqlConnection connection = new SqlConnection("Data Source=SHNI033\\SQLEXPRESS;Initial Catalog=Ticket;Integrated Security=true;");
71 connection.Open();
72 SqlDataAdapter dataAdpater = new SqlDataAdapter("SELECT * FROM Ticket", connection);
73 dataAdpater.InsertCommand = new SqlCommand("INSERT INTO Ticket(TicketNo,IndentType,IndentStatus,Occurred) " +
74 "VALUES (@TicketNo,@IndentType,@IndentStatus,@Occurred)", connection);
75
76 dataAdpater.InsertCommand.Parameters.Add("@TicketNo", SqlDbType.VarChar, 50, "TicketNo");
77 dataAdpater.InsertCommand.Parameters.Add("@IndentType", SqlDbType.VarChar, 50, "IndentType");
78 dataAdpater.InsertCommand.Parameters.Add("@IndentStatus", SqlDbType.VarChar, 50, "IndentStatus");
79 dataAdpater.InsertCommand.Parameters.Add("@Occurred", SqlDbType.VarChar, 50, "Occurred");
80
81 DataTable ticketTable = new DataTable();
82 dataAdpater.Fill(ticketTable);
83 DataRow dr = ticketTable.NewRow();
84 dr["TicketNo"] = "sdfs31";
85 dr["IndentType"] = "ter";
86 dr["IndentStatus"] = "Resigned";
87 dr["Occurred"] = "2010-12-26";
88
89 ticketTable.Rows.Add(dr);
90
91 dataAdpater.Update(ticketTable);
92 GridView1.DataSource = ticketTable;
93 GridView1.DataBind();
94 connection.Close();
95 }
96
97 protected void btnDel_Click(object sender, EventArgs e)
98 {
99 SqlConnection connection = new SqlConnection("Data Source=SHNI033\\SQLEXPRESS;Initial Catalog=Ticket;Integrated Security=true;");
100 connection.Open();
101 SqlDataAdapter dataAdpater = new SqlDataAdapter("SELECT TicketNo, IndentStatus FROM Ticket", connection);
102 DataTable ticketTable = new DataTable();
103 dataAdpater.Update(ticketTable);
104 dataAdpater.Fill(ticketTable);
105 dataAdpater.DeleteCommand = new SqlCommand("DELETE FROM Ticket " +
106 "WHERE TicketNo = @TicketNo", connection);
107
108 dataAdpater.DeleteCommand.Parameters.Add(
109 "@TicketNo", SqlDbType.NVarChar, 50, "TicketNo");
110
111 string strTicketNo = "1";
112 foreach (DataRow dr in ticketTable.Select("TicketNo='" + strTicketNo + "'"))
113 {
114 dr.Delete();
115 }
116 dataAdpater.Update(ticketTable);
117 GridView1.DataSource = ticketTable;
118 GridView1.DataBind();
119 connection.Close();
120 }
121
122 protected void btnUpdate_Click(object sender, EventArgs e)
123 {
124 SqlConnection connection = new SqlConnection("Data Source=SHNI033\\SQLEXPRESS;Initial Catalog=Ticket;Integrated Security=true;");
125 connection.Open();
126 SqlDataAdapter dataAdpater = new SqlDataAdapter("SELECT TicketNo, IndentStatus FROM Ticket", connection);
127
128 dataAdpater.UpdateCommand = new SqlCommand("UPDATE Ticket SET IndentStatus = @IndentStatus " +
129 "WHERE TicketNo = @TicketNo", connection);
130
131 dataAdpater.UpdateCommand.Parameters.Add("@IndentStatus", SqlDbType.VarChar, 50, "IndentStatus");
132 SqlParameter parameter = dataAdpater.UpdateCommand.Parameters.Add("@TicketNo", SqlDbType.VarChar);
133 parameter.SourceColumn = "TicketNo";
134 parameter.SourceVersion = DataRowVersion.Original;
135
136 DataTable ticketTable = new DataTable();
137 dataAdpater.Fill(ticketTable);
138
139 DataRow ticketRow = ticketTable.Rows[0];
140 ticketRow["IndentStatus"] = "New Beverages";
141
142 dataAdpater.Update(ticketTable);
143
144 GridView1.DataSource = ticketTable;
145 GridView1.DataBind();
146 connection.Close();
147 }
148 }
149 }
150
