孙强的查询基类，及应用。

最新推荐文章于 2024-05-14 12:41:34 发布

zgh2002007

最新推荐文章于 2024-05-14 12:41:34 发布

阅读量1k

点赞数

文章标签： dataset string exception object null join

本文链接：https://blog.csdn.net/zgh2002007/article/details/1421349

版权

using System;
using System.Data.SqlClient;
using System.Data;

namespace WebShop
{
/// <summary>
/// Common 的摘要说明。
/// </summary>
public class Common:IDisposable
{
  string sqlconn=System.Configuration.ConfigurationSettings.AppSettings["sqlconn"].ToString();
  SqlDataAdapter adapter;
  public DataSet full(string sqltext,CommandType comtype,string tablename,params SqlParameter[] para)
  {
   adapter=new SqlDataAdapter();
   adapter.SelectCommand=new SqlCommand();
   adapter.SelectCommand.Connection=new SqlConnection(sqlconn);
   adapter.SelectCommand.CommandText=sqltext;
   adapter.SelectCommand.CommandType=comtype;

   foreach(SqlParameter p in para)
   {
    adapter.SelectCommand.Parameters.Add(p);
   }

DataSet ds=new DataSet();

   try
   {
    adapter.SelectCommand.Connection.Open();
    adapter.Fill(ds,tablename);
    return ds;
   }
   catch(System.Exception ex)
   {
    throw new Exception(ex.Message);

   }
   finally
   {
    adapter.SelectCommand.Connection.Close();

   }

  }

  public bool exec(string sqltext,CommandType comtype,params SqlParameter[] para)
  {
   adapter=new SqlDataAdapter();
   adapter.SelectCommand=new SqlCommand();
   adapter.SelectCommand.Connection=new SqlConnection(sqlconn);
   adapter.SelectCommand.CommandText=sqltext;
   adapter.SelectCommand.CommandType=comtype;

   foreach(SqlParameter p in para)
   {
    adapter.SelectCommand.Parameters.Add(p);
   }

   bool number=false;
   try
   {
    adapter.SelectCommand.Connection.Open();
    if(adapter.SelectCommand.ExecuteNonQuery()>0)
     number=true;
    return number;

   }
   catch(System.Exception ex)
   {
    throw new Exception(ex.Message);

   }
   finally
   {
    adapter.SelectCommand.Connection.Close();

   }

  }
  #region IDisposable 成员

  public void Dispose()
  {
   // TODO: 添加 Common.Dispose 实现
  }
  protected void Dispose(bool displsing)
  {
   if(displsing)
   {
    if(adapter!=null)
    {
     if(adapter.SelectCommand!=null)
     {
      if(adapter.SelectCommand.Connection!=null)
       adapter.SelectCommand.Connection.Dispose();
      adapter.SelectCommand.Dispose();
     }
     adapter.Dispose();
     adapter=null;

    }
   }
  }

#endregion
}
}

应用

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

namespace bargaining.ascx
{
/// <summary>
/// gerenfabu 的摘要说明。
/// </summary>
public class gerenfabu : System.Web.UI.Page
{
  protected System.Web.UI.WebControls.Label Label1;
  protected System.Web.UI.WebControls.Button Button1;
  protected System.Web.UI.WebControls.DataList youxibi;

