可编辑 GridView， 无需 ObjectDataSource 概念
3.08/5 (7投票s)
一个 C# 示例，用于将 GridView 绑定到 DataTable，并允许排序、过滤、更新、删除和插入。
引言
这是我的第一篇文章，请多包涵。
有时，直接将 GridView 绑定到 ObjectDataSource 并非可行选项。本文将演示如何将 GridView 控件绑定到 DataTable，并允许插入、更新、删除、按列排序、分页和按字母过滤。我还包含了一个批量导入功能，用于导入 CSV 文件，该文件会检查列约束条件并将数据导入 SQL Server 数据库。结果将显示在刷新后的 GridView 中。
背景
编码最佳实践建议使用存储过程与数据库进行交互。这有助于避免可能危及安全的 SQL 注入攻击。此解决方案提供了有关如何将 GridView 绑定到存储过程结果的示例，同时仍能以各种方式与网格数据进行交互。
示例代码需要 Physician 表，该表具有以下结构
CREATE TABLE [dbo].[tPhysician](
[PhysicianID] [int] IDENTITY(1,1) NOT NULL,
[HospitalID] [int] NOT NULL,
[HospitalPhysicianID] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FirstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MiddleName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Suffix] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsActive] [bit] NOT NULL CONSTRAINT [DF_tPhysician_Active] DEFAULT ((1)),
CONSTRAINT [PK_tPhysician] PRIMARY KEY CLUSTERED
(
[PhysicianID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
Using the Code
请记住，虽然这段代码是为了导入和维护医生列表而编写的，但它可以调整以适应需要存储在数据库中并由用户维护的各种不同场景。我想提一下，虽然我很想声称这一切都是我从头开始写的，但我也不会浪费时间“重复造轮子”；因此，此解决方案的许多内容来自互联网上的各种站点，并非完全来自某个特定站点。
话虽如此，以下是您真正想要的内容
有两个类用于访问数据库。db 类是 SQL 调用的基础。它包含数据库服务器的连接，因此所有其他数据库类都继承自它。dbPhysician 类包含用于 SELECT 医生、UPDATE 医生、DELETE 医生以及导入医生 CSV 文件的调用。PhysicianSetup.aspx 页面及其 .cs 页面包含医生网格列表和导入的 GUI。此页面包含指向母版页的链接，未包含母版页，因此您可以删除该引用或创建自己的引用 （"~/DataEntry/DataEntry.master"）。还包含一个 .csv 文件，其中包含医生的样本以供导入，并指定布局。最后一个文件是一个 .sql 文件，用于创建必要的 SQL 表和 dbPhysician 类中引用的各种存储过程。
- PhysicianSetup.aspx - 主应用程序 GUI
- PhysicianSetup.aspx.cs - 主应用程序代码隐藏
- db.cs - 用于处理数据库调用的
db类 - dbPhysician.cs -
dbPhysician医生数据库类 - LotsOfPhysicians.csv - CSV 格式的医生导入样本文件
- GridViewWithoutDataSource.sql - Physician 表和存储过程的 T-SQL
从 PhysicianSetup.aspx 页面开始，下面的 GridView 定义如下。您会注意到每个字段实际上有三个定义：一个是标签（用于 Select），一个是文本框（用于 Update），还有一个是用于 Insert 的文本框。我们还启用了排序和分页，并定义了处理这些事件的自定义方法。
<asp:GridView ID="gvPhysician" runat="server"
AutoGenerateColumns="False" DataKeyNames="PhysicianID"
OnRowCancelingEdit="gvPhysician_RowCancelingEdit"
OnRowEditing="gvPhysician_RowEditing"
OnRowUpdating="gvPhysician_RowUpdating"
OnRowCommand="gvPhysician_RowCommand" ShowFooter="True"
AllowSorting="true" AllowPaging ="true"
OnRowDeleting="gvPhysician_RowDeleting"
OnSorting="gvPhysician_Sorting"
OnPageIndexChanging="gvPhysician_PageIndexChanging"
PageSize="15">
<columns>
<asp:TemplateField HeaderText="Last Name" SortExpression="LastName">
<edititemtemplate>
<asp:TextBox ID="txtLastName" runat="server"
Text='<%# Eval("LastName") %>'>
</edititemtemplate>
<footertemplate>
<asp:TextBox ID="txtNewLastName" runat="server">
</footertemplate>
<itemtemplate>
<asp:Label ID="Label2" runat="server"
Text='<%# Bind("LastName") %>'>
</itemtemplate>
<asp:TemplateField HeaderText="First Name"
SortExpression="FirstName">
<edititemtemplate>
<asp:TextBox ID="txtFirstName" runat="server"
Text='<%# Eval("FirstName") %>'>
</edititemtemplate>
<footertemplate>
<asp:TextBox ID="txtNewFirstName" runat="server" >
</footertemplate>
<itemtemplate>
<asp:Label ID="Label1" runat="server"
Text='<%# Bind("FirstName") %>'>
</itemtemplate>
<asp:TemplateField HeaderText="Middle Name"
SortExpression="MiddleName">
<edititemtemplate>
<asp:TextBox ID="txtMiddleName" runat="server"
Text='<%# Eval("MiddleName") %>'>
</edititemtemplate>
<footertemplate>
<asp:TextBox ID="txtNewMiddleName" runat="server" >
</footertemplate>
<itemtemplate>
<asp:Label ID="Label4" runat="server"
Text='<%# Bind("MiddleName") %>'>
</itemtemplate>
<asp:TemplateField HeaderText="Suffix" SortExpression="Suffix">
<edititemtemplate>
<asp:TextBox ID="txtSuffix" runat="server"
Text='<%# Eval("Suffix") %>'>
</edititemtemplate>
<footertemplate>
<asp:TextBox ID="txtNewSuffix" runat="server" >
</footertemplate>
<itemtemplate>
<asp:Label ID="Label5" runat="server"
Text='<%# Bind("Suffix") %>'>
</itemtemplate>
<asp:TemplateField HeaderText="Physician ID"
SortExpression="HospitalPhysicianID">
<edititemtemplate>
<asp:TextBox ID="txtHospitalPhysicianID"
runat="server" Text='<%# Eval("HospitalPhysicianID") %>'>
</edititemtemplate>
<footertemplate>
<asp:TextBox ID="txtNewHospitalPhysicianID" runat="server">
</footertemplate>
<itemtemplate>
<asp:Label ID="Label3" runat="server"
Text='<%# Bind("HospitalPhysicianID") %>'>
</itemtemplate>
<asp:TemplateField HeaderText="Edit" ShowHeader="False">
<edititemtemplate>
<asp:LinkButton ID="lbtnUpdate" runat="server"
CausesValidation="True" CommandName="Update"
Text="Update">
<asp:LinkButton ID="lbtnCancel" runat="server"
CausesValidation="False" CommandName="Cancel"
Text="Cancel">
</edititemtemplate>
<footertemplate>
<asp:LinkButton ID="lbtnInsert" runat="server"
CausesValidation="False" CommandName="AddNew"
Text="Add New">
</footertemplate>
<itemtemplate>
<asp:LinkButton ID="lbtEdit" runat="server"
CausesValidation="False" CommandName="Edit"
Text="Edit">
</itemtemplate>
<asp:CommandField HeaderText="Delete"
ShowDeleteButton="True" ShowHeader="True" />
</columns>
此页面上的第二个对象是 Repeater 控件，用于显示用于过滤/搜索网格的字母。
<asp:Repeater id="rptLetters" runat="server"
OnItemCommand="rptLetters_ItemCommand" >
<itemtemplate>
<asp:linkbutton id="lnkLetter" runat="server"
commandname="Filter"
commandargument='<%# DataBinder.Eval(Container, "DataItem.Letter")%>'>
<%# DataBinder.Eval(Container, "DataItem.Letter")%>
</itemtemplate>
页面上的最后一项是一个 HTML 文件输入框，用于选择包含要上传的医生的 CSV 文件。
<td id="Td2" runat="server">
<input type="file" runat="server"
id="PhysicianFile" size="40" />
</td>
<td id="Td3" align="Right" runat="server">
<asp:Button ID="btnImport" runat="server"
Text="Import" OnClick="btnImport_OnClick" />
</td>
在此页面的代码隐藏文件 PhysicianSetup.aspx.cs 中，定义的唯一变量是
protected string _letterFilter;
Page_Load 初始化我们的排序会话变量，调用过程加载医生网格，并调用过程将字母绑定到 Repeater。
object oFilter = ViewState[this.ToString() + "_LetterFilter"];
if (oFilter != null) _letterFilter = (string)oFilter;
else _letterFilter = "All";
if (!IsPostBack)
{
ViewState[this.ToString() + "_SortExpression"] = "LastName";
ViewState[this.ToString() + "_SortDirection"] = "ASC";
FillPhysicianInGrid(true);
letters_Bind();
}
这是将 GridView 绑定到 DataTable 的过程
private void FillPhysicianInGrid(bool Reload)
{
DataTable dtPhysician = null;
if (Reload)
{
dbPhysician physician = new dbPhysician();
dtPhysician = physician.Fetch();
dtPhysician.DefaultView.Sort = ViewState[this.ToString() +
"_SortExpression"].ToString() + " " +
ViewState[this.ToString() + "_SortDirection"].ToString();
}
else
{
//retrieve the session object datatable
dtPhysician = Session["tblPhysician"] as DataTable;
dtPhysician.DefaultView.Sort = ViewState[this.ToString() +
"_SortExpression"].ToString() + " " +
ViewState[this.ToString() + "_SortDirection"].ToString();
}
if (dtPhysician != null)
{
if (dtPhysician.Rows.Count > 0)
{
Session["tblPhysician"] = dtPhysician;
if (_letterFilter == "All")
dtPhysician.DefaultView.RowFilter = string.Empty;
else
dtPhysician.DefaultView.RowFilter =
"LastName LIKE '" + _letterFilter + "%'";
gvPhysician.DataSource = Session["tblPhysician"];
gvPhysician.DataBind();
}
else
{
dtPhysician.Rows.Add(dtPhysician.NewRow());
Session["tblPhysician"] = dtPhysician;
if (_letterFilter == "All")
dtPhysician.DefaultView.RowFilter = string.Empty;
else
dtPhysician.DefaultView.RowFilter =
"LastName LIKE '" + _letterFilter + "%'";
gvPhysician.DataSource = Session["tblPhysician"];
gvPhysician.DataBind();
int TotalColumns = gvPhysician.Rows[0].Cells.Count;
gvPhysician.Rows[0].Cells.Clear();
gvPhysician.Rows[0].Cells.Add(new TableCell());
gvPhysician.Rows[0].Cells[0].ColumnSpan = TotalColumns;
gvPhysician.Rows[0].Cells[0].Text = "No Record Found";
}
}
}
这是处理 GridView 的插入/添加的代码
protected void gvPhysician_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("AddNew"))
{
TextBox txtNewFirstName =
(TextBox)gvPhysician.FooterRow.FindControl("txtNewFirstName");
TextBox txtNewMiddleName =
(TextBox)gvPhysician.FooterRow.FindControl("txtNewMiddleName");
TextBox txtNewLastName =
(TextBox)gvPhysician.FooterRow.FindControl("txtNewLastName");
TextBox txtNewHospitalPhysicianID =
(TextBox)gvPhysician.FooterRow.FindControl("txtNewHospitalPhysicianID");
TextBox txtNewSuffix =
(TextBox)gvPhysician.FooterRow.FindControl("txtNewSuffix");
if (txtNewLastName.Text == "")
{
lblEntryStatus.Text = "*Last Name is a Required Field.";
}
else if (txtNewHospitalPhysicianID.Text == "")
{
lblEntryStatus.Text = "Physician ID is a Required Field.";
}
else
{
dbPhysician physician = new dbPhysician();
physician.Insert(txtNewFirstName.Text, txtNewMiddleName.Text,
txtNewLastName.Text, txtNewHospitalPhysicianID.Text, txtNewSuffix.Text);
lblEntryStatus.Text = "Physician Added.";
}
FillPhysicianInGrid(true);
}
}
这是准备 GridView 进行更新/取消的代码
protected void gvPhysician_RowEditing(object sender, GridViewEditEventArgs e)
{
gvPhysician.EditIndex = e.NewEditIndex;
FillPhysicianInGrid(false);
}
protected void gvPhysician_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
gvPhysician.EditIndex = -1;
FillPhysicianInGrid(false);
}
这是处理删除的代码
protected void gvPhysician_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
dbPhysician physician = new dbPhysician();
physician.Delete(Convert.ToInt32(gvPhysician.DataKeys[e.RowIndex].Values[0].ToString()));
lblEntryStatus.Text = "Physician Deleted.";
FillPhysicianInGrid(true);
}
这是处理 GridView 分页的代码
protected void gvPhysician_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
gvPhysician.PageIndex = e.NewPageIndex;
FillPhysicianInGrid(false);
}
这是处理排序的代码
protected void gvPhysician_Sorting(object sender, GridViewSortEventArgs e)
{
GetSortDirection(e.SortExpression);
FillPhysicianInGrid(false);
}
private void GetSortDirection(string sColumn)
{
//set sort direction to asc
string sSortDirection = "ASC";
string sSortExpression = ViewState[this.ToString() +
"_SortExpression"] as string;
if (sSortExpression != null)
{
//check same column is being sorted
if (sSortExpression == sColumn)
{
string sLastDirection = ViewState[this.ToString() +
"_SortDirection"] as string;
if ((sLastDirection != null) && (sLastDirection == "ASC"))
{
sSortDirection = "DESC";
}
}
}
//save new values in view
ViewState[this.ToString() + "_SortDirection"] = sSortDirection;
ViewState[this.ToString() + "_SortExpression"] = sColumn;
//return sSortDirection;
}
这是用于填充字母 Repeater 以按姓氏过滤 GridView 结果的代码
private void letters_Bind()
{
DataTable dtL;
if (Session[this.ToString() + "_LettersData"] == null)
{
string[] letters = { "A", "B", "C", "D",
"E", "F", "G", "H",
"I", "J", "K",
"L", "M", "N", "O",
"P", "Q", "R", "S",
"T", "U", "V",
"W", "X", "Y", "Z", "All"};
//create a new data table
dtL = new DataTable();
//create the scheme of the table
dtL.Columns.Add(new DataColumn("Letter", typeof(string)));
// Populate the data table with the letter data
for (int i = 0; i < letters.Length; i++)
{
DataRow dr = dtL.NewRow();
dr[0] = letters[i];
dtL.Rows.Add(dr);
}
//store a reference to the alphabet table for use on postback
Session[this.ToString() + "_LettersData"] = dtL;
}
else
{
dtL = (DataTable)Session[this.ToString() + "_LettersData"];
}
//bind to the grid
rptLetters.DataSource = dtL.DefaultView;
rptLetters.DataBind();
}
protected void rptLetters_ItemCommand(Object Sender, RepeaterCommandEventArgs e)
{
lblEntryStatus.Text = "Filtered by Last Name '" +
e.CommandArgument.ToString() + "' .";
_letterFilter = e.CommandArgument.ToString();
ViewState[this.ToString() + "_LetterFilter"] = _letterFilter;
FillPhysicianInGrid(false);
}
这是文件导入的代码
protected void btnImport_OnClick(object obj, System.EventArgs args)
{
lblFileName.Text = "";
string sInput = PhysicianFile.PostedFile.FileName;
sInput.Trim();
if (sInput != "")
{
try
{
DateTime dt = new DateTime();
dt = DateTime.Now;
string sExt = PhysicianFile.PostedFile.FileName.Substring(
PhysicianFile.PostedFile.FileName.LastIndexOf("."));
string sFileName = "PhysicianFile" +
sessionUtil.sessionGet("hospitalID").ToString() + sExt;
sFileName = sFileName.Replace("/", "-");
sFileName = sFileName.Replace(":", "-");
string sPath = "\\\\" +
System.Configuration.ConfigurationManager.AppSettings["DBSERVER"] +
"\\import\\hospData\\" + sFileName;
int nLength = PhysicianFile.PostedFile.ContentLength;
sExt = sExt.ToLower();
if (nLength < 999999999)
{
PhysicianFile.PostedFile.SaveAs(sPath);
dbPhysician physician = new dbPhysician();
string message = physician.ImportPhysician(sFileName, sPath);
if (message != "")
{
lblEntryStatus.Text =
"Error during file Load: " + message;
}
else
{
lblEntryStatus.Text = "File loaded successfully";
}
FillPhysicianInGrid(true);
lblFileName.Text = PhysicianFile.PostedFile.FileName;
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
}
以下是处理插入、更新和删除医生的数据库调用的 dbPhysician.cs 代码
public void Insert(string FName, string MName, string LName,
string HPhysicianID, string Suffix)
{
// Write your own Insert statement blocks
SqlParameter[] parameters =
{
new SqlParameter("@HospitalID", SqlDbType.Int),
new SqlParameter("@HospitalPhysicianID", SqlDbType.VarChar, 50),
new SqlParameter("@FirstName", SqlDbType.VarChar, 50),
new SqlParameter("@MiddleName", SqlDbType.VarChar, 50),
new SqlParameter("@LastName", SqlDbType.VarChar, 50),
new SqlParameter("@Suffix", SqlDbType.VarChar, 10),
};
int i = 0;
parameters[i++].Value = sessionUtil.sessionGet("hospitalID");
parameters[i++].Value = HPhysicianID;
parameters[i++].Value = FName;
parameters[i++].Value = MName;
parameters[i++].Value = LName;
parameters[i++].Value = Suffix;
int n;
RunProcedure("InsertPhysician", parameters, out n, false);
}
public DataTable Fetch()
{
SqlParameter[] parameters =
{
new SqlParameter("@HospitalID", SqlDbType.Int)
};
parameters[0].Value = sessionUtil.sessionGet("HospitalID");
DataTable dt = ((DataSet)(RunProcedure("getPhysicianList",
parameters, "temp"))).Tables[0];
return dt;
}
public void Update(int PhysicianID, string FName, string MName,
string LName, string HPhysicianID, string Suffix)
{
// Write your own Update statement blocks.
// Write your own Insert statement blocks
SqlParameter[] parameters =
{
new SqlParameter("@PhysicianID", SqlDbType.Int),
new SqlParameter("@HospitalPhysicianID", SqlDbType.VarChar, 50),
new SqlParameter("@FirstName", SqlDbType.VarChar, 50),
new SqlParameter("@MiddleName", SqlDbType.VarChar, 50),
new SqlParameter("@LastName", SqlDbType.VarChar, 50),
new SqlParameter("@Suffix", SqlDbType.VarChar, 10),
};
int i = 0;
parameters[i++].Value = PhysicianID;
parameters[i++].Value = HPhysicianID;
parameters[i++].Value = FName;
parameters[i++].Value = MName;
parameters[i++].Value = LName;
parameters[i++].Value = Suffix;
int n;
RunProcedure("UpdatePhysician", parameters, out n, false);
}
public void Delete(int PhysicianID)
{
// Write your own Insert statement blocks
SqlParameter[] parameters =
{
new SqlParameter("@PhysicianID", SqlDbType.Int),
};
int i = 0;
parameters[i++].Value = PhysicianID;
int n;
RunProcedure("DeletePhysician", parameters, out n, false); ;
}
以下代码通过检查文件中是否缺少必需字段并显示错误（如果任何字段为空）来处理文件导入。我还添加了一个检查，以查看用户输入的任何医生 ID 是否已存在于表中。（我们需要此字段在医院之间是唯一的。）
public int IsHospitalPhysicianID(string HospitalPhysicianID)
{
SqlParameter[] parameters =
{
new SqlParameter("@HospitalPhysicianID", SqlDbType.Int, 4),
new SqlParameter("@HospitalID", SqlDbType.Int, 4),
};
int i = 0;
parameters[i++].Value = HospitalPhysicianID;
parameters[i++].Value = sessionUtil.sessionGet("hospitalID");
DataSet ds = RunProcedure("IsHospitalPhysician", parameters, "temp");
if (ds.Tables[0].Rows.Count > 0)
{
return Parsers.intParse(ds.Tables[0].Rows[0]["IsExisting"].ToString(), -1);
}
return 0;
}
public string ImportPhysician(string ImportFileName, string ImportPath)
{
int result = 0;
string message = "";
OleDbConnection Importconn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " +
"Data Source=" +
System.IO.Path.GetDirectoryName(ImportPath + ImportFileName) +
";" + "Extended Properties=\"text;HDR=No;FMT=Delimited\"");
//Read all data into a data table
OleDbCommand oCmd = new OleDbCommand("SELECT " +
sessionUtil.sessionGet("HospitalID") +
" as HospitalID, * FROM [" + ImportFileName + "]", Importconn);
Importconn.Open();
OleDbDataReader oDR = oCmd.ExecuteReader();
while (oDR.Read())
{
if (oDR.IsDBNull(1))
{
result = -1;
message = "Physician ID(s) is/are missing in the file";
break;
}
if (oDR.IsDBNull(4))
{
result = -1;
message = "Last Names(s) is/are missing in the file";
break;
}
if (IsHospitalPhysicianID(oDR[1].ToString()) == 1)
{
result = -1;
message = "Physician ID(s) already exist in the Physician Table. " +
"Duplicates are not allowed.";
break;
}
}
if (result < 0)
{
Importconn.Close();
Importconn = null;
return message;
}
oDR.Close();
oDR = oCmd.ExecuteReader();
if ((oDR.FieldCount == 6))
{
// create the SqlBulkCopy object
using (SqlBulkCopy sBC = new SqlBulkCopy(Connection.ConnectionString,
SqlBulkCopyOptions.CheckConstraints))
{
// set the destination table name
sBC.DestinationTableName = "tPhysician";
sBC.ColumnMappings.Add(0, 1);
sBC.ColumnMappings.Add(1, 2);
sBC.ColumnMappings.Add(2, 3);
sBC.ColumnMappings.Add(3, 4);
sBC.ColumnMappings.Add(4, 5);
sBC.ColumnMappings.Add(5, 6);
// Write from the source to the destination.
try
{
// write the data
sBC.WriteToServer(oDR);
}
catch (Exception ex)
{
result = -1;
message = ex.Message;
}
finally
{
// Close the SqlDataReader. The SqlBulkCopy
// object is automatically closed at the end
// of the using block.
oDR.Close();
oDR = null;
}
}
Importconn.Close();
Importconn = null;
}
else
{
Importconn.Close();
Importconn = null;
return message;
}
return message;
}
历史
- 编译于 2009 年 6 月 8 日。