How to get output from stored procedure using ASP.Net


Few days ago found a member in ASP.Net forum complaining about, he don’t found any clear example to get stored procedure output parameter from c# code in the internet.

So I provide him the code and think to write it here ,For helping others. Its very straight forward I am giving the stored procedure here it will help to understand how to write storedprocedure to return output:

Create procedure PROC_ReturnValue
(
@id int,
@Name_Id varchar(100) Output
)
As

set @Name_Id=(Select (Convert(varchar(20),id)+name)
from Items where id=@id)

And the following c# code shows how to call the storedprocedure along with the parameter(also the output parameter). And after execution  of the stored procedure how to get the output value:

C# Code :

SqlConnection cn = new SqlConnection(“data source=dbServerName;database=dbTest;uid=admin;pwd=Pass@123”);
SqlCommand cm = new SqlCommand();
cm.Connection = cn;

cm.CommandType = CommandType.StoredProcedure;
cm.CommandText = “PROC_ReturnValue”;
cm.Parameters.AddWithValue(“@id”, 6);

     //Adding the output parameter
        cm.Parameters.AddWithValue(“@Name_Id”,string.Empty);
        cm.Parameters[1].SqlDbType = SqlDbType.VarChar;
        cm.Parameters[1].Direction = ParameterDirection.Output;
        cm.Parameters[1].Size = 100;

cn.Open();

SqlDataReader dr= cm.ExecuteReader();

        //Accessing the output parameter value

      string sOutPut= cm.Parameters[1].value;
cn.Close();

How to dynamically add template field of type textbox in ASP.Net gridview / How to get value from dynamically added template field of type textbox


I was working to make a utility tool to use for myself. This required having a gridview where user will input data and save. Here the no of input columns to be added was not fixed and was depends on some calculation at runtime. So I decided to dynamically add template field of type textbox in the gridview and save the user input when clicking save button.

I would describe the process that I followed to achieve this task in below:

The UI code is like below:

<div style=”padding-left: 20px; float: left”>
        <div style=”float: left; width: 100px; padding-top: 5px”>
            Formula</div>
        <div style=”float: left”>
            <asp:DropDownList ID=”drpFormula” runat=”server” OnSelectedIndexChanged=”drpFormula_SelectedIndexChanged”>
            </asp:DropDownList>
        </div>
        <div style=”padding-top: 5px”>
            <asp:Button ID=”btnSave” Width=”100px” runat=”server” Text=”Save” OnClick=”btnSave_Click” />
        </div>
        <div style=”height: 30px”>
        </div>
        <div style=”text-align: center; background-color: #F0F8FF;”>
            <b>List of Member and Attribute mapping</b></div>
        <div style=”padding-top: 20px”>
        </div>
        <asp:GridView ID=”gvMemberAttributeMap” runat=”server” EmptyDataText=”No Data Found”
            AutoGenerateColumns=”False” CellPadding=”4″ ForeColor=”#333333″ Width=”525px”
            ShowHeaderWhenEmpty=”True”>
            <AlternatingRowStyle BackColor=”White” ForeColor=”#284775″ />
            <EditRowStyle BackColor=”#999999″ />
            <FooterStyle BackColor=”#5D7B9D” Font-Bold=”True” ForeColor=”White” />
            <HeaderStyle BackColor=”#5D7B9D” Font-Bold=”True” ForeColor=”White” />
            <PagerStyle BackColor=”#284775″ ForeColor=”White” HorizontalAlign=”Center” />
            <RowStyle BackColor=”#F7F6F3″ ForeColor=”#333333″ />
            <SelectedRowStyle BackColor=”#E2DED6″ Font-Bold=”True” ForeColor=”#333333″ />
            <SortedAscendingCellStyle BackColor=”#E9E7E2″ />
            <SortedAscendingHeaderStyle BackColor=”#506C8C” />
            <SortedDescendingCellStyle BackColor=”#FFFDF8″ />
            <SortedDescendingHeaderStyle BackColor=”#6F8DAE” />
        </asp:GridView>
    </div>

Now whenever user chooses a formula, I have added columns and data in following way:

