Socal Media Productions

Here is how you will execute a stored procedure in the code behind script in .Net.  The parameter you pass to the procedure can come from controls, forms, defined variable, etc... In this example I will use the profile class to grab my values.

 

First step is to define the connection string.  Mine happens to be stored in the web.config file.

 

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["localconnstring"].
ConnectionString);

Define the command that used your stored procedure.

        SqlCommand cmd = new SqlCommand("stroredprocedure", conn);
        cmd.CommandType = CommandType.StoredProcedure;

Define the parameters.

        SqlParameter pr1 = new SqlParameter("@p1", SqlDbType.NVarChar, 50);
        pr1.Value = Profile.p1;//this is where you will set the value of the parameter passed to the query.  I happened to be calling on the profile for this value.
        cmd.Parameters.Add(pr1);

        SqlParameter pr2 = new SqlParameter("@p2", SqlDbType.NVarChar, 10);
        pr2.Value = Profile.p2;
        cmd.Parameters.Add(pr2);

And open the connection.

        conn.Open();

Thats it!  Now you can fill the resulting data into a dataset and bind it to a gridview.

        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = cmd;

        DataSet ds = new DataSet();
        adapter.Fill(ds);

      //  DataRow drow = ds.Tables[0].Rows[0]; use this to get after the column values.

         GridView1.DataSource = ds;
         GridView1.DataBind();

 

First step to fill a dataset is to define the sql connection.  The method below uses the definition in the web.config fie which looks like this:

<connectionStrings>
      <clear />
  <add name="LocalSqlServer" connectionString="Server=192.169.1.1;Initial Catalog=DatabaseName;User ID=username;Password=password;"
   providerName="System.Data.SqlClient" />
</connectionStrings>

Once you have this defined in the config file then call on it by setting a string variable equal to the connectionstring.

String strCon1 = System.Configuration.ConfigurationManager.ConnectionStrings["LocalSqlServer"].
ConnectionString;

Define a container for this connection string.

SqlConnection conn1 = new SqlConnection(strCon1);

Open the connection.

conn1.Open();

Next we set the select statement and put it into a string variable.

string sql1 = "SELECT [ApplicationId],[UserId],[UserName],[LoweredUserName],[MobileAlias],
[IsAnonymous],[LastActivityDate]
FROM [DatabaseName].[dbo].[table]
where [IsAnonymous] = '0' order by [LastActivityDate] desc";

Ok and now the magical adapter which will allow us to grab all of the data in the query and place it into an object that we can do some cool things with. Just set the adapter variable and execute a select command on it using the two string we just defined.

SqlDataAdapter dadapter = new SqlDataAdapter();
dadapter.SelectCommand = new SqlCommand(sql1, conn1);

Now we fill the dataset with all of the returned data from the select command.  A great feature with this method is that the table structure(schema) is retained in the new dataset.

DataSet dset = new DataSet();
dadapter.Fill(dset);

Now you can call on the values in the dataset by using the following syntax.
                     
DataRow dr = dset.Tables[0].Rows[i] ;
dr["userid"].tostring();

Another usefull method is to add the data to a Datatable.  I find it easier to work with datatables when you need to adjust the table values and bind them to a gridview.

Datatable dt = new Datatable
dadapter.Fill(dt);            

 Good luck