1using System;
2using System.Collections;
3using System.ComponentModel;
4using System.Data;
5using System.Drawing;
6using System.Web;
7using System.Web.SessionState;
8using System.Web.UI;
9using System.Web.UI.WebControls;
10using System.Web.UI.HtmlControls;
11
12namespace DataGridDemoCS
13{
14 ///
15 /// MSDNDemo1 的摘要说明。
16 ///
17 public class MSDNDemo1 : System.Web.UI.Page
18 {
19 protected System.Web.UI.WebControls.DataGrid DataGrid1;
20 protected System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1;
21 protected System.Data.SqlClient.SqlCommand sqlSelectCommand1;
22 protected System.Data.SqlClient.SqlCommand sqlInsertCommand1;
23 protected System.Data.SqlClient.SqlCommand sqlUpdateCommand1;
24 protected System.Data.SqlClient.SqlCommand sqlDeleteCommand1;
25 protected DataGridDemoCS.dsCustomers dsCustomers1;
26 protected System.Data.SqlClient.SqlConnection sqlConnection1;
27
28 private void Page_Load(object sender, System.EventArgs e)
29 {
30 // 在此处放置用户代码以初始化页面
31 if (!Page.IsPostBack)
32 {
33 bindData();
34
35
36
37 }
38 }
39 void bindData()
40 {
41 sqlDataAdapter1.Fill(dsCustomers1);
42 DataGrid1.DataBind();
43 }
44
45 #region Web 窗体设计器生成的代码
46 override protected void OnInit(EventArgs e)
47 {
48 //
49 // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
50 //
51 InitializeComponent();
52 base.OnInit(e);
53 }
54
55 ///
56 /// 设计器支持所需的方法 - 不要使用代码编辑器修改
57 /// 此方法的内容。
58 ///
59 private void InitializeComponent()
60 {
61 this.sqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter();
62 this.sqlDeleteCommand1 = new System.Data.SqlClient.SqlCommand();
63 this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();
64 this.sqlInsertCommand1 = new System.Data.SqlClient.SqlCommand();
65 this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand();
66 this.sqlUpdateCommand1 = new System.Data.SqlClient.SqlCommand();
67 this.dsCustomers1 = new DataGridDemoCS.dsCustomers();
68 ((System.ComponentModel.ISupportInitialize)(this.dsCustomers1)).BeginInit();
69 this.DataGrid1.CancelCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.DataGrid1_CancelCommand);
70 this.DataGrid1.EditCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.DataGrid1_EditCommand);
71 this.DataGrid1.UpdateCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.DataGrid1_UpdateCommand);
72 this.DataGrid1.DeleteCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.DataGrid1_DeleteCommand);
73 this.DataGrid1.ItemDataBound += new System.Web.UI.WebControls.DataGridItemEventHandler(this.DataGrid1_ItemDataBound);
74 //
75 // sqlDataAdapter1
76 //
77 this.sqlDataAdapter1.DeleteCommand = this.sqlDeleteCommand1;
78 this.sqlDataAdapter1.InsertCommand = this.sqlInsertCommand1;
79 this.sqlDataAdapter1.SelectCommand = this.sqlSelectCommand1;
80 this.sqlDataAdapter1.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
81 new System.Data.Common.DataTableMapping("Table", "Customers", new System.Data.Common.DataColumnMapping[] {
82 new System.Data.Common.DataColumnMapping("CustomerID", "CustomerID"),
83 new System.Data.Common.DataColumnMapping("CompanyName", "CompanyName"),
84 new System.Data.Common.DataColumnMapping("ContactName", "ContactName"),
85 new System.Data.Common.DataColumnMapping("ContactTitle", "ContactTitle"),
86 new System.Data.Common.DataColumnMapping("Address", "Address"),
87 new System.Data.Common.DataColumnMapping("City", "City"),
88 new System.Data.Common.DataColumnMapping("Region", "Region"),
89 new System.Data.Common.DataColumnMapping("PostalCode", "PostalCode"),
90 new System.Data.Common.DataColumnMapping("Country", "Country"),
91 new System.Data.Common.DataColumnMapping("Phone", "Phone"),
92 new System.Data.Common.DataColumnMapping("Fax", "Fax")})});
93 this.sqlDataAdapter1.UpdateCommand = this.sqlUpdateCommand1;
94 //
95 // sqlDeleteCommand1
96 //
97 this.sqlDeleteCommand1.CommandText = @"DELETE FROM Customers WHERE (CustomerID = @Original_CustomerID) AND (Address = @Original_Address OR @Original_Address IS NULL AND Address IS NULL) AND (City = @Original_City OR @Original_City IS NULL AND City IS NULL) AND (CompanyName = @Original_CompanyName) AND (ContactName = @Original_ContactName OR @Original_ContactName IS NULL AND ContactName IS NULL) AND (ContactTitle = @Original_ContactTitle OR @Original_ContactTitle IS NULL AND ContactTitle IS NULL) AND (Country = @Original_Country OR @Original_Country IS NULL AND Country IS NULL) AND (Fax = @Original_Fax OR @Original_Fax IS NULL AND Fax IS NULL) AND (Phone = @Original_Phone OR @Original_Phone IS NULL AND Phone IS NULL) AND (PostalCode = @Original_PostalCode OR @Original_PostalCode IS NULL AND PostalCode IS NULL) AND (Region = @Original_Region OR @Original_Region IS NULL AND Region IS NULL)";
98 this.sqlDeleteCommand1.Connection = this.sqlConnection1;
99 this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_CustomerID", System.Data.SqlDbType.NVarChar, 5, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "CustomerID", System.Data.DataRowVersion.Original, null));
100 this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_Address", System.Data.SqlDbType.NVarChar, 60, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Address", System.Data.DataRowVersion.Original, null));
101 this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_City", System.Data.SqlDbType.NVarChar, 15, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "City", System.Data.DataRowVersion.Original, null));
102 this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_CompanyName", System.Data.SqlDbType.NVarChar, 40, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "CompanyName", System.Data.DataRowVersion.Original, null));
103 this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_ContactName", System.Data.SqlDbType.NVarChar, 30, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "ContactName", System.Data.DataRowVersion.Original, null));
104 this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_ContactTitle", System.Data.SqlDbType.NVarChar, 30, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "ContactTitle", System.Data.DataRowVersion.Original, null));
105 this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_Country", System.Data.SqlDbType.NVarChar, 15, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Country", System.Data.DataRowVersion.Original, null));
106 this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_Fax", System.Data.SqlDbType.NVarChar, 24, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Fax", System.Data.DataRowVersion.Original, null));
107 this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_Phone", System.Data.SqlDbType.NVarChar, 24, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Phone", System.Data.DataRowVersion.Original, null));
108 this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_PostalCode", System.Data.SqlDbType.NVarChar, 10, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "PostalCode", System.Data.DataRowVersion.Original, null));
109 this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_Region", System.Data.SqlDbType.NVarChar, 15, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Region", System.Data.DataRowVersion.Original, null));
110 //
111 // sqlConnection1
112 //
113 this.sqlConnection1.ConnectionString = "workstation id=\"FANCY-FAMILY\";packet size=4096;user id=sa;data source=\"FANCY-FAMI" +
114 "LY\";persist security info=False;initial catalog=Northwind";
115 //
116 // sqlInsertCommand1
117 //
118 this.sqlInsertCommand1.CommandText = @"INSERT INTO Customers(CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax) VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax); SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)";
119 this.sqlInsertCommand1.Connection = this.sqlConnection1;
120 this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CustomerID", System.Data.SqlDbType.NVarChar, 5, "CustomerID"));
121 this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CompanyName", System.Data.SqlDbType.NVarChar, 40, "CompanyName"));
122 this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ContactName", System.Data.SqlDbType.NVarChar, 30, "ContactName"));
123 this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ContactTitle", System.Data.SqlDbType.NVarChar, 30, "ContactTitle"));
124 this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Address", System.Data.SqlDbType.NVarChar, 60, "Address"));
125 this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@City", System.Data.SqlDbType.NVarChar, 15, "City"));
126 this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Region", System.Data.SqlDbType.NVarChar, 15, "Region"));
127 this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PostalCode", System.Data.SqlDbType.NVarChar, 10, "PostalCode"));
128 this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Country", System.Data.SqlDbType.NVarChar, 15, "Country"));
129 this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Phone", System.Data.SqlDbType.NVarChar, 24, "Phone"));
130 this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Fax", System.Data.SqlDbType.NVarChar, 24, "Fax"));
131 //
132 // sqlSelectCommand1
133 //
134 this.sqlSelectCommand1.CommandText = "SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region," +
135 " PostalCode, Country, Phone, Fax FROM Customers";
136 this.sqlSelectCommand1.Connection = this.sqlConnection1;
137 //
138 // sqlUpdateCommand1
139 //
140 this.sqlUpdateCommand1.CommandText = @"UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName, ContactName = @ContactName, ContactTitle = @ContactTitle, Address = @Address, City = @City, Region = @Region, PostalCode = @PostalCode, Country = @Country, Phone = @Phone, Fax = @Fax WHERE (CustomerID = @Original_CustomerID) AND (Address = @Original_Address OR @Original_Address IS NULL AND Address IS NULL) AND (City = @Original_City OR @Original_City IS NULL AND City IS NULL) AND (CompanyName = @Original_CompanyName) AND (ContactName = @Original_ContactName OR @Original_ContactName IS NULL AND ContactName IS NULL) AND (ContactTitle = @Original_ContactTitle OR @Original_ContactTitle IS NULL AND ContactTitle IS NULL) AND (Country = @Original_Country OR @Original_Country IS NULL AND Country IS NULL) AND (Fax = @Original_Fax OR @Original_Fax IS NULL AND Fax IS NULL) AND (Phone = @Original_Phone OR @Original_Phone IS NULL AND Phone IS NULL) AND (PostalCode = @Original_PostalCode OR @Original_PostalCode IS NULL AND PostalCode IS NULL) AND (Region = @Original_Region OR @Original_Region IS NULL AND Region IS NULL); SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)";
141 this.sqlUpdateCommand1.Connection = this.sqlConnection1;
142 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CustomerID", System.Data.SqlDbType.NVarChar, 5, "CustomerID"));
143 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CompanyName", System.Data.SqlDbType.NVarChar, 40, "CompanyName"));
144 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ContactName", System.Data.SqlDbType.NVarChar, 30, "ContactName"));
145 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ContactTitle", System.Data.SqlDbType.NVarChar, 30, "ContactTitle"));
146 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Address", System.Data.SqlDbType.NVarChar, 60, "Address"));
147 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@City", System.Data.SqlDbType.NVarChar, 15, "City"));
148 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Region", System.Data.SqlDbType.NVarChar, 15, "Region"));
149 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PostalCode", System.Data.SqlDbType.NVarChar, 10, "PostalCode"));
150 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Country", System.Data.SqlDbType.NVarChar, 15, "Country"));
151 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Phone", System.Data.SqlDbType.NVarChar, 24, "Phone"));
152 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Fax", System.Data.SqlDbType.NVarChar, 24, "Fax"));
153 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_CustomerID", System.Data.SqlDbType.NVarChar, 5, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "CustomerID", System.Data.DataRowVersion.Original, null));
154 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_Address", System.Data.SqlDbType.NVarChar, 60, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Address", System.Data.DataRowVersion.Original, null));
155 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_City", System.Data.SqlDbType.NVarChar, 15, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "City", System.Data.DataRowVersion.Original, null));
156 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_CompanyName", System.Data.SqlDbType.NVarChar, 40, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "CompanyName", System.Data.DataRowVersion.Original, null));
157 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_ContactName", System.Data.SqlDbType.NVarChar, 30, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "ContactName", System.Data.DataRowVersion.Original, null));
158 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_ContactTitle", System.Data.SqlDbType.NVarChar, 30, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "ContactTitle", System.Data.DataRowVersion.Original, null));
159 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_Country", System.Data.SqlDbType.NVarChar, 15, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Country", System.Data.DataRowVersion.Original, null));
160 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_Fax", System.Data.SqlDbType.NVarChar, 24, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Fax", System.Data.DataRowVersion.Original, null));
161 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_Phone", System.Data.SqlDbType.NVarChar, 24, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Phone", System.Data.DataRowVersion.Original, null));
162 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_PostalCode", System.Data.SqlDbType.NVarChar, 10, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "PostalCode", System.Data.DataRowVersion.Original, null));
163 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_Region", System.Data.SqlDbType.NVarChar, 15, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Region", System.Data.DataRowVersion.Original, null));
164 //
165 // dsCustomers1
166 //
167 this.dsCustomers1.DataSetName = "dsCustomers";
168 this.dsCustomers1.Locale = new System.Globalization.CultureInfo("zh-CN");
169 this.Load += new System.EventHandler(this.Page_Load);
170 ((System.ComponentModel.ISupportInitialize)(this.dsCustomers1)).EndInit();
171
172 }
173 #endregion
174
175 private void DataGrid1_EditCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
176 {
177 DataGrid1.EditItemIndex=e.Item.ItemIndex;
178 bindData();
179 }
180
181 private void DataGrid1_UpdateCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
182 {
183 string CustomerID=e.Item.Cells[1].Text;
184 string CompanyName=((TextBox)e.Item.Cells[2].Controls[0]).Text;
185 string ContactName=((TextBox)e.Item.Cells[3].Controls[0]).Text;
186 string ContactTitle=((TextBox)e.Item.Cells[4].Controls[0]).Text;
187 string Address=((TextBox)e.Item.Cells[5].Controls[0]).Text;
188 string City=((TextBox)e.Item.Cells[6].Controls[0]).Text;
189 string Region=((TextBox)e.Item.Cells[7].Controls[0]).Text;
190 string Country=((TextBox)e.Item.Cells[8].Controls[0]).Text;
191 sqlDataAdapter1.Fill(dsCustomers1);
192 dsCustomers.CustomersRow dr=dsCustomers1.Customers.FindByCustomerID(CustomerID);
193 dr.CompanyName=CompanyName;
194 dr.ContactName=ContactName;
195 dr.ContactTitle=ContactTitle;
196 dr.Address=Address;
197 dr.Region=Region;
198 dr.Country=Country;
199 sqlDataAdapter1.Update(dsCustomers1);
200 dsCustomers1.AcceptChanges();
201 DataGrid1.EditItemIndex=-1;
202 bindData();
203 }
204
205 private void DataGrid1_DeleteCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
206 {
207 string CustomerID=e.Item.Cells[1].Text;
208 sqlDataAdapter1.Fill(dsCustomers1);
209 dsCustomers.CustomersRow dr=dsCustomers1.Customers.FindByCustomerID(CustomerID);
210 dr.Delete();
211 sqlDataAdapter1.Update(dsCustomers1);
212 dsCustomers1.AcceptChanges();
213 bindData();
214 }
215
216 private void DataGrid1_CancelCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
217 {
218 DataGrid1.EditItemIndex=-1;
219 bindData();
220 }
221
222 private void DataGrid1_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
223 {
224 if (e.Item.ItemType==ListItemType.Item || e.Item.ItemType==ListItemType.AlternatingItem)
225 {
226 LinkButton l=(LinkButton)e.Item.Cells[11].Controls[0];
227 DataRowView drv=(DataRowView)e.Item.DataItem;
228 l.Attributes.Add("onclick","javascript:return confirm('你确认要删除"+drv.Row.ItemArray[0]+"?');");
229 Label label=(Label)e.Item.FindControl("LblID");
230 label.Text=Convert.ToString(e.Item.ItemIndex+1);
231 }
232 }
233
234
235 }
236}
237
2using System.Collections;
3using System.ComponentModel;
4using System.Data;
5using System.Drawing;
6using System.Web;
7using System.Web.SessionState;
8using System.Web.UI;
9using System.Web.UI.WebControls;
10using System.Web.UI.HtmlControls;
11
12namespace DataGridDemoCS
13{
14 ///
15 /// MSDNDemo1 的摘要说明。
16 ///
17 public class MSDNDemo1 : System.Web.UI.Page
18 {
19 protected System.Web.UI.WebControls.DataGrid DataGrid1;
20 protected System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1;
21 protected System.Data.SqlClient.SqlCommand sqlSelectCommand1;
22 protected System.Data.SqlClient.SqlCommand sqlInsertCommand1;
23 protected System.Data.SqlClient.SqlCommand sqlUpdateCommand1;
24 protected System.Data.SqlClient.SqlCommand sqlDeleteCommand1;
25 protected DataGridDemoCS.dsCustomers dsCustomers1;
26 protected System.Data.SqlClient.SqlConnection sqlConnection1;
27
28 private void Page_Load(object sender, System.EventArgs e)
29 {
30 // 在此处放置用户代码以初始化页面
31 if (!Page.IsPostBack)
32 {
33 bindData();
34
35
36
37 }
38 }
39 void bindData()
40 {
41 sqlDataAdapter1.Fill(dsCustomers1);
42 DataGrid1.DataBind();
43 }
44
45 #region Web 窗体设计器生成的代码
46 override protected void OnInit(EventArgs e)
47 {
48 //
49 // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
50 //
51 InitializeComponent();
52 base.OnInit(e);
53 }
54
55 ///
56 /// 设计器支持所需的方法 - 不要使用代码编辑器修改
57 /// 此方法的内容。
58 ///
59 private void InitializeComponent()
60 {
61 this.sqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter();
62 this.sqlDeleteCommand1 = new System.Data.SqlClient.SqlCommand();
63 this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();
64 this.sqlInsertCommand1 = new System.Data.SqlClient.SqlCommand();
65 this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand();
66 this.sqlUpdateCommand1 = new System.Data.SqlClient.SqlCommand();
67 this.dsCustomers1 = new DataGridDemoCS.dsCustomers();
68 ((System.ComponentModel.ISupportInitialize)(this.dsCustomers1)).BeginInit();
69 this.DataGrid1.CancelCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.DataGrid1_CancelCommand);
70 this.DataGrid1.EditCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.DataGrid1_EditCommand);
71 this.DataGrid1.UpdateCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.DataGrid1_UpdateCommand);
72 this.DataGrid1.DeleteCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.DataGrid1_DeleteCommand);
73 this.DataGrid1.ItemDataBound += new System.Web.UI.WebControls.DataGridItemEventHandler(this.DataGrid1_ItemDataBound);
74 //
75 // sqlDataAdapter1
76 //
77 this.sqlDataAdapter1.DeleteCommand = this.sqlDeleteCommand1;
78 this.sqlDataAdapter1.InsertCommand = this.sqlInsertCommand1;
79 this.sqlDataAdapter1.SelectCommand = this.sqlSelectCommand1;
80 this.sqlDataAdapter1.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
81 new System.Data.Common.DataTableMapping("Table", "Customers", new System.Data.Common.DataColumnMapping[] {
82 new System.Data.Common.DataColumnMapping("CustomerID", "CustomerID"),
83 new System.Data.Common.DataColumnMapping("CompanyName", "CompanyName"),
84 new System.Data.Common.DataColumnMapping("ContactName", "ContactName"),
85 new System.Data.Common.DataColumnMapping("ContactTitle", "ContactTitle"),
86 new System.Data.Common.DataColumnMapping("Address", "Address"),
87 new System.Data.Common.DataColumnMapping("City", "City"),
88 new System.Data.Common.DataColumnMapping("Region", "Region"),
89 new System.Data.Common.DataColumnMapping("PostalCode", "PostalCode"),
90 new System.Data.Common.DataColumnMapping("Country", "Country"),
91 new System.Data.Common.DataColumnMapping("Phone", "Phone"),
92 new System.Data.Common.DataColumnMapping("Fax", "Fax")})});
93 this.sqlDataAdapter1.UpdateCommand = this.sqlUpdateCommand1;
94 //
95 // sqlDeleteCommand1
96 //
97 this.sqlDeleteCommand1.CommandText = @"DELETE FROM Customers WHERE (CustomerID = @Original_CustomerID) AND (Address = @Original_Address OR @Original_Address IS NULL AND Address IS NULL) AND (City = @Original_City OR @Original_City IS NULL AND City IS NULL) AND (CompanyName = @Original_CompanyName) AND (ContactName = @Original_ContactName OR @Original_ContactName IS NULL AND ContactName IS NULL) AND (ContactTitle = @Original_ContactTitle OR @Original_ContactTitle IS NULL AND ContactTitle IS NULL) AND (Country = @Original_Country OR @Original_Country IS NULL AND Country IS NULL) AND (Fax = @Original_Fax OR @Original_Fax IS NULL AND Fax IS NULL) AND (Phone = @Original_Phone OR @Original_Phone IS NULL AND Phone IS NULL) AND (PostalCode = @Original_PostalCode OR @Original_PostalCode IS NULL AND PostalCode IS NULL) AND (Region = @Original_Region OR @Original_Region IS NULL AND Region IS NULL)";
98 this.sqlDeleteCommand1.Connection = this.sqlConnection1;
99 this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_CustomerID", System.Data.SqlDbType.NVarChar, 5, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "CustomerID", System.Data.DataRowVersion.Original, null));
100 this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_Address", System.Data.SqlDbType.NVarChar, 60, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Address", System.Data.DataRowVersion.Original, null));
101 this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_City", System.Data.SqlDbType.NVarChar, 15, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "City", System.Data.DataRowVersion.Original, null));
102 this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_CompanyName", System.Data.SqlDbType.NVarChar, 40, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "CompanyName", System.Data.DataRowVersion.Original, null));
103 this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_ContactName", System.Data.SqlDbType.NVarChar, 30, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "ContactName", System.Data.DataRowVersion.Original, null));
104 this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_ContactTitle", System.Data.SqlDbType.NVarChar, 30, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "ContactTitle", System.Data.DataRowVersion.Original, null));
105 this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_Country", System.Data.SqlDbType.NVarChar, 15, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Country", System.Data.DataRowVersion.Original, null));
106 this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_Fax", System.Data.SqlDbType.NVarChar, 24, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Fax", System.Data.DataRowVersion.Original, null));
107 this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_Phone", System.Data.SqlDbType.NVarChar, 24, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Phone", System.Data.DataRowVersion.Original, null));
108 this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_PostalCode", System.Data.SqlDbType.NVarChar, 10, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "PostalCode", System.Data.DataRowVersion.Original, null));
109 this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_Region", System.Data.SqlDbType.NVarChar, 15, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Region", System.Data.DataRowVersion.Original, null));
110 //
111 // sqlConnection1
112 //
113 this.sqlConnection1.ConnectionString = "workstation id=\"FANCY-FAMILY\";packet size=4096;user id=sa;data source=\"FANCY-FAMI" +
114 "LY\";persist security info=False;initial catalog=Northwind";
115 //
116 // sqlInsertCommand1
117 //
118 this.sqlInsertCommand1.CommandText = @"INSERT INTO Customers(CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax) VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax); SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)";
119 this.sqlInsertCommand1.Connection = this.sqlConnection1;
120 this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CustomerID", System.Data.SqlDbType.NVarChar, 5, "CustomerID"));
121 this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CompanyName", System.Data.SqlDbType.NVarChar, 40, "CompanyName"));
122 this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ContactName", System.Data.SqlDbType.NVarChar, 30, "ContactName"));
123 this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ContactTitle", System.Data.SqlDbType.NVarChar, 30, "ContactTitle"));
124 this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Address", System.Data.SqlDbType.NVarChar, 60, "Address"));
125 this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@City", System.Data.SqlDbType.NVarChar, 15, "City"));
126 this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Region", System.Data.SqlDbType.NVarChar, 15, "Region"));
127 this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PostalCode", System.Data.SqlDbType.NVarChar, 10, "PostalCode"));
128 this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Country", System.Data.SqlDbType.NVarChar, 15, "Country"));
129 this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Phone", System.Data.SqlDbType.NVarChar, 24, "Phone"));
130 this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Fax", System.Data.SqlDbType.NVarChar, 24, "Fax"));
131 //
132 // sqlSelectCommand1
133 //
134 this.sqlSelectCommand1.CommandText = "SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region," +
135 " PostalCode, Country, Phone, Fax FROM Customers";
136 this.sqlSelectCommand1.Connection = this.sqlConnection1;
137 //
138 // sqlUpdateCommand1
139 //
140 this.sqlUpdateCommand1.CommandText = @"UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName, ContactName = @ContactName, ContactTitle = @ContactTitle, Address = @Address, City = @City, Region = @Region, PostalCode = @PostalCode, Country = @Country, Phone = @Phone, Fax = @Fax WHERE (CustomerID = @Original_CustomerID) AND (Address = @Original_Address OR @Original_Address IS NULL AND Address IS NULL) AND (City = @Original_City OR @Original_City IS NULL AND City IS NULL) AND (CompanyName = @Original_CompanyName) AND (ContactName = @Original_ContactName OR @Original_ContactName IS NULL AND ContactName IS NULL) AND (ContactTitle = @Original_ContactTitle OR @Original_ContactTitle IS NULL AND ContactTitle IS NULL) AND (Country = @Original_Country OR @Original_Country IS NULL AND Country IS NULL) AND (Fax = @Original_Fax OR @Original_Fax IS NULL AND Fax IS NULL) AND (Phone = @Original_Phone OR @Original_Phone IS NULL AND Phone IS NULL) AND (PostalCode = @Original_PostalCode OR @Original_PostalCode IS NULL AND PostalCode IS NULL) AND (Region = @Original_Region OR @Original_Region IS NULL AND Region IS NULL); SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)";
141 this.sqlUpdateCommand1.Connection = this.sqlConnection1;
142 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CustomerID", System.Data.SqlDbType.NVarChar, 5, "CustomerID"));
143 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CompanyName", System.Data.SqlDbType.NVarChar, 40, "CompanyName"));
144 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ContactName", System.Data.SqlDbType.NVarChar, 30, "ContactName"));
145 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ContactTitle", System.Data.SqlDbType.NVarChar, 30, "ContactTitle"));
146 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Address", System.Data.SqlDbType.NVarChar, 60, "Address"));
147 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@City", System.Data.SqlDbType.NVarChar, 15, "City"));
148 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Region", System.Data.SqlDbType.NVarChar, 15, "Region"));
149 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PostalCode", System.Data.SqlDbType.NVarChar, 10, "PostalCode"));
150 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Country", System.Data.SqlDbType.NVarChar, 15, "Country"));
151 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Phone", System.Data.SqlDbType.NVarChar, 24, "Phone"));
152 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Fax", System.Data.SqlDbType.NVarChar, 24, "Fax"));
153 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_CustomerID", System.Data.SqlDbType.NVarChar, 5, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "CustomerID", System.Data.DataRowVersion.Original, null));
154 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_Address", System.Data.SqlDbType.NVarChar, 60, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Address", System.Data.DataRowVersion.Original, null));
155 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_City", System.Data.SqlDbType.NVarChar, 15, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "City", System.Data.DataRowVersion.Original, null));
156 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_CompanyName", System.Data.SqlDbType.NVarChar, 40, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "CompanyName", System.Data.DataRowVersion.Original, null));
157 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_ContactName", System.Data.SqlDbType.NVarChar, 30, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "ContactName", System.Data.DataRowVersion.Original, null));
158 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_ContactTitle", System.Data.SqlDbType.NVarChar, 30, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "ContactTitle", System.Data.DataRowVersion.Original, null));
159 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_Country", System.Data.SqlDbType.NVarChar, 15, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Country", System.Data.DataRowVersion.Original, null));
160 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_Fax", System.Data.SqlDbType.NVarChar, 24, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Fax", System.Data.DataRowVersion.Original, null));
161 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_Phone", System.Data.SqlDbType.NVarChar, 24, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Phone", System.Data.DataRowVersion.Original, null));
162 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_PostalCode", System.Data.SqlDbType.NVarChar, 10, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "PostalCode", System.Data.DataRowVersion.Original, null));
163 this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_Region", System.Data.SqlDbType.NVarChar, 15, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "Region", System.Data.DataRowVersion.Original, null));
164 //
165 // dsCustomers1
166 //
167 this.dsCustomers1.DataSetName = "dsCustomers";
168 this.dsCustomers1.Locale = new System.Globalization.CultureInfo("zh-CN");
169 this.Load += new System.EventHandler(this.Page_Load);
170 ((System.ComponentModel.ISupportInitialize)(this.dsCustomers1)).EndInit();
171
172 }
173 #endregion
174
175 private void DataGrid1_EditCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
176 {
177 DataGrid1.EditItemIndex=e.Item.ItemIndex;
178 bindData();
179 }
180
181 private void DataGrid1_UpdateCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
182 {
183 string CustomerID=e.Item.Cells[1].Text;
184 string CompanyName=((TextBox)e.Item.Cells[2].Controls[0]).Text;
185 string ContactName=((TextBox)e.Item.Cells[3].Controls[0]).Text;
186 string ContactTitle=((TextBox)e.Item.Cells[4].Controls[0]).Text;
187 string Address=((TextBox)e.Item.Cells[5].Controls[0]).Text;
188 string City=((TextBox)e.Item.Cells[6].Controls[0]).Text;
189 string Region=((TextBox)e.Item.Cells[7].Controls[0]).Text;
190 string Country=((TextBox)e.Item.Cells[8].Controls[0]).Text;
191 sqlDataAdapter1.Fill(dsCustomers1);
192 dsCustomers.CustomersRow dr=dsCustomers1.Customers.FindByCustomerID(CustomerID);
193 dr.CompanyName=CompanyName;
194 dr.ContactName=ContactName;
195 dr.ContactTitle=ContactTitle;
196 dr.Address=Address;
197 dr.Region=Region;
198 dr.Country=Country;
199 sqlDataAdapter1.Update(dsCustomers1);
200 dsCustomers1.AcceptChanges();
201 DataGrid1.EditItemIndex=-1;
202 bindData();
203 }
204
205 private void DataGrid1_DeleteCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
206 {
207 string CustomerID=e.Item.Cells[1].Text;
208 sqlDataAdapter1.Fill(dsCustomers1);
209 dsCustomers.CustomersRow dr=dsCustomers1.Customers.FindByCustomerID(CustomerID);
210 dr.Delete();
211 sqlDataAdapter1.Update(dsCustomers1);
212 dsCustomers1.AcceptChanges();
213 bindData();
214 }
215
216 private void DataGrid1_CancelCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
217 {
218 DataGrid1.EditItemIndex=-1;
219 bindData();
220 }
221
222 private void DataGrid1_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
223 {
224 if (e.Item.ItemType==ListItemType.Item || e.Item.ItemType==ListItemType.AlternatingItem)
225 {
226 LinkButton l=(LinkButton)e.Item.Cells[11].Controls[0];
227 DataRowView drv=(DataRowView)e.Item.DataItem;
228 l.Attributes.Add("onclick","javascript:return confirm('你确认要删除"+drv.Row.ItemArray[0]+"?');");
229 Label label=(Label)e.Item.FindControl("LblID");
230 label.Text=Convert.ToString(e.Item.ItemIndex+1);
231 }
232 }
233
234
235 }
236}
237