New template object added like below:

public class GridViewTemplate : ITemplate
{
//A variable to hold the type of ListItemType.
ListItemType _templateType;

//A variable to hold the column name.
string _columnName;

//Constructor where we define the template type and column name.
public GridViewTemplate(ListItemType type, string colname)
{
//Stores the template type.
_templateType = type;

//Stores the column name.
_columnName = colname;
}

void ITemplate.InstantiateIn(System.Web.UI.Control container)
{
switch (_templateType)
{
case ListItemType.Header:

//Creates a new label control and add it to the container.
Label lbl = new Label();            //Allocates the new label object.
lbl.Text = _columnName;             //Assigns the name of the column in the lable.
container.Controls.Add(lbl);        //Adds the newly created label control to the container.

break;

case ListItemType.Item:
//Creates a new text box control and add it to the container.
TextBox tb1 = new TextBox();                            //Allocates the new text box object.
//tb1.ID = “txtItem”;
tb1.DataBinding += new EventHandler(tb1_DataBinding);   //Attaches the data binding event.
tb1.Columns = 4;                                        //Creates a column with size 4.

container.Controls.Add(tb1);                            //Adds the newly created textbox to the container.

break;

case ListItemType.EditItem:
//Creates a new text box control and add it to the container.
TextBox tb2 = new TextBox();
//Allocates the new text box object.
tb2.DataBinding += new EventHandler(tb1_DataBinding);   //Attaches the data binding event.
tb2.Columns = 4;                                        //Creates a column with size 4.

container.Controls.Add(tb2);
//As, I am not using any EditItem, I didnot added any code here.
break;

case ListItemType.Footer:

CheckBox chkColumn = new CheckBox();
chkColumn.ID = “Chk” + _columnName;
container.Controls.Add(chkColumn);

break;

}

}

/// <summary>
/// This is the event, which will be raised when the binding happens.
/// </summary>
/// <param name=”sender”></param>
/// <param name=”e”></param>
void tb1_DataBinding(object sender, EventArgs e)
{
TextBox txtdata = (TextBox)sender;
GridViewRow container = (GridViewRow)txtdata.NamingContainer;
object dataValue = DataBinder.Eval(container.DataItem, _columnName);

if (dataValue != DBNull.Value)
{
txtdata.Text = dataValue.ToString();
}
}

}