  private void Page_Load(object sender, System.EventArgs e)
  {
   // 在此处放置用户代码以初始化页面
   if(!Page.IsPostBack)
   {
    WebShop.Common common=new WebShop.Common();
    string strSql="SELECT wupinxinxi.shangpinid,wupinxinxi.serverid,dbo.game.gamename, dbo.subarea.gamesubarea,wupinxinxi.xinxibiaoti, dbo.server.gameserver,prop.propname,prop.propid, dbo.wupinxinxi.shuliang AS shu FROM dbo.wupinxinxi INNER JOIN dbo.game ON dbo.wupinxinxi.gameid = dbo.game.gameid INNER JOIN dbo.server ON dbo.wupinxinxi.serverid = dbo.server.serverid INNER JOIN dbo.subarea ON dbo.wupinxinxi.subareaid = dbo.subarea.subareaid inner join prop on wupinxinxi.propid=prop.propid GROUP BY dbo.game.gamename, dbo.subarea.gamesubarea, dbo.server.gameserver,dbo.prop.propname,wupinxinxi.xinxibiaoti,prop.propid,wupinxinxi.propid,wupinxinxi.serverid ,wupinxinxi.userid,wupinxinxi.shangpinid,dbo.wupinxinxi.shuliang,shengyushuliang HAVING (dbo.wupinxinxi.userid = '"+Session["UserId"].ToString()+"') and shengyushuliang>0";
    DataSet ds=common.full(strSql,CommandType.Text,"bixiangxi");
    if(ds.Tables[0].Rows.Count>0)
    {
//     this.LabelRecord.Text=ds.Tables[0].Rows.Count.ToString();
//     int RecordCount=int.Parse(this.LabelRecord.Text);
//     int intPageNo,intPageSize,intPageCount;
//     intPageSize=5;
//     intPageCount=RecordCount%intPageSize==0?RecordCount/intPageSize:RecordCount/intPageSize+1;
//     LabelPage.Text = intPageCount.ToString();
//
//     PagedDataSource objpage=new PagedDataSource();
//     objpage.DataSource=ds.Tables[0].DefaultView;
//     objpage.AllowPaging=true;
//     objpage.PageSize=intPageSize;
//     if(this.Request["CurrentPage"]==null)
//     {
//      intPageNo=1;
//     }
//     else
//     {
//      intPageNo=int.Parse(this.Request["CurrentPage"]);
//     }
//
//     if(!this.IsPostBack)
//     {
//      for(int i=1;i<=intPageCount;i++)
//      {
//       this.Dropdownlist2.Items.Add(i.ToString());
//      }
//      this.Dropdownlist2.SelectedValue=intPageNo.ToString();
//     }
//     intPageNo=int.Parse(this.Dropdownlist2.SelectedItem.Text);
//     this.LabelRow.Text=intPageNo.ToString();
//     objpage.CurrentPageIndex=intPageNo-1;
//     if(intPageNo>1)
//     {
//      this.HLFistPage.NavigateUrl="gerenshangdian.aspx?CurrentPage=1";
//      this.HLPrevPage.NavigateUrl=String.Concat("gerenshangdian.aspx?CurrentPage=","",intPageNo-1);
//     }
//     else
//     {
//      HLFistPage.NavigateUrl = "";
//      HLPrevPage.NavigateUrl = "";
//     }
//     if(intPageNo!=intPageCount)
//     {
//      this.HLNextPage.NavigateUrl=String.Concat("gerenshangdian.aspx?CurrentPage=","",intPageNo+1);
//      this.HLEndPage.NavigateUrl=String.Concat("gerenshangdian.aspx?CurrentPage=","",intPageCount);
//     }
//     else
//     {
//      this.HLNextPage.NavigateUrl="";
//      this.HLEndPage.NavigateUrl="";
//     }
     this.youxibi.DataSource=ds.Tables[0];
     this.youxibi.DataBind();
    }
   }
  }

  #region Web 窗体设计器生成的代码
  override protected void OnInit(EventArgs e)
  {
   //
   // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
   //
   InitializeComponent();
   base.OnInit(e);
  }

  /// <summary>
  /// 设计器支持所需的方法 - 不要使用代码编辑器修改
  /// 此方法的内容。
  /// </summary>
  private void InitializeComponent()
  {
   this.Button1.Click += new System.EventHandler(this.Button1_Click);
   this.Load += new System.EventHandler(this.Page_Load);

}
#endregion

  public void Button1_Click(object sender, System.EventArgs e)
  {
   LinkButton d=(LinkButton)sender;
   string id=d.CommandArgument.ToString();
   WebShop.Common1 comm=new WebShop.Common1();
   string strSql="delete wupinxinxi where shangpinid='"+id+"'";
   comm.exec(strSql,CommandType.Text,"wupinxinxi");

  }
}
}

