使用ASP.Net和MySQL执行插入、更新、搜索和删除（CRUD操作）
4.87/5 (21投票s)
在ASP.Net中使用MySQL数据库执行创建、读取、更新和删除操作，采用N层架构
引言
本文旨在帮助理解ASP.Net中使用MySQL作为数据库的插入、更新、搜索和删除操作。许多新手在这些基本操作上遇到问题。我将使用存储过程来执行所有数据库操作。这里我们将以简单的国家示例来帮助理解。此操作对于大多数应用程序都类似。有许多ORM可以执行类似的任务。但我不会讲解ORM的内容。请查看使用GUI工具备份/还原MySQL数据库。您还需要根据您的数据库设置更改Web.Config中的ConnectionString。
背景
- Visual Studio / Visual Web Developer（最好是最新版本）
- Dot Net Framework（最好是最新版本）
- MySQL + MySQL GUI工具（如果您有MySQL GUI工具会更容易）
- MySQL .NET连接器
- 您应该具备SQL、C#和ASP.NET的基本知识
使用代码
希望您已经具备数据库知识。创建表、存储过程和编写查询。让我们开始创建Country表和相关的存储过程。
创建Country Master数据库表CREATE TABLE `tbl_countrymaster` (`NM_COUNTRY_ID` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'UNIQUE NO TO IDENTIFY COUNTRY',`VC_COUNTRY_NAME` varchar(45) NOT NULL COMMENT 'NAME OF THE COUNTRY',PRIMARY KEY (`NM_COUNTRY_ID`));
创建Error Log数据库表
CREATE TABLE `tbl_error_log` (`dt_error` datetime NOT NULL,`vc_error` longtext,`vc_error_stack` longtext NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
添加Country的存储过程
CREATE PROCEDURE `InsertCountry`(
IN _CountryName varchar(200),
OUT _ReturnValue int
)
BEGIN
DECLARE IsCountryExist int(5);
SELECT COUNT(NM_COUNTRY_ID) INTO IsCountryExist FROM
TBL_COUNTRYMASTER WHERE VC_COUNTRY_NAME = _CountryName;
START TRANSACTION;
IF IsCountryExist = 0 THEN
INSERT INTO TBL_COUNTRYMASTER(VC_COUNTRY_NAME)
VALUES (_CountryName);
END IF;
SELECT LAST_INSERT_ID() INTO _ReturnValue;
COMMIT;
END 获取所有Country的存储过程
CREATE PROCEDURE `getAllCountry`()
BEGIN
SELECT NM_COUNTRY_ID,
VC_COUNTRY_NAME
FROM
TBL_COUNTRYMASTER
ORDER BY
VC_COUNTRY_NAME;
END 删除Country的存储过程
CREATE PROCEDURE `DeleteCountry`(
IN _CountryID int,
OUT _ReturnValue int
)
BEGIN
Declare referenceCount int;
SELECT COUNT(NM_STATE_ID) INTO referenceCount
FROM
TBL_STATEMASTER
WHERE NM_COUNTRY_ID = _CountryID;
IF referenceCount=0 THEN
DELETE FROM TBL_COUNTRYMASTER
WHERE NM_COUNTRY_ID = _CountryID;
set _ReturnValue=1;
ELSE
set _ReturnValue=-2;
END IF;
END
按名称选择Country的存储过程
CREATE PROCEDURE `SelectCountryByName`(
IN _CountryName varchar(200)
)
BEGIN
SELECT NM_COUNTRY_ID,
VC_COUNTRY_NAME
FROM
TBL_COUNTRYMASTER
WHERE
VC_COUNTRY_NAME like CONCAT('%',_CountryName,'%')
ORDER BY
VC_COUNTRY_NAME;
END
更新Country名称的存储过程
CREATE PROCEDURE `UpdateCountryName`(
IN _CountryName varchar(200),
IN _CountryID int
)
BEGIN
UPDATE TBL_COUNTRYMASTER SET VC_COUNTRY_NAME = _CountryName WHERE NM_COUNTRY_ID = _CountryID;
END
插入Log的存储过程
CREATE PROCEDURE `InsertLog`(
IN _Datetime datetime,
IN _ErrorMessage varchar(1000),
IN _ErrorStack varchar(5000),
OUT _ReturnValue int
)
BEGIN
INSERT INTO TBL_ERROR_LOG(dt_error, vc_error, vc_error_stack)
VALUES (_Datetime,_ErrorMessage,_ErrorStack);
SET _ReturnValue = 0;
END
“C”代表创建
开始.NET部分
现在数据库已经准备好执行所有操作。您可以创建一个简单的Web应用程序，而无需代码分离，例如数据访问层或业务逻辑层。但在这里我将遵循3层架构。这将帮助您理解数据在3层架构中的流动。您也可以称之为N层架构。在这里我们可以添加更多的层用于日志记录或许可。但本文不会涉及许可部分。我们将概述Noggin。让我们开始创建项目，例如**UI**（用户界面，ASP.NET应用程序）、**DAL**（数据访问层，主要包含SQLHelper）、**BAL**（业务逻辑层）、**PROP**（属性）以及最后的**Logging**。我倾向于这种结构和命名约定。您也可以为您的项目使用其他方式/命名约定。DAL、BAL、PROP和Logging是类项目。我们还可以创建更多属性层。我们可以使用该类在层之间传递信息。
PROP：Country属性列表public class PROPCountry
{
public int CountryID { get; set; }
public string CountryName { get; set; }
public PROPCountry()
{
}
public PROPCountry(int countryID, string countryName)
{
this.CountryID = countryID;
this.CountryName = countryName;
}
}
以上类可用于各层之间的通信。当我们需要传递某个类的所有信息时，我们通常将其作为对象传递，并填充所有属性。
创建用户界面
这里我们不讨论创建有吸引力的用户界面的开发。我们将看到如何在ASP.NET中执行CRUD操作。所以请原谅我不好的UI。Code project中有许多文章可以改进用户界面。UI页面：CountryMaster.aspx
<div>Create Country</div>
<span style="height:20px; vertical-align:top">Country Name :</span>
<asp:TextBox ID="txtCountryName" runat="server"></asp:TextBox>
<asp:Button ID="btnCreate" runat="server" Text="Create" onclick="btnCreate_Click" />
我们将获取txtCountryName的信息并将其传递给业务逻辑层以创建Country。
后台代码：CountryMaster.aspx.cs btnCreate Click事件
protected void btnCreate_Click(object sender, EventArgs e)
{
BALCountry balCountry = new BALCountry();
try
{
throw new ArgumentException();
int returnValue = balCountry.CreateCountry(txtCountryName.Text);
if (returnValue > 0)
{
Page.RegisterClientScriptBlock("message", "<script>alert('Country is created successfully')</script>");
txtCountryName.Text = string.Empty;
}
else
{
Page.RegisterClientScriptBlock("message", "<script>alert('Incorrect User Inputs.')</script>");
} }
catch (Exception ex)
{
clsLogging logError = new clsLogging();
logError.WriteLog(ex);
}
}
BAL：BALCountry.cs CreateCountry函数
public int CreateCountry(string countryName)
{
if (string.IsNullOrEmpty(countryName))
{
return -1;
}
else
{
DALCountry dalCountry = new DALCountry();
return dalCountry.CreateCountry(countryName);
}
} DAL：DALCountry.cs CreateCountry函数
public int CreateCountry(string countryName)
{
SQLHelper sqlHelper = new SQLHelper();
List<MySqlParameter> parameters = new List<MySqlParameter>();
parameters.Add(new MySqlParameter("_CountryName", countryName));
return sqlHelper.executeSP<int>(parameters, "InsertCountry");
}
正如您在上面的代码中看到的，按钮单击事件将CountryName作为参数调用BAL函数，然后BAL将执行一些验证。如果验证正确，它将调用DAL函数并通过我们的存储过程插入数据。
希望您对3层架构的工作原理有清晰的认识。以上所有代码将完成Country的创建操作。我们还没有涉及日志记录代码。让我们看看日志记录。
Logging：clsLogging.cs WriteLog函数
public void WriteLog(Exception ex)
{
SQLHelper sqlHelper = new SQLHelper();
List<MySqlParameter> parameters = new List<MySqlParameter>();
parameters.Add(new MySqlParameter("_DateTime", DateTime.Now));
parameters.Add(new MySqlParameter("_ErrorMessage", ex.Message));
parameters.Add(new MySqlParameter("_ErrorStack", ex.StackTrace));
sqlHelper.executeSP<int>(parameters, "InsertLog");
}
以上所有代码为您提供了使用N层架构执行创建（插入）数据库操作的基本概念。对于读取、更新和删除等所有其他操作，我们将逐步查看代码。对于所有后续主题，我们将不再详细讨论所有基本步骤，而是通过查看代码快速浏览。
“R”代表读取
用户界面
<div style="font-weight:bold">Search Country</div>
<span style="height:20px; vertical-align:top">Country Name :</span>
<asp:TextBox ID="txtSearchName" runat="server"></asp:TextBox>
<asp:Button ID="btnSearch" runat="server" Text="Search" onclick="btnSearch_Click" />
<asp:GridView ID="gvCountryList" runat="server">
</asp:GridView> 用于搜索按钮单击事件的后台代码文件以及所有其他相关代码
protected void btnSearch_Click(object sender, EventArgs e)
{
binding(txtSearchName.Text);
}
private void binding(string searchCountry)
{
try
{
BALCountry balCountry = new BALCountry();
gvCountryList.DataSource = balCountry.getCoutry(searchCountry);
gvCountryList.DataBind();
}
catch (Exception ex)
{
clsLogging logError = new clsLogging();
logError.WriteLog(ex);
}
}
正如您在上面的代码中看到的，我们使用了binding函数将我们的数据网格绑定以显示搜索结果。我们还实现了搜索参数，以便用户可以搜索相关的国家名称。binding函数是为了多用途而创建的。它可以用于获取特定的搜索结果，也可以通过传递null参数来获取所有国家列表。
让我们在BAL和DAL中创建getCountry函数
BAL函数
public List<PROPCountry> getCoutry(string searchCountry)
{
DALCountry dalCountry = new DALCountry();
if (string.IsNullOrEmpty(searchCountry))
{
return dalCountry.getAllCountry();
}
else
{
return dalCountry.getCountry(searchCountry);
}
}在上面的代码中，我们获得了List<PROPCountry>，它可以直接绑定到数据网格。在这里您可以看到，如果我们有null的searchCountry变量，它将调用getAllCountry函数，否则它将调用getCountry函数进行搜索。现在我们将看到如何在DAL层实现上述两个描述的函数。
DAL函数
public List<PROPCountry> getAllCountry()
{
List<PROPCountry> countryList = new List<PROPCountry>();
SQLHelper sqlHelper = new SQLHelper();
List<MySqlParameter> parameters = new List<MySqlParameter>();
var resultSet = sqlHelper.executeSP<DataSet>(parameters, "getAllCountry");
PROPCountry country;
foreach (DataRow drow in resultSet.Tables[0].Rows)
{
country = new PROPCountry(Convert.ToInt32(drow[0].ToString()) , drow[1].ToString());
countryList.Add(country);
}
return countryList;
}
public List<PROPCountry> getCountry(string searchCountry)
{
List<PROPCountry> countryList = new List<PROPCountry>();
SQLHelper sqlHelper = new SQLHelper();
List<MySqlParameter> parameters = new List<MySqlParameter>();
parameters.Add(new MySqlParameter("_CountryName", searchCountry));
var resultSet = sqlHelper.executeSP<DataSet>(parameters, "SelectCountryByName");
PROPCountry country;
foreach (DataRow drow in resultSet.Tables[0].Rows)
{
country = new PROPCountry(Convert.ToInt32(drow[0].ToString()), drow[1].ToString());
countryList.Add(country);
}
return countryList;
} 正如您在上述两个方法中看到的，唯一的区别是存储过程的名称和我们传递的参数。我们也可以将这两个存储过程合并起来以用于不同的函数。但在这里为了便于理解，我们在C#端进行了这些更改。我们还使用了PROPCountry的构造函数来赋值CountryId和CountryName属性。在这两个函数中，我们都遍历结果并生成List<PROPCountry>列表并将其传递给BAL层。
这就是我们如何执行数据库的读取操作。我们已经在一个功能中涵盖了普通数据读取以及搜索机制。
“U”代表更新/编辑
更新操作是最关键的操作。如果我们操作不当，会导致数据库结果不正确。我们需要选择正确的更新查询来修改特定数据。Country示例非常简单，不会像我刚才解释的那样复杂，但当涉及到带有子项的账单和更新子项时，会变得更加复杂。本文将不涵盖这些复杂的内容。本文的范围仅在于理解基本功能如何执行。
用户界面
<div style="font-weight:bold">Edit Country</div>
<span style="height:20px; vertical-align:top">Country ID :</span>
<asp:DropDownList ID="ddlCountry" runat="server" AutoPostBack="True" onselectedindexchanged="ddlCountry_SelectedIndexChanged">
</asp:DropDownList>
<br />
<span style="height:20px; vertical-align:top">Country Name :</span>
<asp:TextBox ID="txtEditCountryName" runat="server"></asp:TextBox>
<asp:Button ID="btnEditCountryName" runat="server" Text="UpdateCountry" onclick="btnEditCountryName_Click" /> 我们使用下拉列表来选择要更新的特定记录。我们也可以在GridView中添加更新链接并执行类似的操作。为了简单起见，当我们更改DropDownList时，我们将记录置于编辑模式。一旦点击btnEditCountryName，Country将被更新。正如您所看到的，我们已将AutoPostBack="True"更改为onSelectedIndexChanged工作，否则事件不会触发。
用于DropDownList和Button事件的后台代码
protected void ddlCountry_SelectedIndexChanged(object sender, EventArgs e)
{
string countryName = string.Empty;
BALCountry balCountry = new BALCountry();
try
{
countryName = balCountry.getCountryByID(ddlCountry.SelectedValue);
if (string.IsNullOrEmpty(countryName))
{
Page.RegisterClientScriptBlock("message", "<script>alert('Country Id is not found.')</script>");
}
else
{
txtEditCountryName.Text = countryName;
}
}
catch (Exception ex)
{
clsLogging logError = new clsLogging();
logError.WriteLog(ex);
}
}
当用户更改DropDownList时，它将从数据库获取特定的Country记录并填充txtEditCountryName文本框。我们可以直接从DropDownList获取，但为了理解，我们这样做了。这里我们只有一个值需要设置，但当有客户信息时，我们需要从数据库获取客户类及其所有属性。因此，上述功能将为您提供如何从CountryID
protected void btnEditCountryName_Click(object sender, EventArgs e)
{
try
{
PROP.PROPCountry country = new PROP.PROPCountry();
country.CountryID = Convert.ToInt16(ddlCountry.SelectedValue);
country.CountryName = txtEditCountryName.Text;
BALCountry balCountry = new BALCountry();
bool result = balCountry.updateCountry(country);
if (!result)
{
Page.RegisterClientScriptBlock("message", "<script>alert('Invalid Inputs for update.')</script>");
}
else
{
Page.RegisterClientScriptBlock("message", "<script>alert('Country is updated successfully.')</script>");
binding(null);
ddlCountry.SelectedIndex = 0;
txtEditCountryName.Text = string.Empty;
}
}
catch (Exception ex)
{
clsLogging logError = new clsLogging();
logError.WriteLog(ex);
}
}
当用户修改txtEditCountryName文本框中的信息并点击Edit按钮时，它将更新数据库中的信息。我们在BAL中进行了一些验证，以防止CountryName为空。如果传递了空的CountryName，updateCountry将返回false。
用于更新操作的BAL函数
public string getCountryByID(string stringCountryID)
{
int countryID;
DALCountry dalCountry = new DALCountry();
int.TryParse(stringCountryID, out countryID);
if (countryID == 0)
{
return string.Empty;
}
else
{
return dalCountry.GetCountryById(countryID);
}
}
public bool updateCountry(PROPCountry country)
{
if (string.IsNullOrEmpty(country.CountryName) || country.CountryID <= 0)
{
return false;
}
else
{
DALCountry dalCountry = new DALCountry();
dalCountry.UpdateCountry(country);
return true;
}
} getCountryById根据CountryID获取Country的名称。updateCountry将执行验证并将经过筛选的数据传递给DAL进行进一步的更新操作。
用于调用存储过程的DAL函数
public string GetCountryById(int countryID)
{
SQLHelper sqlHelper = new SQLHelper();
List<MySqlParameter> lstParameter = new List<MySqlParameter>();
lstParameter.Add(new MySqlParameter("_CountryID", countryID));
return sqlHelper.executeScaler(lstParameter, "SelectCountryByID");
}
public void UpdateCountry(PROPCountry country)
{
SQLHelper sqlHelper = new SQLHelper();
List<MySqlParameter> lstParameter = new List<MySqlParameter>();
lstParameter.Add(new MySqlParameter("_CountryID", country.CountryID));
lstParameter.Add(new MySqlParameter("_CountryName", country.CountryName));
sqlHelper.executenonquery(lstParameter, "UpdateCountryName");
} 在这里，DAL的UpdateCountry函数用于在数据库中更新给定的Country。我们也可以从存储过程进行验证。我们还可以添加检查数据库是否已更新，并根据结果返回结果而不是void类型。希望所有其他代码对您来说看起来都非常相似。
“D”代表删除
删除操作应使用查询中的标识字段的WHERE子句进行。我们需要确保参与删除操作的字段是标识/GUID。删除操作与更新操作一样关键。我们在删除任何信息时都需要格外小心。在本例中，我们将从用户那里获取CountryID输入，并向用户提供删除确认。
用户界面
<div style="font-weight:bold">Delete Country By ID</div>
<span style="height:20px; vertical-align:top">Country ID :</span>
<asp:TextBox ID="txtDeleteID" runat="server"></asp:TextBox>
<asp:Button ID="btnDelete" runat="server" Text="Delete" onclick="btnDelete_Click" /> 在删除按钮单击事件中，我们可以编写删除代码并检查数据库中的状态。删除操作可以通过GridView中的删除链接或其他方式完成。
用于删除按钮单击事件的后台代码
protected void btnDelete_Click(object sender, EventArgs e)
{
BALCountry balCountry = new BALCountry();
try
{
int returnValue = balCountry.deleteCountry(txtDeleteID.Text);
if (returnValue == 0)
{
Page.RegisterClientScriptBlock("message", "<script>alert('Incorrect Country Id')</script>");
}
else if(returnValue == -2)
{
Page.RegisterClientScriptBlock("message", "<script>alert('Country ID could not found.')</script>");
}
else if (returnValue == 1)
{
Page.RegisterClientScriptBlock("message", "<script>alert('Country is deleted successfully.')</script>");
binding(null);
txtDeleteID.Text = string.Empty;
}
else
{
Page.RegisterClientScriptBlock("message", "<script>alert('Unspecified error.')</script>");
}
}
catch (Exception ex)
{
clsLogging logError = new clsLogging();
logError.WriteLog(ex);
}
}
正如您在上面的按钮单击事件中看到的。我们有不同的returnValue及其对应的消息。所有这些状态信息都来自存储过程或业务逻辑层。大多数验证应该在业务逻辑层或存储过程中完成。我们已将验证层分开，以便从一个地方识别所有验证。
让我们看看Delete操作的BAL实现
public int deleteCountry(string stringCountryID)
{
int countryID;
DALCountry dalCountry = new DALCountry();
int.TryParse(stringCountryID, out countryID);
if (countryID == 0)
{
return 0;
}
else
{
return dalCountry.DeleteCountry(countryID);
}
}
在这里，我们正在验证CountryId信息。我们需要验证其是否为int类型，因为用户可以从用户界面输入任何类型的信息。我们可以从用户界面进行数字验证，但黑客可以绕过它。因此，我们为删除操作采取了额外的预防措施。
用于删除操作的DAL实现
public int DeleteCountry(int countryID)
{
SQLHelper sqlHelper = new SQLHelper();
List<MySqlParameter> lstParameter = new List<MySqlParameter>();
lstParameter.Add(new MySqlParameter("_CountryID", countryID));
return sqlHelper.executeSP<int>(lstParameter, "DeleteCountry");
} DAL删除操作的实现与其他操作一样简单。
结语
以上就是CRUD操作和N层架构的全部内容。在上面的所有代码中，我为了您的理解而放置了小的函数。请查找附件中的代码和数据库脚本以正确执行。希望大家喜欢阅读这篇文章。这里有一些您可能想阅读的有趣的书籍，例如，