Asp.net programming we consider all important topic for knowledge about controls,web Services, JSON, Ajax, database, and interview related Question. In this Asp.net and sql server tutorial blog we give many examples of jquery as like How Play YouTube Video in Your Asp.net Website by URL, How to Play YouTube Videos in Asp.net by using Jquery , Jquery Change div background on mouseover in asp.net , JQuery UI Datepicker (Calendar) etc.

Friday, December 5, 2014

How to insert value in database by call SQL stored procedures in ASP.NET page

SQL Server Stored Procedures for insert values 

in database using ASP.Net Example:



Call SQL stored procedures in ASP.NET page:

In this post we take an example of insert data in to database by the help of store procedure in asp.net and also learn how to call store procedure in C# code page. 

In this post we want to create a sample example for this we first create a table.

Now when we create my table in data base then we want to write the store procedure on data base. Some time the developer makes store procedure according to the table. Means every table have separate store procedure.

How to create a store procedure for insert in to data base table:

Here we create a store procedure in you want to learn more about sql server store procedure then click here.

CREATE PROCEDURE Proc_Mytest
@UserID int=NULL,
@UserName varchar(50)=null,
@Password varchar(50)=null,
@Address varchar(max)=null,
@QueryType varchar(50)=null
As
BEGIN

if(@QueryType='Insert')
begin
insert into Table_1(UserName,Password,Address)values(@UserName,@Password,@Address)
end
     
END
Here we make a store procedure for the table_1.  And give the query of insert the record into the table.

Now we make an asp.net web application and save some record by user input. For this first we want to create the connection.

Write the connection string into web.config file:

<connectionStrings>
    <add name="Myconnection" connectionString="Data Source=<server name>;Initial Catalog=Mytest;Integrated Security=True;"  providerName="System.Data.SqlClient"/>
  </connectionStrings>

create a web page in asp.net  with C#.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div align="center">
        <table>
            <tr>
                <td>
                    User Name :
                </td>
                <td>
                    <asp:TextBox ID="TxtUserName" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    Password :
                </td>
                <td>
                    <asp:TextBox ID="TxtPassword" runat="server" TextMode="Password"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    Address :
                </td>
                <td>
                    <asp:TextBox ID="TxtAddress" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                </td>
                <td>
                    <asp:Button ID="Submit" runat="server" Text="Submit" onclick="Submit_Click" />
                    <asp:Label ID="lblMsg" runat="server" ForeColor="Red" Text=""></asp:Label>
                </td>
            </tr>
        </table>
        <div>
</div>
    </div>
    </form>
</body>
</html>

In this page we take some text field and a button control, now we want to fill the text field and when click submit button the data will be store in to database by store procedure.

Store procedure on .asps.cs page for insert data:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

public partial class _Default : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["Myconnection"].ConnectionString);
    SqlCommand cmd;
    DataSet ds;
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {

        }
    }
    protected void Submit_Click(object sender, EventArgs e)
    {
        try
        {
            con.Open();
            cmd = new SqlCommand("Proc_Mytest",con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@UserName",TxtUserName.Text);
            cmd.Parameters.AddWithValue("@Password", TxtPassword.Text);
            cmd.Parameters.AddWithValue("@Address", TxtAddress.Text);
            cmd.Parameters.AddWithValue("@QueryType", "Insert");
            cmd.ExecuteNonQuery();
            lblMsg.Text = "data store in to data base";
           // SqlDataAdapter adp = new SqlDataAdapter(cmd);
            //adp.Fill(ds);

        }
       
       
        catch (Exception ex)
        {
           
           
        }
    }
}

The next post we try to fetch the data from data base by the help of store procedure.


Sql-Server Related post:





No comments:

Post a Comment