namespace bargaining
{
/// <summary>
/// dingdan 的摘要说明。
/// </summary>
public class dingdan : System.Web.UI.Page
{
  protected System.Web.UI.HtmlControls.HtmlForm Form1;
  protected System.Web.UI.HtmlControls.HtmlForm Form2;
  protected System.Web.UI.WebControls.Label Label10;
  protected System.Web.UI.WebControls.Label Label8;
  protected System.Web.UI.WebControls.Label Label9;
  protected System.Web.UI.WebControls.Label Label7;
  protected System.Web.UI.WebControls.LinkButton LinkButton1;
  protected System.Web.UI.WebControls.Label Label1;
  protected System.Web.UI.WebControls.Label Label6;
  protected System.Web.UI.WebControls.Label Label5;
  protected System.Web.UI.WebControls.Label Label3;
  protected System.Web.UI.WebControls.Label Label2;
  protected System.Web.UI.WebControls.Label Label4;
  protected System.Web.UI.WebControls.Image Image1;
  protected System.Web.UI.HtmlControls.HtmlTable Table7;
  protected System.Web.UI.WebControls.LinkButton LinkButton2;
  protected System.Web.UI.HtmlControls.HtmlForm Form3;

  private void Page_Load(object sender, System.EventArgs e)
  {
   string h="";
   if(!this.IsPostBack)
   {
    try
    {
     h=Session["UserId"].ToString();
    }
    catch
    {
     h="";
    }
    if(h!="")
    {
     this.Table7.Visible=false;
     string diankaname=this.Request.QueryString["dingdanname"].ToString();;
     Response.Write(diankaname);
     DataSet ds;
     string strSql="SELECT dbo.diankaxinxi.diankaid, dbo.diankaxinxi.diankaname, dbo.diankaxinxi.accountname, dbo.diankaxinxi.password, dbo.diankaxinxi.newjia, dbo.diankaxinxi.jiage, dbo.dianka.jieshengshu, dbo.dianka.dianshu, dbo.dianka.img ,dianka.beizhu,dbo.diankaxinxi.xiangxiid FROM dbo.dianka INNER JOIN dbo.diankaxinxi ON dbo.dianka.diankaid = dbo.diankaxinxi.diankaid where dianka.diankaname='"+diankaname+"'";
     using(WebShop.Common common=new WebShop.Common())
     {
      ds=common.full(strSql,CommandType.Text,"dianka");
     }
     if(ds.Tables[0].Rows.Count>0)
     {
      this.Image1.ImageUrl="dianka/"+ds.Tables[0].Rows[0][8].ToString();
      this.Label4.Text=ds.Tables[0].Rows[0][1].ToString();
      this.Label2.Text=ds.Tables[0].Rows[0][5].ToString();
      this.Label3.Text=ds.Tables[0].Rows[0][4].ToString();
      string jiesheng=ds.Tables[0].Rows[0][6].ToString();
      double jie=double.Parse(jiesheng.ToString());
      if(jie<0)
      {
       this.Label5.Text="现价高于原价";
      }
      else
      {
       this.Label5.Text=ds.Tables[0].Rows[0][6].ToString()==""?"0.00":ds.Tables[0].Rows[0][6].ToString();
      }
      this.Label6.Text=ds.Tables[0].Rows[0][7].ToString();
      this.Label1.Text=ds.Tables[0].Rows[0][9].ToString();
      this.ViewState.Add("accountname",ds.Tables[0].Rows[0][2].ToString());
      this.ViewState.Add("password",ds.Tables[0].Rows[0][3].ToString());
      this.ViewState.Add("diankaid",ds.Tables[0].Rows[0][10].ToString());
     }
     else
     {

      Response.Write( "<script language='javascript'>");
      Response.Write("alert('没有此点卡');");
      Response.Write( "parent.opener=null;");
      Response.Write( "window.close();");
      Response.Write( "</script>");

     }
    }
    else
    {
     Response.Write( "<script language='javascript'>");
     Response.Write("alert('请您先登陆');");
     Response.Write( "parent.opener=null;");
     Response.Write( "window.close();");
     Response.Write( "</script>");

}

   this.LinkButton1.Attributes.Add("onclick","return confirm('您确认购买吗？')");
  }
  private void sendemail(string kano,string pwd)
  {

   MailMessage mymail=new MailMessage();
   mymail.To=this.Session["Email"].ToString();
   mymail.From="lfjdc001@163.com";
   mymail.Subject=":您好！请查收您在天诚游戏网购买的点卡账号及密码！";
   mymail.Body="点卡账号："+kano+"点卡密码："+pwd;
   mymail.BodyFormat=MailFormat.Html;
   mymail.Fields.Add("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate", "1" );
   mymail.Fields.Add("http://schemas.microsoft.com/cdo/configuration/sendusername", "lfjdc001" );
   mymail.Fields.Add("http://schemas.microsoft.com/cdo/configuration/sendpassword", "2881056" );
   SmtpMail.SmtpServer ="smtp.163.com";
   try
   {
    SmtpMail.Send(mymail);

    this.Response.Write("<script>alert('点卡账号及密码已经发送到您的邮箱中，请注意查收！')</script>");
   }
   catch(System.Exception ex)
   {
    throw new Exception(ex.Message);
   }
  }
  #region Web 窗体设计器生成的代码
  override protected void OnInit(EventArgs e)
  {
   //
   // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
   //
   InitializeComponent();
   base.OnInit(e);
  }

  /// <summary>
  /// 设计器支持所需的方法 - 不要使用代码编辑器修改
  /// 此方法的内容。
  /// </summary>
  private void InitializeComponent()
  {
   this.LinkButton1.Click += new System.EventHandler(this.LinkButton1_Click);
   this.LinkButton2.Click += new System.EventHandler(this.LinkButton2_Click);
   this.Load += new System.EventHandler(this.Page_Load);

}
#endregion

private void LinkButton1_Click(object sender, System.EventArgs e)
{

   DataSet account=(DataSet)this.Session["account"];
   if(account.Tables[0].Rows.Count>0)
   {
    if(double.Parse(account.Tables[0].Rows[0][2].ToString())>=double.Parse(this.Label3.Text))
    {
     int userid=int.Parse(this.Session["UserID"].ToString());
     using(WebShop.Common common=new WebShop.Common())
     {
      common.exec("pro_diankatran",CommandType.StoredProcedure,new SqlParameter("@userid",userid),new SqlParameter("@wupinname",this.Label4.Text),new SqlParameter("@jiage",decimal.Parse(this.Label3.Text)),new SqlParameter("@dingdantime",System.DateTime.Now.ToString()),new SqlParameter("@mymoney",decimal.Parse(this.Label3.Text)),new SqlParameter("@diankaid",int.Parse(this.ViewState["diankaid"].ToString())));
     }
     this.Table7.Visible=true;
     this.Label9.Visible=true;
     this.Label10.Visible=true;
     this.Label9.Text=this.ViewState["accountname"].ToString();
     this.Label10.Text=this.ViewState["password"].ToString();
     this.LinkButton1.Enabled=false;

this.sendemail(this.ViewState["accountname"].ToString(),this.ViewState["password"].ToString());
// this.Response.Redirect("default.aspx",false);

    }
    else
    {
     this.Response.Write("<script>alert('您的可用金额不足，请充值！')</script>");
    }
   }
   else
   {
    this.Response.Write("<script>alert('您的账户尚未充值，请先充值！')</script>");
   }
  }

  private void LinkButton2_Click(object sender, System.EventArgs e)
  {
//   this.Response.Redirect("default.aspx",true);
   DataSet account=(DataSet)this.Session["account"];
   if(account.Tables[0].Rows.Count>0)
   {
    if(double.Parse(account.Tables[0].Rows[0][2].ToString())>=double.Parse(this.Label3.Text))
    {
     int userid=int.Parse(this.Session["UserID"].ToString());
     using(WebShop.Common common=new WebShop.Common())
     {
      common.exec("pro_diankatran",CommandType.StoredProcedure,new SqlParameter("@userid",userid),new SqlParameter("@wupinname",this.Label4.Text),new SqlParameter("@jiage",decimal.Parse(this.Label3.Text)),new SqlParameter("@dingdantime",System.DateTime.Now.ToString()),new SqlParameter("@mymoney",decimal.Parse(this.Label3.Text)),new SqlParameter("@diankaid",int.Parse(this.ViewState["diankaid"].ToString())));
     }
     this.Table7.Visible=true;
     this.Label9.Visible=true;
     this.Label10.Visible=true;
     this.Label9.Text=this.ViewState["accountname"].ToString();
     this.Label10.Text=this.ViewState["password"].ToString();
     this.LinkButton1.Enabled=false;