如何使用 C# 更新 SQL Server 数据库 ASP.NET 中的配置文件
本文关键字：NET ASP 配置文件 数据库 Server 何使用 更新 SQL | 更新日期: 2023-09-27 18:36:32
我浏览了一些代码，用于更新使用 Access 数据库的配置文件详细信息。但是，对于我当前的程序，我目前正在使用 SQL Server 数据库。
因此，我正在尝试调整这些代码以使其适用于 SQL Server 数据库。
编程按钮的目的是允许程序更新已登录用户的用户配置文件详细信息。
但是，我无法这样做，因为单击按钮后，我无法在数据库中输入信息。
引发的异常：
Error 15 Argument missing LINE 41
这是我的代码隐藏文件：
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;
public partial class RealClientEditProfileaspx : System.Web.UI.Page
{
public string UFlag = "F"; public string strUserId;
static readonly string scriptSuccessUpdate = "<script language ='"javascript'">'n" +
"alert ('"Update Successful - Please surf to other pages to shop'");'n </script>";
protected void Page_Load(object sender, EventArgs e)
{
firstnameLabel.Text = Session["cFirstName"].ToString();
lastnameLabel.Text = Session["cLastName"].ToString();
dobLabel.Text = Session["cDOB"].ToString();
companyLabel.Text = Session["cCompanyName"].ToString();
addressLabel.Text = Session["cAddress"].ToString();
zipcodeLabel.Text = Session["cZipCode"].ToString();
phonenumberLabel.Text = Session["cPhoneNo"].ToString();
faxnumberLabel.Text = Session["cfax"].ToString();
emailLabel.Text = Session["cEmail"].ToString();
passwordLabel.Text = Session["cPassword"].ToString();
foreach (char ch in passwordLabel.Text.ToCharArray())
{
realPasswordLabel.Text += "*";
}
}
public void UpdateCustomer(string strFName, string strFValue)
{
SqlConnection conn = new SqlConnection(@"Data Source=(LocalDB)'v11.0;AttachDbFilename=D:'Desktop'TemporarySter'App_Data'legitdatabase.mdf;Integrated Security=True;Connect Timeout=30;MultipleActiveResultSets=true");
conn.Open();
Type csType = this.GetType();
SqlCommand com;
SqlDataReader rdr;
String strSQL = "UPDATE Client SET " + strFName + " = @newValue WHERE ClientNo = @ClientNo ";
com = new SqlCommand(strSQL, conn);
com.Parameters.Add("@newValue", ).Value = strFValue;
com.ExecuteNonQuery();
UFlag = "T";
conn.Close();
string insertQuery = "UPDATE Client (cFirstName, cLastName, cDOB, cCompanyName, cAddress, cZipCode, cPhoneNo, cFax, cEmail, cUsername, cPassword) values (@firstname,@lastname,@dob,@companyname,@address,@zipcode,@phoneno,@fax,@email,@password)";
com = new SqlCommand(insertQuery, conn);
}
protected void Button1_Click(object sender, EventArgs e)
{
strUserId = (string)Session["sUserId"];
if (firstnameTB.Text != "")
{
String strFName = "cFirstName"; String strFValue = firstnameTB.Text;
UpdateCustomer(strFName, strFValue);
}
if (lastnameTB.Text != "")
{
String strFName = "cLastName"; String strFValue = lastnameTB.Text;
UpdateCustomer(strFName, strFValue);
}
if (dobTB.Text != "")
{
String strFName = "cDOB"; String strFValue = dobTB.Text;
UpdateCustomer(strFName, strFValue);
}
if (addressTB.Text != "")
{
String strFName = "cAddress"; String strFValue = addressTB.Text;
UpdateCustomer(strFName, strFValue);
}
if (zipcodeTB.Text != "")
{
String strFName = "cZipCode"; String strFValue = zipcodeTB.Text;
UpdateCustomer(strFName, strFValue);
}
if (phonenumberTB.Text != "")
{
String strFName = "cPhoneNo"; String strFValue = phonenumberTB.Text;
UpdateCustomer(strFName, strFValue);
}
if (faxnumberTB.Text != "")
{
String strFName = "cFax"; String strFValue = faxnumberTB.Text;
UpdateCustomer(strFName, strFValue);
}
if (emailTB.Text != "")
{
String strFName = "cEmail"; String strFValue = emailTB.Text;
UpdateCustomer(strFName, strFValue);
}
if (passwordTB.Text != "")
{
String strFName = "cPassword"; String strFValue = passwordTB.Text;
UpdateCustomer(strFName, strFValue);
}
if (UFlag == "T")
{
Type strType = this.GetType();
ClientScript.RegisterStartupScript(strType, "Success", scriptSuccessUpdate);
}
}
}
下面是 ASPX 标记：
<%@ Page Title="" Language="C#" MasterPageFile="~/ClientPortal.master" AutoEventWireup="true" CodeFile="RealClientEditProfileaspx.aspx.cs" Inherits="RealClientEditProfileaspx" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
<style type="text/css">
.style1
{
font-size: 1em;
}
.style4
{
width: 208px;
}
.style8
{
color: #FF9933;
}
.auto-style8 {
width: 121px;
text-align: right;
}
.auto-style9 {
width: 227px;
text-align: left;
}
.auto-style10 {
text-align: left;
}
</style>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<br />
<table class="auto-style2">
<tr>
<td class="auto-style8">
<asp:Label ID="Label1" runat="server" Text="Particulars"></asp:Label>
</td>
<td class="auto-style9">
<asp:Label ID="Label12" runat="server" style="text-align: left" Text="Current particulars"></asp:Label>
</td>
<td class="auto-style10">
<asp:Label ID="Label23" runat="server" Text="Updated particulars"></asp:Label>
</td>
</tr>
<tr>
<td class="auto-style8">First Name:</td>
<td class="auto-style9">
<asp:Label ID="firstnameLabel" runat="server" Text="Label"></asp:Label>
</td>
<td class="auto-style10">
<asp:TextBox ID="firstnameTB" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td class="auto-style8">
<asp:Label ID="Label3" runat="server" Text="Last Name:"></asp:Label>
</td>
<td class="auto-style9">
<asp:Label ID="lastnameLabel" runat="server" Text="Label"></asp:Label>
</td>
<td class="auto-style10">
<asp:TextBox ID="lastnameTB" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td class="auto-style8">
<asp:Label ID="Label4" runat="server" Text="Date Of Birth:"></asp:Label>
</td>
<td class="auto-style9">
<asp:Label ID="dobLabel" runat="server" Text="Label"></asp:Label>
</td>
<td class="auto-style10">
<asp:TextBox ID="dobTB" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td class="auto-style8">
<asp:Label ID="Label5" runat="server" Text="Company Name:"></asp:Label>
</td>
<td class="auto-style9">
<asp:Label ID="companyLabel" runat="server" Text="Label"></asp:Label>
</td>
<td class="auto-style10">
<asp:TextBox ID="companyTB" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td class="auto-style8">
<asp:Label ID="Label6" runat="server" Text="Address:"></asp:Label>
</td>
<td class="auto-style9">
<asp:Label ID="addressLabel" runat="server" Text="Label"></asp:Label>
</td>
<td class="auto-style10">
<asp:TextBox ID="addressTB" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td class="auto-style8">
<asp:Label ID="Label7" runat="server" Text="Zip Code:"></asp:Label>
</td>
<td class="auto-style9">
<asp:Label ID="zipcodeLabel" runat="server" Text="Label"></asp:Label>
</td>
<td class="auto-style10">
<asp:TextBox ID="zipcodeTB" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td class="auto-style8">
<asp:Label ID="Label8" runat="server" Text="Phone Number:"></asp:Label>
</td>
<td class="auto-style9">
<asp:Label ID="phonenumberLabel" runat="server" Text="Label"></asp:Label>
</td>
<td class="auto-style10">
<asp:TextBox ID="phonenumberTB" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td class="auto-style8">
<asp:Label ID="Label9" runat="server" Text="Fax Number:"></asp:Label>
</td>
<td class="auto-style9">
<asp:Label ID="faxnumberLabel" runat="server" Text="Label"></asp:Label>
</td>
<td class="auto-style10">
<asp:TextBox ID="faxnumberTB" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td class="auto-style8">
<asp:Label ID="Label10" runat="server" Text="Email:"></asp:Label>
</td>
<td class="auto-style9">
<asp:Label ID="emailLabel" runat="server" Text="Label"></asp:Label>
</td>
<td class="auto-style10">
<asp:TextBox ID="emailTB" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td class="auto-style8">
<asp:Label ID="Label11" runat="server" Text="Password:"></asp:Label>
</td>
<td class="auto-style9">
<asp:Label ID="realPasswordLabel" runat="server" Text="********"></asp:Label>
</td>
<td class="auto-style10">
<asp:TextBox ID="passwordTB" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td class="auto-style8"> </td>
<td class="auto-style9">
<asp:TextBox ID="passwordLabel" runat="server" ReadOnly="True" TextMode="Password" Visible="False"></asp:TextBox>
</td>
<td class="auto-style10">
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" style="text-align: right" Text="Update" />
</td>
</tr>
</table>
</asp:Content>
实际修复：
必须为 @CustomerNo 参数提供一个值。在 Button1_Click 方法中，你有一个名为 strUserId 的变量，它应该传递给 UpdateCustomer() ：
UpdateCustomer(strUserId, strFName, strFValue);
然后在UpdateCustomer：
public void UpdateCustomer(string strUserId, string strFName, string strFValue)
{
...
com.Parameters.Add("@ClientNo", ).Value = strUserId;
com.Parameters.Add("@newValue", ).Value = strFValue;
然后删除INSERT逻辑，因为这不属于"更新客户"的方法。
建议的改进
真的，这可以使用一点面向对象的优点。我会在 C# 中创建一个类来表示客户：
public class User
{
public long Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public DateTime DateOfBirth { get; set; }
public string CompanyName { get; set; }
public string Address { get; set; }
public string ZipCode { get; set; }
public string PhoneNumber { get; set; }
public string FaxNumber { get; set; }
public string Email { get; set; }
public string Password { get; set; }
}
这表示您的域模型。接下来，您希望在用户控件中使用它。
首先，Button1_Click方法变为：
protected void Button1_Click(object sender, EventArgs e)
{
User customer = new User()
{
Id = long.Parse((string)Session["sUserId"]),
FirstName = firstnameTB.Text.Trim(),
LastName = lastnameTB.Text.Trim(),
DateOfBirth = DateTime.Parse(dobTB.Text.Trim()),
Address = addressTB.Text.Trim(),
ZipCode = zipcodeTB.Text.Trim(),
PhoneNumber = phonenumberTB.Text.Trim(),
FaxNumber = faxnumberTB.Text.Trim(),
Email = emailTB.Text.Trim(),
Password = passwordTB.Text.Trim()
};
UpdateCustomer(customer);
if (UFlag == "T")
{
Type strType = this.GetType();
ClientScript.RegisterStartupScript(strType, "Success", scriptSuccessUpdate);
}
}
这读起来更干净，更易于维护。对UpdateCustomer的调用确实只会更新客户。现在让我们看一下对UpdateCustomer方法的更改：
public void UpdateCustomer(User customer)
{
string updateSql =
@"UPDATE Client
SET cFirstName = @FirstName,
cLastName = @LastName,
cDOB = @DateOfBirth,
cAddress = @Address,
cZipCode = @ZipCode,
cPhoneNo = @PhoneNumber,
cFax = @FaxNumber,
cEmail = @Email,
cPassword = @Password
WHERE ClientNo = @Id";
using (var connection = new SqlConnection(@"..."))
{
connection.Open();
var command = new SqlCommand(updateSql, connection);
var args = command.Parameters;
args.Add("@FirstName", customer.FirstName);
args.Add("@LastName", customer.LastName);
args.Add("@DateOfBirth", customer.DateOfBirth);
args.Add("@Address", customer.Address);
args.Add("@ZipCode", customer.ZipCode);
args.Add("@PhoneNumber", customer.PhoneNumber);
args.Add("@FaxNumber", customer.FaxNumber);
args.Add("@Email", customer.Email);
args.Add("@Password", customer.Password);
args.Add("@Id", customer.Id);
command.ExecuteNonQuery();
}
UFlag = "T";
}
SQL 允许您在一个命令中更新所有列，而不是为每个表单字段发出 UPDATE。一次只更新一个字段会不必要地导致数据库出现更多流量。
当用户首次访问页面并填充表单字段时，应从数据库中填充所有字段。
补充阅读
- 域模型 领域
- 驱动设计及其赞美，制作邪恶的领域模型
- 存储库模式 - 用于组织对数据库执行 CRUD 操作的代码。