asp.net和数据库的交互太多,如果把语句都写在代码里面,看起来太长太乱。我更喜欢在数据库里面把操作过程维护成过程或者函数,在代码里面一调用就可以了。这可能是我刚入门编程的一种错误的看法`~`。
但是,我发现数据库并不能获取我需要给它传入的值,应该是我传参的语法不对。所以,将google到的东西整理至我代码里面去。参考的博客是——在c#中执行sql语句时传递参数的小经验 - lui - 博客园 https://www.cnblogs.com/lj821022/archive/2006/04/25/384906.html
1. 在数据库创建过程.
CREATE PROC sp_UserRegister
@strUserName VARCHAR(50),
@strPassWord VARCHAR(50),
@strRealName VARCHAR(20),
@strPhone VARCHAR(20),
@strAddress VARCHAR(100),
@strSex VARCHAR(2)
AS
/*******************************************
* 2010-10-25:创建,用于用户注册界面
*******************************************/
BEGIN
IF EXISTS (SELECT 1 FROM [User] AS u(NOLOCK) WHERE u.UserName = @strUserName )
BEGIN
RETURN -1
END
INSERT INTO [User]
(
UserName,
[Password],
Sex,
RealName,
TelePhone,
[Address]
)
VALUES
(
@strUserName,
@strPassWord,
@strSex,
@strRealName,
@strPhone,
@strAddress
)
RETURN 1
END
2.asp.net中代码调用(直接把参数值写入到数据库)
(1)代码:
string strUserName = this.txtUserName.Text;
string strPassWord = this.txtPassWord.Text;
string strRealName = this.txtRealName.Text;
string strPhone = this.txtPhone.Text;
string strAddress = this.txtAddress.Text;
string strSex = "";
if (rdoMale.Checked)
strSex = "男";
if (rdoFemale.Checked)
strSex = "女";
string strRegister = "exec sp_UserRegister '" + strUserName + "', '" + strPassWord + "', '" + strRealName + "', '" + strPhone + "', '" + strAddress + "', '" + strSex + "'";
int intReturn = DataBase.ExecuteSql(strRegister);
if (intReturn == -1)
{
System.Web.HttpContext.Current.Response.Write("<script>alert('该用户名已经存在');</script>");
}
else
{
System.Web.HttpContext.Current.Response.Write("<script>alert('注册成功。');</script>");
}
(2)运行代码跟踪数据库:
exec sp_UserRegister 'dd', 'dd', 'dogdog', 'dogdog', 'dogdog', '男'
3.以参数的形式给数据库传参。
string strUserName = this.txtUserName.Text;
string strPassWord = this.txtPassWord.Text;
string strRealName = this.txtRealName.Text;
string strPhone = this.txtPhone.Text;
string strAddress = this.txtAddress.Text;
string strSex = "";
if (rdoMale.Checked)
strSex = "男";
if (rdoFemale.Checked)
strSex = "女";
//string strRegister = "exec sp_UserRegister '" + strUserName + "', '" + strPassWord + "', '" + strRealName + "', '" + strPhone + "', '" + strAddress + "', '" + strSex + "'";
string strRegister = "exec sp_UserRegister @strUserName , @strPassWord , @strRealName , @strPhone , @strAddress , @strSex ";
SqlParameter[] SqlParameter = new SqlParameter[]
{
new SqlParameter("@strUserName",strUserName),
new SqlParameter("@strPassWord",strPassWord),
new SqlParameter("@strRealName",strRealName),
new SqlParameter("@strPhone",strPhone),
new SqlParameter("@strAddress",strAddress),
new SqlParameter("@strSex",strSex)
};
int intReturn = DataBase.ExecuteSql(strRegister, SqlParameter);
if (intReturn == -1)
{
System.Web.HttpContext.Current.Response.Write("<script>alert('该用户名已经存在');</script>");
}
else
{
System.Web.HttpContext.Current.Response.Write("<script>alert('注册成功。');</script>");
}
后台跟踪到的语句为
exec sp_executesql N'exec sp_UserRegister @strUserName , @strPassWord , @strRealName , @strPhone , @strAddress , @strSex ',N'@strUserName nvarchar(2),@strPassWord nvarchar(2),@strRealName nvarchar(6),@strPhone nvarchar(1),@strAddress nvarchar(1),@strSex nvarchar(1)',@strUserName=N'cc',@strPassWord=N'cc',@strRealName=N'pigpig',@strPhone=N'1',@strAddress=N'1',@strSex=N'男'
4. DataBase类如下
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
/// <summary>
/// Class1 的摘要说明
/// </summary>
public static class DataBase
{
private static SqlConnection connection;
public static SqlConnection Connection
{
get
{
string connectionString = ConfigurationManager.ConnectionStrings["YP"].ConnectionString;
if (connection == null || connection.State == ConnectionState.Closed)
{
connection = new SqlConnection(connectionString);
connection.Open();
}
return connection;
}
}
public static DataTable GetdataSet(string sql)
{
SqlCommand command = new SqlCommand(sql, Connection);
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(command);
da.Fill(ds);
return ds.Tables[0];
}
public static DataTable GetdataSet(string sql, SqlParameter[] sqlParameter)
{
SqlCommand command = new SqlCommand(sql, Connection);
foreach (SqlParameter parameter in sqlParameter)
{
command.Parameters.Add(parameter);
}
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(command);
da.Fill(ds);
return ds.Tables[0];
}
public static int ExecuteSql(string sql)
{
SqlCommand command = new SqlCommand(sql, Connection);
return command.ExecuteNonQuery();
}
public static int ExecuteSql(string sql, SqlParameter[] sqlParameter)
{
SqlCommand command = new SqlCommand(sql, Connection);
foreach (SqlParameter parameter in sqlParameter)
{
command.Parameters.Add(parameter);
}
return command.ExecuteNonQuery();
}
}