初次涉足三层应用的存储过程,肯请各位大哥给一个三层应用的操作存储过程的例子,把我这个简单的sql语句变成三层应用的存储过程即可!如下一个简单的sql语句:
------------------------------------public void bindinfo()
{ string typeid = Request.QueryString["id"].ToString();
string category = this.TextBox1.Text.ToString();
string keywords = this.TextBox2.Text.ToString(); string sql = "select * from news_info where newstype = '"+typeid+"' and newscategory = '"+category+"' and newstitle like '%"+keywords+"%' order by newsid desc"; DataSet ds = newdata.dbind(sql);}------------------------------------要求:
有一个DAL数据访问层,然后在应用的时候,调用DAL层中的方法,我看过petshop中的存储过程的例子,无耐我太菜,看了很长时间,看不懂,没办法,请各位哥哥如果能我给我一个这样的实例,高效正规些的,我会一下子入门的,谢 谢 大 家了!
------------------------------------public void bindinfo()
{ string typeid = Request.QueryString["id"].ToString();
string category = this.TextBox1.Text.ToString();
string keywords = this.TextBox2.Text.ToString(); string sql = "select * from news_info where newstype = '"+typeid+"' and newscategory = '"+category+"' and newstitle like '%"+keywords+"%' order by newsid desc"; DataSet ds = newdata.dbind(sql);}------------------------------------要求:
有一个DAL数据访问层,然后在应用的时候,调用DAL层中的方法,我看过petshop中的存储过程的例子,无耐我太菜,看了很长时间,看不懂,没办法,请各位哥哥如果能我给我一个这样的实例,高效正规些的,我会一下子入门的,谢 谢 大 家了!
解决方案 »
- js扩展Object??
- 郁闷了半天的一个初级问题....急.在线等....
- 如何在网页中使用摄像头
- 关于ASP.NET中DataGrid的打印问题,在线等。。。
- server=(local)\sms连接
- 用Repeater绑定数据的,但想要根据数据库里某一个字段的值来让Repeater里面的表格某一行显示或隐藏怎么办?
- 个人出来接项目做,与商务合作人怎么分配利益?
- 还是datagrid的问题
- Girdview控件的自定义分页实例
- 请问:如何在客户端形成doc文件,将服务器端数据库中得到的数据写入这个doc文件中?--在线
- 又遇到了错误,请高手指点一下啦
- 这个后台没有代码,所以也跳转不了,我该怎么办?用的是DataList
int result=UserOper.RegUser(this.username.Text,this.password1.Text,this.sign.Text);调用逻辑层
# region 注册
public static int RegUser(string username,string password,string sign)
{
if(username.Length>20||username.Length<4)
return 1;
else
{
SqlDataReader dr=GetData.GetOneUser(username);
dr.Read();
if(dr.HasRows)
return 0;
else
{
if(password.Length>20||password.Length<4)
return 2;
else
{
if(sign.Length>200)
return 3;
else
{
password=System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(password,"md5");
SqlParameter [] arrParms = new SqlParameter[4];
arrParms[0] = new SqlParameter("@user_name", SqlDbType.NVarChar,50);
arrParms[0].Value = username;
arrParms[1] = new SqlParameter("@user_password", SqlDbType.NVarChar,50);
arrParms[1].Value = password;
arrParms[2] = new SqlParameter("@user_sign", SqlDbType.NVarChar,200);
arrParms[2].Value = sign;
arrParms[3] = new SqlParameter("@time", SqlDbType.NVarChar,50);
arrParms[3].Value = DateTime.Now;
SqlHelper.ExecuteNonQuery(Conn.strconn,CommandType.StoredProcedure,"sp_InsertUser",arrParms);
return 4;
}
}
}
}
}
# endregion 调用的是微软写的sqlhelper的类注册的sp表现层repeaterClass.DataSource=GetData.GetClassAndBoard();
Page.DataBind();逻辑层public static DataSet GetClassAndBoard()
{
if(System.Web.HttpContext.Current.Cache["ClassAndBoard"]==null)
{
DataSet ds=new DataSet();
SqlHelper.FillDataset(Conn.strconn,CommandType.StoredProcedure,"sp_SelectClass",ds,new string[]{"tb_class"});
SqlHelper.FillDataset(Conn.strconn,CommandType.StoredProcedure,"sp_SelectBoard",ds,new string[]{"tb_board"});
ds.Relations.Add("Board_Class",ds.Tables["tb_class"].Columns["Class_ID"],ds.Tables["tb_board"].Columns["Board_Class_ID"]);
System.Web.HttpContext.Current.Cache["ClassAndBoard"]=ds;
return ds;
}
else
{
return (DataSet)System.Web.HttpContext.Current.Cache["ClassAndBoard"];
}
}存储过程不写了就是select这个似乎更清晰
{
SqlCommand cmd = CreateCommand(procName, null);
cmd.ExecuteNonQuery();
this.Close();
return (int)cmd.Parameters["ReturnValue"].Value;
} /// <summary>
/// Run stored procedure,have params,return int.
/// </summary>
/// <param name="procName">Name of stored procedure.</param>
/// <param name="prams">Stored procedure params.</param>
/// <returns>Stored procedure return value.</returns>
public int RunProcINT(string procName, SqlParameter[] prams) {
SqlCommand cmd = CreateCommand(procName, prams);
cmd.ExecuteNonQuery();
this.Close();
return (int)cmd.Parameters["ReturnValue"].Value;
}
/// <summary>
/// Run proc,return value.
/// </summary>
public object RunProcValue(string procName, SqlParameter[] prams)
{
SqlCommand cmd = CreateCommand(procName, prams);
object id=cmd.ExecuteScalar();
this.Close();
return id;
}
// make sure connection is open
Open(); //command = new SqlCommand( sprocName, new SqlConnection( ConfigManager.DALConnectionString ) );
SqlCommand cmd = new SqlCommand(procName, con);
cmd.CommandType = CommandType.StoredProcedure; // add proc parameters
if (prams != null) {
foreach (SqlParameter parameter in prams)
cmd.Parameters.Add(parameter);
}
// return param
cmd.Parameters.Add(
new SqlParameter("ReturnValue", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0,
string.Empty, DataRowVersion.Default, null)); return cmd;
}
/// <summary>
/// Create command object by Text.
/// </summary>
private SqlCommand CreateCommand2(string stringcmd, SqlParameter[] prams) {
// make sure connection is open
Open();
SqlCommand cmd = new SqlCommand(stringcmd, con);
cmd.CommandType = CommandType.Text; // add proc parameters
if (prams != null) {
foreach (SqlParameter parameter in prams)
cmd.Parameters.Add(parameter);
}
return cmd;
}
/// <summary>
/// Create command object by Text.
/// Overload the CreateCommand2
/// Excute Sql State.
/// </summary>
private SqlCommand CreateCommand2(string stringcmd)
{
// make sure connection is open
Open();
SqlCommand cmd = new SqlCommand(stringcmd, con);
cmd.CommandType = CommandType.Text;
return cmd;
}
/// <summary>
/// Open the connection.
/// </summary>
private void Open()
{
// open connection
if (con == null)
{
con = new SqlConnection(GetConnectionString());
try
{
con.Open();
}
catch(Exception ex)
{
// StreamWriter sw=new StreamWriter(path,false);
// sw.WriteLine(ex.Source+"|"+ex.Message);
// sw.Close();
con=null;
}
}
else if(con!=null && Convert.ToString(con.State)=="Closed")
{
con.Open();
}
}
using System.Data;
using System.Data.SqlClient;namespace D
{
/// <summary>
/// Class1 的摘要说明。
/// </summary>
public sealed class DB
{
public DB()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
/// <summary>
/// 数据库连接对象.可以通过连接对象调用事务
/// </summary>
public static SqlConnection conn = null;
/// <summary>
/// 打开数据库连接
/// </summary>
public static void Open()
{
conn = new SqlConnection();
if(conn.State != ConnectionState.Open)
{
//System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader();
//conn.ConnectionString = ((string)(configurationAppSettings.GetValue("ConnectionString", typeof(string))));
// conn.ConnectionString = "workstation id=xiaodong;packet size=4096;user id=sa;initial catalog=5173;persist security info=True;password=654321";
conn.ConnectionString = "User ID=sa;Initial Catalog=pubs;Data Source=192.168.1.111;Packet Size=4096;Workstation ID=XIAODONG;password=sa";
conn.Open();
}
}
/// <summary>
/// 执行一个SQL语句返回执行后返回记录
/// </summary>
/// <param name="CommandText">sql 语句</param>
/// <returns>dataset</returns>
public static DataSet GetDataSet(string CommandText)
{
if(CommandText == ""||CommandText == null)
{
throw new Exception("参数不正确");
}
try
{
SqlDataAdapter da = new SqlDataAdapter(CommandText,conn);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
catch(SqlException e)
{
Close();
throw new Exception(e.Message);
}
} /// <summary>
/// 执行带参数的存储过程.并返回结果集
/// </summary>
/// <param name="spName">存储过程</param>
/// <param name="SPparam"></param>
/// <returns></returns>
public static DataSet GetDataSet(string spName,SqlParameter [] SPparam)
{
if(spName == ""||spName == null)
{
throw new Exception("参数不正确");
}
try
{
SqlCommand cmd = new SqlCommand(spName,conn);
cmd.CommandType = CommandType.StoredProcedure;
if(SPparam != null)
{
for(int i = 0 ; i < SPparam.Length; i++)
{
cmd.Parameters.Add(SPparam[i]);
}
}
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
catch(SqlException e)
{
Close();
throw new Exception(e.Message);
}
} /// <summary>
/// 执行一个sql 语句返回执行的结果.该结果对与Update insert delete返回该命令的影响行数 .对于select或者命令语句返回-1
/// </summary>
/// <param name="CommandText"></param>
/// <returns></returns>
public static int ExecuteNonQuery(string CommandText)
{
if(CommandText == ""||CommandText == null)
{
throw new Exception("参数不正确");
}
try
{
SqlCommand cmd = new SqlCommand(CommandText,conn);
cmd.CommandType = CommandType.Text;
int i = cmd.ExecuteNonQuery();
return i;
}
catch(SqlException e)
{
Close();
throw new Exception(e.Message);
}
}
/// <summary>
/// 执行一个带参数的存储过程并返回执行的结果.该结果对与Update insert delete返回该命令的影响行数 .对于select或者命令语句返回-1
/// </summary>
/// <param name="spName"></param>
/// <param name="SPparam"></param>
/// <returns></returns>
public static int ExecuteNonQuery(string spName,SqlParameter [] SPparam)
{
if(spName == ""||spName == null)
{
throw new Exception("参数不正确");
}
try
{
SqlCommand cmd = new SqlCommand(spName,conn);
if(SPparam != null)
{
for(int i = 0 ; i < SPparam.Length; i++)
{
cmd.Parameters.Add(SPparam[i]);
}
}
cmd.CommandType = CommandType.StoredProcedure;
int m = cmd.ExecuteNonQuery();
return m;
}
catch(SqlException e)
{
Close();
throw new Exception(e.Message);
}
}
/// <summary>
/// 执行一个SQL 语句返回 SqlDataReader 对象,该方法对与只读记录类型具有性能上的优势.
/// </summary>
/// <param name="CommandText"></param>
/// <returns></returns>
public static SqlDataReader ExecuteReader(string CommandText)
{
if(CommandText == ""||CommandText == null)
{
throw new Exception("参数不正确");
}
try
{
SqlCommand cmd = new SqlCommand(CommandText,conn);
cmd.CommandType = CommandType.Text;
SqlDataReader dr = cmd.ExecuteReader();
return dr;
}
catch(SqlException e)
{
Close();
throw new Exception(e.Message);
}
} /// <summary>
/// 执行一个带参数的存储过程返回 SqlDataReader 对象,该方法对与只读记录类型具有性能上的优势.
/// </summary>
/// <param name="spName"></param>
/// <param name="SPparam"></param>
/// <returns></returns>
public static SqlDataReader ExecuteReader(string spName,SqlParameter [] SPparam)
{
if(spName == ""||spName == null)
{
throw new Exception("参数不正确");
}
try
{
SqlCommand cmd = new SqlCommand(spName,conn);
if(SPparam != null)
{
for(int i = 0 ; i < SPparam.Length; i++)
{
cmd.Parameters.Add(SPparam[i]);
}
}
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader dr = cmd.ExecuteReader();
return dr;
}
catch(SqlException e)
{
Close();
throw new Exception(e.Message);
}
} /// <summary>
/// 执行查询,并返回查询所返回的结果集中第一行的第一列。忽略额外的列或行
/// </summary>
/// <param name="CommandText"></param>
/// <returns></returns>
public static object ExecuteScalar(string CommandText)
{
if(CommandText == ""||CommandText == null)
{
throw new Exception("参数不正确");
}
try
{
SqlCommand cmd = new SqlCommand(CommandText,conn);
cmd.CommandType = CommandType.Text;
object o = cmd.ExecuteScalar();
return o;
}
catch(SqlException e)
{
Close();
throw new Exception(e.Message);
}
} /// <summary>
/// 执行带参数的存储过程查询,并返回查询所返回的结果集中第一行的第一列。忽略额外的列或行
/// </summary>
/// <param name="spName"></param>
/// <param name="SPparam"></param>
/// <returns></returns>
public static object ExecuteScalar(string spName,SqlParameter [] SPparam)
{
if(spName == ""||spName == null)
{
throw new Exception("参数不正确");
}
try
{
SqlCommand cmd = new SqlCommand(spName,conn);
if(SPparam != null)
{
for(int i = 0 ; i < SPparam.Length; i++)
{
cmd.Parameters.Add(SPparam[i]);
}
}
cmd.CommandType = CommandType.StoredProcedure;
object o = cmd.ExecuteScalar();
return o;
}
catch(SqlException e)
{
Close();
throw new Exception(e.Message);
}
} /// <summary>
/// 关闭数据库连接对象
/// </summary>
public static void Close()
{
if(conn.State != ConnectionState.Closed)
{ conn.Close();
}
}
/// <summary>
/// 用来过滤SQL语句中的不安全字符函数
/// </summary>
/// <param name="strUserInput"></param>
/// <returns></returns>
public static string Replace(string strUserInput)
{
strUserInput = strUserInput.Replace("<", "<");
strUserInput = strUserInput.Replace(">", "> ");
strUserInput = strUserInput.Replace("'", "''");
strUserInput = strUserInput.Replace("%", string.Empty);
strUserInput = strUserInput.Replace(":", string.Empty);
strUserInput = strUserInput.Replace("(", string.Empty);
strUserInput = strUserInput.Replace(")", string.Empty);
strUserInput = strUserInput.Replace("&", string.Empty);
strUserInput = strUserInput.Replace("+", string.Empty);
strUserInput = strUserInput.Replace("-", string.Empty);
return strUserInput;
}
}
}