How to use a DataAdapter with stored procedure and parameter

Question

I want to fill a DataGridView control using DataAdapter  But I don t know how to do it since I m using a stored procedure with parameter  Can someone cite an example please

User · Answer

SqlConnection con   new SqlConnection   Some Connection String     SqlDataAdapter da   new SqlDataAdapter  ParaEmp Select  con               da SelectCommand CommandType   CommandType StoredProcedure              da SelectCommand Parameters Add   Contactid   SqlDbType Int  Value   123              DataTable dt   new DataTable                da Fill dt               dataGridView1 DataSource   dt

User · Answer

public DataSet Myfunction string Myparameter                config cmd Connection   config cnx          config cmd CommandText    ProcName           config cmd CommandType   CommandType StoredProcedure          config cmd Parameters Add  parameter   SqlDbType VarChar  10           config cmd Parameters  parameter   Value   Myparameter           config dRadio   new SqlDataAdapter config cmd           config dRadio Fill config ds  Table             return config ds

User · Answer

I got it    hehe  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

User · Answer

Maybe your code is missing this line from the Microsoft example   MyDataAdapter SelectCommand CommandType   CommandType StoredProcedure

User · Answer

SqlConnection con   new SqlConnection   Some Connection String     connection object SqlDataAdapter da   new SqlDataAdapter  ParaEmp Select  con    SqlDataAdapter class object da SelectCommand CommandType   CommandType StoredProcedure    command sype da SelectCommand Parameters Add   Contactid   SqlDbType Int  Value   123    pass perametter DataTable dt   new DataTable       dataset class object da Fill dt     call the stored producer

User · Answer

Short and sweet     DataTable dataTable   new DataTable    try      using  var adapter   new SqlDataAdapter  StoredProcedureName   ConnectionString               adapter SelectCommand CommandType   CommandType StoredProcedure         adapter SelectCommand Parameters Add   ParameterName   SqlDbType Int  Value   123         adapter Fill dataTable           catch  Exception ex        Logger Error  Error occured while fetching records from SQL server   ex

User · Answer

SqlConnectionStringBuilder builder   new SqlConnectionStringBuilder            builder DataSource    lt sql server name gt           builder UserID    lt user id gt     User id used to login into SQL         builder Password    lt password gt     password used to login into SQL         builder InitialCatalog    lt database name gt     Name of Database          DataTable orderTable   new DataTable                lt sp name gt  stored procedute name which you want to exceute         using  var con   new SqlConnection builder ConnectionString           using  SqlCommand cmd   new SqlCommand  lt sp name gt   con            using  var da   new SqlDataAdapter cmd                         cmd CommandType   System Data CommandType StoredProcedure                Data adapter da  fills the data retuned from stored procedure               into orderTable             da Fill orderTable

User · Answer

public class SQLCon     public static string cs       ConfigurationManager ConnectionStrings  DefaultConnection   ConnectionString    protected void Page Load object sender  EventArgs e        SqlDataAdapter MyDataAdapter      SQLCon cs   new SQLCon        DataSet RsUser   new DataSet        RsUser   new DataSet        using  SqlConnection MyConnection   new SqlConnection SQLCon cs                    MyConnection Open            MyDataAdapter   new SqlDataAdapter  GetAPPID   MyConnection              Set the command type as StoredProcedure          MyDataAdapter SelectCommand CommandType   CommandType StoredProcedure          RsUser   new DataSet            MyDataAdapter SelectCommand Parameters Add new SqlParameter   organizationID            SqlDbType Int            MyDataAdapter SelectCommand Parameters   organizationID   Value   TxtID Text          MyDataAdapter Fill RsUser   GetAPPID                    if  RsUser Tables 0  Rows Count  gt  0    data was found                 Session  AppID     RsUser Tables 0  Rows 0   AppID   ToString                  else

User · Answer

Here we go   DataSet ds   new DataSet    SqlCommand cmd   new SqlCommand    cmd Connection   con    database connection cmd CommandText    WRITE STORED PROC NAME       Stored procedure name cmd CommandType   CommandType StoredProcedure     set it to stored proc   add parameter if necessary cmd Parameters Add   userId   SqlDbType Int  Value   courseid   SqlDataAdapter adap   new SqlDataAdapter cmd   adap Fill ds   Course    return ds

[c#] How to use a DataAdapter with stored procedure and parameter

Examples related to c#

Examples related to stored-procedures

Examples related to dataadapter