Gridview with the above template filed loads by the following way:

         /// <summary>
        /// Selected index change event handler for formula
        /// </summary>
        /// <param name=”sender”></param>
        /// <param name=”e”></param>
        protected void cmbFormula_SelectedIndexChanged(object sender, EventArgs e)
        {
            Guid gFormulaId = new Guid(cmbFormula.SelectedValue);
            BindDataToGrid(gFormulaId);
        }

        /// <summary>
        /// This method load the Gridview for the selected formula
        /// </summary>
        /// <param name=” gFormulaId”></param>
        void BindDataToGrid(Guid gFormulaId)
        {
            DataTable dt = DistributionManagement.GetAllMemberAttributeMapForFormula(gFormulaId);

            int i = 1;

            gvMemberAttributeMap.Columns.Clear();

            foreach (DataColumn dc in dt.Columns)
            {
                TemplateField tempField = new TemplateField();
                tempField.HeaderTemplate = new GridViewTemplate(ListItemType.Header, dc.ColumnName);
                tempField.ItemTemplate = new GridViewTemplate(ListItemType.EditItem, dc.ColumnName);

                BoundField boundField = new BoundField();
                boundField.DataField = dc.ColumnName;
                boundField.HeaderText = dc.ColumnName;

                /*For even order column add the bound filed and hide that column*/
                if (i % 2 != 0)
                {
                    gvMemberAttributeMap.Columns.Add(boundField);
                    boundField.HeaderStyle.CssClass = “hide”;
                    boundField.ItemStyle.CssClass = “hide”;

                }
                /*For odd order(including the first) column add the template filed*/
                else
                {
                    if (i == 2)
                    {
                        gvMemberAttributeMap.Columns.Add(boundField);
                    }
                    else
                    {
                        gvMemberAttributeMap.Columns.Add(tempField);
                    }
                }

                i++;
            }

            gvMemberAttributeMap.DataSource = dt;
            gvMemberAttributeMap.DataBind();

            Session[“CurrentSource”] = dt;

        }

        /// <summary>
        /// Save button click event handler
        /// </summary>
        /// <param name=”sender”></param>
        /// <param name=”e”></param>
        protected void btnSave_Click(object sender, EventArgs e)
        {
            Guid gFormulaId = new Guid(cmbFormula.SelectedValue);

            List<MemberAttributeMapp> lstMemberAttributeMap = new List<MemberAttributeMapp>();
            MemberAttributeMapp oMemberAttributeMap = null;

            if (gFormulaId.Equals(Guid.Empty))
            {
                return;
            }

           // BindDataToGrid(gFormulaId);

            foreach (GridViewRow gRow in gvMemberAttributeMap.Rows)
            {
                int iNoOfAttribute = gRow.Cells.Count;
                Guid gMemberId = new Guid(gRow.Cells[0].Text);

                for (int i = 2; i < iNoOfAttribute; i += 2)
                {
                    oMemberAttributeMap = new MemberAttributeMapp();
                    oMemberAttributeMap.ID = Guid.NewGuid();
                    oMemberAttributeMap.FormulaId = gFormulaId;
                    oMemberAttributeMap.MemberId = gMemberId;

                    oMemberAttributeMap.AttributeId = new Guid(gvMemberAttributeMap.Columns[i].HeaderText);
                    oMemberAttributeMap.Weight = Convert.ToDecimal(((System.Web.UI.WebControls.TextBox)gRow.Cells[i+1].Controls[0]).Text);

                    lstMemberAttributeMap.Add(oMemberAttributeMap);
                }

            }

            DistributionManagement.SaveAllMemberAttributeMapForFormula(lstMemberAttributeMap, gFormulaId);

            BindDataToGrid(gFormulaId);
        }

 Now Gridview loads successfully upon selected index change of formula dropdown.

GridView view after adding dynamic template field of textbox type

But when clicking the save button it throws exception in the line

oMemberAttributeMap.Weight = Convert.ToDecimal(((System.Web.UI.WebControls.TextBox)gRow.Cells[i+1].Controls[0]).Text);

 And upon debugging I found that it doesn’t contains the added template TextBox control.

Try lots of different way and found the addition of CreateChildControls() event solves my problem and it work fine. The CreateChildControls() is like below for my task :

/*Addds columns and Binds the gridview with the current session data*/

 protected override void CreateChildControls()
        {
            base.CreateChildControls();

            if (Session[“CurrentSource”] != null && !cmbFormula.SelectedValue.Equals(Guid.Empty.ToString()))
            {
                DataTable dt = (DataTable)Session[“CurrentSource”];

                gvMemberAttributeMap.Columns.Clear();
                int i = 1;

                foreach (DataColumn dc in dt.Columns)
                {
                    TemplateField tempField = new TemplateField();
                    tempField.HeaderTemplate = new GridViewTemplate(ListItemType.Header, dc.ColumnName.TrimEnd());
                    tempField.ItemTemplate = new GridViewTemplate(ListItemType.EditItem, dc.ColumnName.TrimEnd());

                    BoundField boundField = new BoundField();
                    boundField.DataField = dc.ColumnName;
                    boundField.HeaderText = dc.ColumnName;

                    if (i % 2 != 0)
                    {
                        gvMemberAttributeMap.Columns.Add(boundField);
                        boundField.HeaderStyle.CssClass = “hide”;
                        boundField.ItemStyle.CssClass = “hide”;

                    }
                    else
                    {
                        if (i == 2)
                        {
                            gvMemberAttributeMap.Columns.Add(boundField);
                        }
                        else
                        {
                            gvMemberAttributeMap.Columns.Add(tempField);
                        }
                    }

                    i++;
                }

                gvMemberAttributeMap.DataSource = dt;
                gvMemberAttributeMap.DataBind();
            }