使用存储过程和三层体系结构插入更新删除 [英] insert update delete using store procedure and three tier architecture

问题描述

我想在gridview中显示一些记录。我使用三层架构来显示记录

这是商店程序和表名apmc2。

 创建 过程 proapmc3 
  as  
 选择 * 来自 apmc2 

BEL类中的
.i已经声明已经在我的表格中的属性。

  public   class  BEL 
 {
 
  public   string  ID { get ;  set ; } 
  public   string 名称{ get ;  set ;} 
  public   decimal 薪水{获取;  set ; } 
  public  DateTime Date_In { get ;  set ;我已经宣布
 
 
  public   object  displygird（BEL oob）
 {
 DLL ob =  new  DLL（）; 
  return  ob.bindgrid（oob）; DLL类中的
} 
 
 
 
  public   object  bindgrid（BEL obj）
 {
 SqlConnection con =  new  SqlConnection（  Data Source = .; Initial Catalog = master; Integrated Security = True）; 
 con.Open（）; 
 SqlCommand cmd =  new  SqlCommand（  proapmc3 ，con）; 
 cmd.CommandType = CommandType.StoredProcedure; 
 SqlDataAdapter da =  new  SqlDataAdapter（cmd）; 
 cmd.Parameters.Add（ new  SqlParameter（  @id，SqlDbType.VarChar， 22 ））; 
 cmd.Parameters [  @ id]。Value = obj.ID; 
 
 cmd.Parameters.Add（ new  SqlParameter（  @ Name，SqlDbType.VarChar， 44 ））; 
 cmd.Parameters [  @ Name]。Value = obj.Name; 
 
 cmd.Parameters.Add（ new  SqlParameter（  @ Salary，SqlDbType.Decimal， 7 ））; 
 cmd.Parameters [  @ Salary]。Value = obj.Salary; 
 
 cmd.Parameters.Add（ new  SqlParameter（  @ Date_in，SqlDbType.DateTime， 27 ））; 
 cmd.Parameters [  @ Date_in]。Value = obj.Date_In; 
 
 DataSet ds =  new  DataSet（）; 
 BLL ob =  new  BLL（）; 
 
 da.Fill（ds）; 
  return  ds; 
} 
 
 
和我家中的最后一个.aspx 
 
方法是吹
  public   void  displygrid（）
 {
 BEL ojbelb = 新 BEL（）; 
 BLL ob =  new  BLL（）; 
 GridView1.DataSource = ob.displygird（ojbelb）; 
 GridView1.DataBind（）; 
} 
 
 
请帮助我，我错了。
 
 
 
我收到一个异常错误在da.fill（ds）行
 
 SqlDateTime溢出。必须在1/1/1753 12:00:00 AM和12/31/9999 11:59之间： 59 PM。 
解决方案
 
请参阅：将null作为SQLParameter DateTime值传递 
 
 
如果您正在使用像您在问题中提到的存储过程... 
 
 
 
 
 创建 程序 proapmc3 
  as  
  select  * 来自 apmc2 
 
 
 
 
并且它不会使用任何参数来返回数据，那么为什么要向bindgrid方法发送参数呢？ br /> 
 
 
并根据您的代码，y你正在使用BEL类对象来定义你的属性，你只是创建它的对象并在你的displygird方法中传递它而不初始化任何属性值，如ID，Name或Date_In等。
 
 
 
请删除DLL方法中的所有参数绑定，也要从BLL方法中删除BLE参数。并尝试调试所有这些方法。
 
 
 
像这样... 
 
 
 
 
 SqlConnection con =  new  SqlConnection（ 数据源= .;初始目录=主;集成安全性=真）; 
 con.Open（）; 
 SqlCommand cmd =  new  SqlCommand（  proapmc3 ，con）; 
 cmd.CommandType = CommandType.StoredProcedure; 
 SqlDataAdapter da =  new  SqlDataAdapter（cmd）; 
 DataSet ds =  new  DataSet（）; 
 da.Fill（ds）; 
 
 
 
 
 
也请注意你的开放连接对象，请正确打开/关闭你的sql server连接
 
 
i want to display some records in the gridview. i am using three tier architecture for display the records 

This is store procedure and table name apmc2.

create procedure proapmc3
as
select * from apmc2


in the BEL class .i have declared properties which is already in my table.

public class BEL
{

    public string ID { get; set; }
    public string Name { get; set;}
    public decimal Salary { get; set; }
    public DateTime Date_In { get; set; }
}


in the BLL class i have declared 

public object displygird(BEL oob)
{
    DLL ob = new DLL();
    return ob.bindgrid(oob);
}


in the DLL class
public object bindgrid(BEL obj)
{
    SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=master;Integrated Security=True");
    con.Open();
    SqlCommand cmd = new SqlCommand("proapmc3", con);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.VarChar, 22));
    cmd.Parameters["@id"].Value = obj.ID;

    cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar, 44));
    cmd.Parameters["@Name"].Value = obj.Name;

    cmd.Parameters.Add(new SqlParameter("@Salary", SqlDbType.Decimal, 7));
    cmd.Parameters["@Salary"].Value = obj.Salary;

    cmd.Parameters.Add(new SqlParameter("@Date_in", SqlDbType.DateTime, 27));
    cmd.Parameters["@Date_in"].Value = obj.Date_In;

    DataSet ds = new DataSet();
    BLL ob = new BLL();

    da.Fill(ds);
    return ds;
}


and in the last in my home.aspx 

method is blew
public void displygrid()
{
    BEL ojbelb = new BEL();
    BLL ob = new BLL();
    GridView1.DataSource = ob.displygird(ojbelb);
    GridView1.DataBind();
}


please help me where i am wrong .



I am getting one exception error in da.fill(ds) line 

"SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
解决方案
Refer this : Passing null as SQLParameter DateTime value

If you are using store procedure like you mention in your question...



create procedure proapmc3
as
select * from apmc2




And it will not taking any parameter to return data, so why are you sending parameter to your bindgrid method?



and as per your code, you are using BEL class object to define your property and you are just creating object of it and pass it in your displygird method without initializing any property values like ID, Name or Date_In etc.



Please remove all parameter binding in your DLL method and also remove parameter of BLE from your BLL method too. And try to debug all this methods.



Like this...



SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=master;Integrated Security=True");
con.Open();
SqlCommand cmd = new SqlCommand("proapmc3", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);




also please take care your your open connection object and please properly open/close your sql server connection


                        
这篇关于使用存储过程和三层体系结构插入更新删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！