ASP.NET - Data Adapter, Store procedure

Asked By Neha .
20-Aug-11 12:44 PM
hi,

can any one give me example of how to use data adapter with store procedure.

Thanks
  dipa ahuja replied to Neha .
20-Aug-11 01:17 PM
You can use DataAdapter with stored procedure with the help of Sqlcommand object: 

protected void Page_Load(object sender, EventArgs e)
{
  if (!IsPostBack)
  {
    string connString = "ConnectionString";
    SqlConnection conn = new SqlConnection(connString);
    SqlCommand comm = new SqlCommand("selection", conn);
 
    comm.CommandType = CommandType.StoredProcedure;
 
    SqlDataAdapter da = new SqlDataAdapter(comm);
 
    DataTable dt = new DataTable();
    da.Fill(dt);
    GridView1.DataSource = dt;
    GridView1.DataBind();
  }
}
  Ravi S replied to Neha .
20-Aug-11 01:34 PM
HI

refer this

protected DataTable RetrieveEmployeeSubInfo(string employeeNo)
        {
            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter da = new SqlDataAdapter();
            DataTable dt = new DataTable();
            try
            {
                cmd = new SqlCommand("RETRIEVE_EMPLOYEE", pl.ConnOpen());
                cmd.Parameters.Add(new SqlParameter("@EMPLOYEENO", employeeNo));
                cmd.CommandType = CommandType.StoredProcedure;
                da.SelectCommand = cmd;
                da.Fill(dt);
                dataGridView1.DataSource = dt;
            }
            catch (Exception x)
            {
                MessageBox.Show(x.GetBaseException().ToString(), "Error",
                        MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                cmd.Dispose();
                pl.MySQLConn.Close();
            }
            return dt;
        }
refer  
 http://www.c-sharpcorner.com/UploadFile/dclark/UseSPwithDP11282005035417AM/UseSPwithDP.aspx
  Riley K replied to Neha .
20-Aug-11 11:57 PM
To access stored procedure using DataAdapter in DotNet.
The below stored procedure of two Result  will be binding to the DataSet.

string ConStr="server=.;User ID=sa;Password=sa;Initial Catalog=pubs";
SqlConnection Con = new SqlConnection(ConStr);
Con.open();
DataSet DS = new DataSet();
 
DS.EnforceConstraints=false;
       
SqlCommand Comm = new SqlCommand("P1",Con);
//Stored Procedure "P1" will return two result set of tables TableA,TableB
Comm.CommandType=CommandType.StoredProcedure;
 
SqlDataAdapter Ada = new SqlDataAdapter(Comm);
Ada.TableMappings.Add("TABLEA","A");
Ada.TableMappings.Add("TABLEB","B");
 
Ada.Fill(DS);
 
DS.EnforceConstraints=true;

You have to pass the Command Object to the stored procedure

Regards

  James H replied to Neha .
21-Aug-11 09:09 AM

A stored procedure is a named collection of SQL statements that are stored in the database. To the client a stored procedure acts similar to a function. The stored procedure is called by name, can accept parameter values passed in, and can return parameter values back to the client. There are many advantages to incorporating stored procedures into your application logic including:

  • Shared application logic among various client applications
  • Faster execution
  • Reduced network traffic
  • Improved database security

The purpose of this article is to demonstrate how stored procedures can be used in conjunction with the SqlDataAdapter in order to fill and update data contained in a DataSet.

Note: In order to complete the activities outlined in this article you must have Visual Studio .NET installed and access to SQL Server 2000 with the Pubs database installed.




private void btnGetAuthors_Click(System.Object sender, System.EventArgs e)
{
  DataSet DS = null;
  SqlConnection MyConnection = default(SqlConnection);
  SqlDataAdapter MyDataAdapter = default(SqlDataAdapter);
 
  //Create a connection to the SQL Server.
  MyConnection = new SqlConnection("server=(local);database=pubs;Trusted_Connection=yes");
 
  //Create a DataAdapter, and then provide the name of the stored procedure.
  MyDataAdapter = new SqlDataAdapter("GetAuthorsByLastName", MyConnection);
 
  //Set the command type as StoredProcedure.
  MyDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
 
  //Create and add a parameter to Parameters collection for the stored procedure.
  MyDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@au_lname", SqlDbType.VarChar, 40));
 
  //Assign the search value to the parameter.
  MyDataAdapter.SelectCommand.Parameters("@au_lname").Value = Strings.Trim(txtLastName.Text);
 
  //Create and add an output parameter to Parameters collection.
  MyDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@RowCount", SqlDbType.Int, 4));
 
  //Set the direction for the parameter. This parameter returns the Rows returned.
  MyDataAdapter.SelectCommand.Parameters("@RowCount").Direction = ParameterDirection.Output;
 
  DS = new DataSet();
  //Create a new DataSet to hold the records.
  MyDataAdapter.Fill(DS, "AuthorsByLastName");
  //Fill the DataSet with the rows returned.
 
  //Get the number of rows returned, and then assign it to the Label control.
  //lblRowCount.Text = DS.Tables(0).Rows.Count().ToString() & " Rows Found!"
  lblRowCount.Text = MyDataAdapter.SelectCommand.Parameters(1).Value + " Rows Found!";
 
  //Set the data source for the DataGrid as the DataSet that holds the rows.
  Grdauthors.DataSource = DS.Tables["AuthorsByLastName"].DefaultView;
 
  //Bind the DataSet to the DataGrid.
  //NOTE: If you do not call this method, the DataGrid is not displayed!
  Grdauthors.DataBind();
 
  MyDataAdapter.Dispose();
  //Dispose of the DataAdapter.
  MyConnection.Close();
  //Close the connection.
}

For more information, see the following topics in the Microsoft .NET Framework Software Development Kit (SDK) documentation:
Overview of ADO.NET
http://msdn.microsoft.com/en-us/library/h43ks021.aspx

ADO.NET DataSet
http://msdn.microsoft.com/en-us/library/zb0sdh0b%28VS.71%29.aspx

Using .NET Data Providers to Access Data
http://msdn2.microsoft.com/en-us/library/s7ee2dwt%28vs.71%29.aspx
For more general information about ADO.NET or Visual Basic .NET, refer to the following MSDN newsgroups:
http://msdn.microsoft.com/newsgroups/default.aspx?query=dotnet.framework.adonet&dg=&cat=en-us-msdn&lang=en&cr=US&pt=&catlist=774F24A2-F71F-425F-AC2B-DC48AB0DA5C9&dglist=&ptlist=&exp=&sloc=en-us

http://msdn.microsoft.com/newsgroups/default.aspx?query=dotnet.languages.vb&dg=&cat=en-us-msdn&lang=en&cr=US&pt=&catlist=774F24A2-F71F-425F-AC2B-DC48AB0DA5C9&dglist=&ptlist=&exp=&sloc=en-us
For more information, see the following book:
Wyke, R. Allen, and Sultan Rehman and Brad Leupen. http://www.microsoft.com/mspress/books/sampchap/4798.aspx. Microsoft Press, 2001.
For more information, see the following Microsoft Training & Certification course:
http://www.microsoft.com/learning/syllabi/en-us/2389Bfinal.mspx
For additional information about how to perform this task by using Microsoft Active Server Pages, click the article number below to view the article in the Microsoft Knowledge Base:
http://support.microsoft.com/kb/300488/EN-US  How To Run SQL Stored Procedures from an ASP Page

  aneesa replied to Neha .
21-Aug-11 11:31 PM

SqlCommand cmd = new SqlCommand("Select * from cshift where deleted='False'", con.OpenConnection());

SqlDataAdapter adp = new SqlDataAdapter(cmd);

DataTable dt = new DataTable();

adp.Fill(dt);

adp.Fill(dt);

  aneesa replied to Neha .
21-Aug-11 11:32 PM

DataTable dt = new DataTable();

con.Conn.Open();

SqlCommand cmd = new SqlCommand("FEECOLLECTEDALL", con.Conn);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@ACID", ddlacyear.SelectedValue);

cmd.Parameters.AddWithValue("@FROMDATE", ReadOnlyDate1.Text);

cmd.Parameters.AddWithValue("@TODATE", ReadOnlyDate2.Text);

cmd.Parameters.AddWithValue("@CLASSIDS", classid);

cmd.Parameters.AddWithValue("@TYPE", type);

cmd.Parameters.AddWithValue("@UID", ddluniv.SelectedValue);

SqlDataAdapter adp = new SqlDataAdapter(cmd);

con.Conn.Close();

adp.Fill(dt);

Create New Account
help
Frequently asked Interview Questions in ADO.Net hi friends Any one send frequently asked Important questions in C# .Net, ADO .Net, Asp .Net and Sql Server. . . . . . . . tx in Advance. . . . . . Hi, Find this. . (B)What is an IL? (B)What is a B) What is concept of Boxing and Unboxing ? (B) What is the difference between VB.NET and C#? (I) what is the difference between System exceptions and Application exceptions? (I)What
Interview Questions for .NET Framework This article is specially for the users those are in development or want to be a .net developer • To test a Web Service you must create a windows application or web application to consume this service? It is True / False? FALSE How many classes can a single.NET DLL contain? Answer1: As many Answer2: One or more What are good ADO.NET object(s) to replace the ADO Recordset object? The differences includes In ADO, the in memory representation of data is the recordset. In ADO.net, it is the dataset A recordset looks like a single table in ADO In contrast, a dataset is a collection of one or more tables in ADO.net ADO is designed primarily for connected access ADO net the disconnected access to the database is used In ADO you communicate with the database
Migration from ASP to ASP.net How to convert ASP site to ASP.NET site using C# http: / / www.asp.net / downloads / archived-v11 / migration-assistants / asp-to-aspnet hi, ASP.NET framework is very much different from unstrucured ASP and there is no correct way to