SQL Server Stored Procedure C# Windows form Application

How to create:
Simple Registration Form
We need to create a Windows form application and on submit button click we need to insert data's into Sql server Db using sql server stored procedure.
So what all the things we need.
Software Requirements.
We need visual studio for form development,SQL Server Management studio for Database operations.
First we will create a Database with name Test. in sql server management studio.
create a table with 5 columns
You can create it Manually or you can run the below script in your SQL SMStudio.

/*Your Code Starts Here*/

USE [Test]
GO

/****** Object:  Table [dbo].[tbl_login]    Script Date: 4/16/2013 12:09:51 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tbl_login1](
[Id] [int] IDENTITY(1,1) NOT NULL,
[firstname] [varchar](50) NULL,
[lastname] [varchar](50) NULL,
[username] [varchar](50) NULL,
[password] [varchar](50) NULL,
 CONSTRAINT [PK_tbl_login1] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

/* Code Ends Here */

Here Id is a primary key and its an auto incremental one.
Now we will create a stored procedure for Inserting data's into Table tbl_login.
How to create stored procedure in SQL Server.
Go to your database Inside programability>Storedprocedure> Right Click and create new procedure.

/* Your Code Starts Here */


USE [Test]
GO
/****** Object:  StoredProcedure [dbo].[Registration]    Script Date: 4/16/2013 12:15:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,TONY>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
Create PROCEDURE [dbo].[Registration]
@FirstName as varchar(50),
@Lastname as varchar(50),
@UserName as varchar(50),
@password as varchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    -- Insert statements for procedure here
Insert into  tbl_login(firstname,lastname,username,password)
values(@FirstName,@Lastname,@UserName,@password)
END

/* Code Ends Here */

Here we have created a sp with name Registration and we are passing 4(firstname,lastname,username,password) Input parameters and Inserting it into tbl_login

and we will call this sp in our Windows Application.

Now our database operation over.

Open visual studio and create a Windows Form Project  with c#.

Design your form like this. 

Add a App.configuration file to your project.

and write this code in your app.config file.

Sql Server Connection String in App.config file.

/* Your code starts here */

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

  <connectionStrings>

    <add  name="Connectionstring1" 

          connectionString="Data Source=Tonz-Pc; Initial Catalog=Test;Integrated Security=true"  providerName="System.Data.SqlClient"/>

  </connectionStrings>

</configuration>

/* Your code ends here */

here we have declare the connection string with name Connectionstring1, and database name Test

Double click on the submit  button and write this code inside click event

/* Code starts here */

 private void btnSubmit_Click(object sender, EventArgs e)

        {

            string username;

            string password;

            string firstname;

            string lastname;

            username = txtUserName.Text;

            password = txtPassword.Text;

            firstname = txtFirstName.Text;

            lastname = txtLastname.Text;

            Register(firstname, lastname, username, password);

        }

/* code ends here */

We are receiving  firstname, lastname, username, password from Form and setting it into declared variables.
and we are calling the Register Function and passing the 4 values into that

/* Code Starts Here */


  public void Register(string Fname, string Lname, string Uname, string paswd)
        {
            /* Assigning coonection string to 'constring' variable by calling getConnection function */
           string constring= getConnection();

            /* Declaring Connection Variable */
           SqlConnection con = new SqlConnection(constring);

            /* Checking Connection is Opend or not If its not open the Opens */
           if (con.State != ConnectionState.Open)
               con.Open();

            /* Calling Stored Procedure as SqlCommand */
           SqlCommand cmd = new SqlCommand("Registration", con);
           cmd.CommandType = CommandType.StoredProcedure;

            /* Passing Input Parameters with Command */
           cmd.Parameters.AddWithValue("@FirstName", Fname);
           cmd.Parameters["@FirstName"].Direction=ParameterDirection.Input;
           
           cmd.Parameters.AddWithValue("@Lastname",Lname);
           cmd.Parameters["@Lastname"].Direction=ParameterDirection.Input;

           cmd.Parameters.AddWithValue("@UserName",Uname);
           cmd.Parameters["@UserName"].Direction=ParameterDirection.Input;

           cmd.Parameters.AddWithValue("@password",paswd);
           cmd.Parameters["@password"].Direction = ParameterDirection.Input;

            /* Executing Stored Procedure */
           cmd.ExecuteNonQuery();
           con.Close();
            MessageBox.Show("Data Inserted Succesfully");

        }

/* Code Ends Here */

Get Connection function It will return connection String.

/* Code Starts here */


  public static string getConnection()
        {
            /*Reading Connection string from App.config File */
            string constr = ConfigurationManager.ConnectionStrings["Connectionstring1"].ConnectionString;
            return constr;
            //return true;
        }

/* Code Ends here */

You need to Import these References on top of your code additional to the default Libraries


using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;

Compile your project and on submit button click you will get "Data Inserted Succesfully" message if its inserted successfully.


Share This :