INTRODUCTION:
Hello friends !! In this article I will give a detailed explanation for storing the data in a database.This article is a continuation of my article Steps to create a Registration page in Asp.Net .I have shown how to create a basic registration page in my previous article and now I will help you all to store the Registered data in a database . The database I have used here is MSSQL server 2012 and Visual Studio 2013 .If you want to download and install any of these then you could refer my post Getting started with Asp.Net and SQL server
HOW TO CREATE A DATABASE IN SQL SERVER ???
Let me first guide you all to create a database and a table in SQL server
Follow the steps below...
STEP 1:
Open your SQL server Right click Database and Click New Database as shown below .Give your database a name .In this case I have given ProjectDb
After creating a database you can see it listed under Database.
STEP 2:
TO CREATE A NEW TABLE ...
Right click Table and click New Table. Don't change the number of rows ,columns or anything.Let it be as such.You will get a table as soon as you create !!
STEP 3:
Now you have to design your table.You have to specify the name of your column and other details.
Just type the contents as below.We are creating a database for storing the data from registration table !! In my previous article I have created a Registration table which has name,password,email,contact number so you have to create the exact number of columns which your registration page has !! My registration page has 4 fields and so I created the table with the same number of fields !!
You can give any name to the column and the DataType is the type of data you are going to store in the table
If you are going to store any data other than 'integer' then you can use the datatype as 'nvarchar' and the first column 'id' is to uniquely identify each user.Here I have allowed 'null' value to the contact number meaning while registering,the user can give the contact number or can leave that empty.
Just imagine,
You are doing registration for a Gmail account , Do we register with any 'id' ?? Do we tell the Gmail account that I am user number 500 .. or so?? Absolutely no !!
The database ,he himself will increment the count of users !
But how??
This is possible by 2 things ..
WHAT IS AN IDENTITY SPECIFICATION AND WHAT IT DOES???...
Consider a class in a College and your name as 'Cutie Pie' .There will be many students with a same name but what the staff will do actually??
The staff will enter,
Roll No. Name
1. Cutie pie
2. xyz
3. Cutie Pie
in the attendance register so, anyone can easily identify that CutiePie with a RollNo 1 is you and CutiePie with a RollNo 3 is someother ..!!
But we don't have any staff in the database to put Roll No for each and every user..!!
Then who is doing this breathtaking job?
Its none other Identify Specification ..!!
But we need primary key to be set !!
If we set a column with a 'Primary Key' then there can be no repetition in the column !!
Its for unique Identification and the column with a primary key cannot be 'null'
To which column do we need to set the primary key ??
We don't want the RollNo(for eg) in the registration table to be a repeated one and we don't want that to be null too so we can set 'id' column as 'Primary Key'
HOW TO SET PRIMARY KEY??
We need the 'id' column to be unique so Right click the column and click set Primary Key and that's it .You have set a Key for your column
As soon as you set up key for your column you can see the symbol in the appropriate column !!
HOW TO SET IDENTITY SPECIFICATION??
We need the id of the column to be automatically incremented so to do this,below the table you will see a list of properties.Select id column (because we need id to be automatically incremented)and select 'Identity Specification' and turn 'Is Identity' to Yes
Below that you can see Identity Increment .By default it will be '1' !! meaning all the 'id' will be incremented by 1.If you want you can change it to any number but ,at later stage you will be confused so its always better to have the increment by 1. !!
STEP 4:
Now you can save the table.Click Ctrl+s and you will get a popup like this .Give a meaningful name and save it !
IMPORTANT: Make sure you did all the changes before saving the database. Just for showing you all,I clicked 'null' for name after saving and I saved it again. If we try to do any changes to existing table it will throw out this error !!
Only way to get rid of this is to create the table again.Delete the existing one before saving the newly created one and refresh the table.
You can 'Refresh' by clicking the table and click refresh !!
As soon as you click refresh you will see the table listed under 'Tables' .If you wish to view it you can click 'Design' and your table will appear !!
JUST FOR YOUR INFO...
You can add the data in the table manually by Right clicking the table and select Edit top 200 rows as shown below and you will see the table with a values blinking brightly :)
<connectionStrings>
<add name="DBCS" connectionString="data source=.; database=ProjectDb; integrated security=SSPI"
providerName="System.Data.SqlClient" />
</connectionStrings>
Include this code in web.config file in visual studio.Its the first file that gets executed when we compile the project !!
Refer the image below..!!
data source= . specifies localhost .The '.' is nothing but localhost.You can even specify as (local) or local but in this I have explained with '.'
name="DBCS" It is nothing but the name of the connection.You can give any name as you wish .DBCS is database Connection String.!!
database=ProjectDb , It is the name of the database you wish to connect with a visual studio project.Remember ?? The database we created is ProjectDb and so I have given the same name here .!!
Make sure you type the project name here correctly else you will be getting damn errors !
Now, [spregistrationUsers] is the name of the stored procedure .Leave that dbo.It is auto generated
are parameters.We are passing the parameters to the RegistrationUserTbl (name of the table we created)
(name,password,email,contactnumber) are the column names.So,we are inserting the parameters to the column of the registration table !!
WHY DO WE NEED A STORED PROCEDURE ??
We can insert all these data from the visual studio directly without using Stored Procedure but just imagine at later stages we may create many tables and many web forms.For eg, If I wish to retrieve an email id in 2 web forms(say) I need to write coding for retrieving it 2 times in visual studio ..and similarly if I want to make any changes to the coding then I must update in 2 of my web forms which will be a hell time consuming !!
To help us,Stored Procedure came into existence.We can call the stored Procedure any number of times from the visual studio and If you wish to update your code then it is enough if you change it in stored procedure alone !!
As a developer,stored procedure helped me a lot and its easy too !! Just the same concept of passing parameters !
STEP 7:
TO PASS PARAMETERS FROM VISUAL STUDIO..
We need to store the data in the database as soon as the 'submit' button is clicked so, on the button click event place the code given below..
protected void Button1_Click(object sender, EventArgs e)
{
String cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("spregistrationUsers", con);
cmd.CommandType = CommandType.StoredProcedure;
string encryptedpassword = FormsAuthentication.HashPasswordForStoringInConfigFile(TextBoxPass.Text, "SHA1");
SqlParameter username = new SqlParameter("@name", TextBoxname.Text);
SqlParameter password = new SqlParameter("@password", encryptedpassword);
SqlParameter email = new SqlParameter("@email", TextBoxEmail.Text);
SqlParameter contactnumber = new SqlParameter("@contactnumber", TextBoxContact.Text);
cmd.Parameters.Add(username);
cmd.Parameters.Add(password);
cmd.Parameters.Add(email);
cmd.Parameters.Add(contactnumber);
con.Open();
cmd.ExecuteNonQuery();
Response.Redirect("~/Welcome.aspx", true);
con.Close();
}
}
CODE EXPLANATION..
SqlParameter username = new SqlParameter("@name", TextBoxname.Text)
SqlParameter password = new SqlParameter("@password", encryptedpassword);
These lines imply we are passing the values as a parameter
But from where these values come from??
They are coming from TextBoxname (id of the user name textbox) ,TextBoxEmail.Text etc are we are passing the encrypted value too !!
cmd.Parameters.Add(username);
cmd.Parameters.Add(password);
cmd.Parameters.Add(email);
cmd.Parameters.Add(contactnumber);
We are adding those parametes to the sql,opening the connection and closing it once done !!
Notice I have given Response.Redirect("~/Welcome.aspx", true); meaning on clicking the submit button If the registration is successful ,it will be redirected to Welcome page .!!
STEP 8:
Now that's it run the code and you can see the registration page as below !!
JavaScript works well as it throws out the error when I clicked submit without giving user name !! Test other cases also
As you can see Javascript is letting me to submit only if the form is in proper format !!
Open your SQL server Right click Database and Click New Database as shown below .Give your database a name .In this case I have given ProjectDb
After creating a database you can see it listed under Database.
STEP 2:
TO CREATE A NEW TABLE ...
Right click Table and click New Table. Don't change the number of rows ,columns or anything.Let it be as such.You will get a table as soon as you create !!
STEP 3:
Now you have to design your table.You have to specify the name of your column and other details.
Just type the contents as below.We are creating a database for storing the data from registration table !! In my previous article I have created a Registration table which has name,password,email,contact number so you have to create the exact number of columns which your registration page has !! My registration page has 4 fields and so I created the table with the same number of fields !!
You can give any name to the column and the DataType is the type of data you are going to store in the table
If you are going to store any data other than 'integer' then you can use the datatype as 'nvarchar' and the first column 'id' is to uniquely identify each user.Here I have allowed 'null' value to the contact number meaning while registering,the user can give the contact number or can leave that empty.
Just imagine,
You are doing registration for a Gmail account , Do we register with any 'id' ?? Do we tell the Gmail account that I am user number 500 .. or so?? Absolutely no !!
The database ,he himself will increment the count of users !
But how??
This is possible by 2 things ..
- By setting the primary Key
- By setting Identity specification
WHAT IS AN IDENTITY SPECIFICATION AND WHAT IT DOES???...
Consider a class in a College and your name as 'Cutie Pie' .There will be many students with a same name but what the staff will do actually??
The staff will enter,
Roll No. Name
1. Cutie pie
2. xyz
3. Cutie Pie
in the attendance register so, anyone can easily identify that CutiePie with a RollNo 1 is you and CutiePie with a RollNo 3 is someother ..!!
But we don't have any staff in the database to put Roll No for each and every user..!!
Then who is doing this breathtaking job?
Its none other Identify Specification ..!!
But we need primary key to be set !!
If we set a column with a 'Primary Key' then there can be no repetition in the column !!
Its for unique Identification and the column with a primary key cannot be 'null'
To which column do we need to set the primary key ??
We don't want the RollNo(for eg) in the registration table to be a repeated one and we don't want that to be null too so we can set 'id' column as 'Primary Key'
HOW TO SET PRIMARY KEY??
We need the 'id' column to be unique so Right click the column and click set Primary Key and that's it .You have set a Key for your column
As soon as you set up key for your column you can see the symbol in the appropriate column !!
HOW TO SET IDENTITY SPECIFICATION??
We need the id of the column to be automatically incremented so to do this,below the table you will see a list of properties.Select id column (because we need id to be automatically incremented)and select 'Identity Specification' and turn 'Is Identity' to Yes
Below that you can see Identity Increment .By default it will be '1' !! meaning all the 'id' will be incremented by 1.If you want you can change it to any number but ,at later stage you will be confused so its always better to have the increment by 1. !!
STEP 4:
Now you can save the table.Click Ctrl+s and you will get a popup like this .Give a meaningful name and save it !
IMPORTANT: Make sure you did all the changes before saving the database. Just for showing you all,I clicked 'null' for name after saving and I saved it again. If we try to do any changes to existing table it will throw out this error !!
Only way to get rid of this is to create the table again.Delete the existing one before saving the newly created one and refresh the table.
As soon as you click refresh you will see the table listed under 'Tables' .If you wish to view it you can click 'Design' and your table will appear !!
JUST FOR YOUR INFO...
You can add the data in the table manually by Right clicking the table and select Edit top 200 rows as shown below and you will see the table with a values blinking brightly :)
Whats now ..?? We have created our first friend database,successfully but let us make our first friend happy by introducing 'a new friend' named 'Visual studio project'
But why do we need our Visual studio project who is our second friend to be connected to our first friend ??
That's because they will be helpful to each other !! Visual studio says ..Hey friend just keep these data with you because you have a great memory capacity !!
But Visual studio isn't a stone hearted one ..! He is letting everyone know that my friend Database is helping me to store data and we are thick friends !! Pretty ..Isn't??
He is letting everyone know with the help of code below..
HOW TO CONNECT FIRST FRIEND WITH THE SECOND??
To connect the visual studio project with a database we need to include some code..
<add name="DBCS" connectionString="data source=.; database=ProjectDb; integrated security=SSPI"
providerName="System.Data.SqlClient" />
</connectionStrings>
Include this code in web.config file in visual studio.Its the first file that gets executed when we compile the project !!
Refer the image below..!!
data source= . specifies localhost .The '.' is nothing but localhost.You can even specify as (local) or local but in this I have explained with '.'
name="DBCS" It is nothing but the name of the connection.You can give any name as you wish .DBCS is database Connection String.!!
database=ProjectDb , It is the name of the database you wish to connect with a visual studio project.Remember ?? The database we created is ProjectDb and so I have given the same name here .!!
Make sure you type the project name here correctly else you will be getting damn errors !
So now we have established the connection.!! Visual studio and database are friends now :) !!
STEP 4:
Now we are going to create a Stored Procedure in database..!! I will tell you later why we actually need it !!
STEP 5:
Go to sql server under your database you will find Programmability .Expand that you will find Stored Procedure.Right click that and create a new one as below !!
Once you create a new one you will see the file as below !! Delete the marked lines as shown !!
STEP 6:
Remove the above codes and insert the following code
CREATE PROCEDURE [dbo].[spregistrationUsers]
@name nvarchar(500),
@password nvarchar(500),
@email nvarchar(500),
@contactnumber nvarchar(500)
as
Begin
Insert into RegistrationUserTbl(name,password,email,contactnumber) Values(@name,@password,@email,@contactnumber)
End
GO
@name nvarchar(500),
@password nvarchar(500),
@email nvarchar(500),
@contactnumber nvarchar(500)
(name,password,email,contactnumber) are the column names.So,we are inserting the parameters to the column of the registration table !!
WHY DO WE NEED A STORED PROCEDURE ??
We can insert all these data from the visual studio directly without using Stored Procedure but just imagine at later stages we may create many tables and many web forms.For eg, If I wish to retrieve an email id in 2 web forms(say) I need to write coding for retrieving it 2 times in visual studio ..and similarly if I want to make any changes to the coding then I must update in 2 of my web forms which will be a hell time consuming !!
To help us,Stored Procedure came into existence.We can call the stored Procedure any number of times from the visual studio and If you wish to update your code then it is enough if you change it in stored procedure alone !!
As a developer,stored procedure helped me a lot and its easy too !! Just the same concept of passing parameters !
STEP 7:
TO PASS PARAMETERS FROM VISUAL STUDIO..
We need to store the data in the database as soon as the 'submit' button is clicked so, on the button click event place the code given below..
protected void Button1_Click(object sender, EventArgs e)
{
String cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("spregistrationUsers", con);
cmd.CommandType = CommandType.StoredProcedure;
string encryptedpassword = FormsAuthentication.HashPasswordForStoringInConfigFile(TextBoxPass.Text, "SHA1");
SqlParameter username = new SqlParameter("@name", TextBoxname.Text);
SqlParameter password = new SqlParameter("@password", encryptedpassword);
SqlParameter email = new SqlParameter("@email", TextBoxEmail.Text);
SqlParameter contactnumber = new SqlParameter("@contactnumber", TextBoxContact.Text);
cmd.Parameters.Add(username);
cmd.Parameters.Add(password);
cmd.Parameters.Add(email);
cmd.Parameters.Add(contactnumber);
con.Open();
cmd.ExecuteNonQuery();
Response.Redirect("~/Welcome.aspx", true);
con.Close();
}
}
CODE EXPLANATION..
- ConnectionStrings["DBCS"] meaning we are connecting with DBCS (name of the connection we gave in web.config file)
- cmd.CommandType = CommandType.StoredProcedure; meaning we are going to use Stored Procedure instead of writing the codes directly in visual studio !!
- FormsAuthentication.HashPasswordForStoringInConfigFile(TextBoxPass.Text, "SHA1"); This line is to encrypt the password before storing in a database.
We are doing this because just imagine I am the admin.I am managing the database and you are entering password. Immediately, it will be stored in a database and wow !! I can see your lovely password .!! Admin may misuse it and so we must not let this happen at any cause .So its better to encrypt before storing and we are using SHA1 algorithm to encrypt it !!
I am not well versed with those algo still I know that we must use it :P
SqlParameter username = new SqlParameter("@name", TextBoxname.Text)
SqlParameter password = new SqlParameter("@password", encryptedpassword);
These lines imply we are passing the values as a parameter
But from where these values come from??
They are coming from TextBoxname (id of the user name textbox) ,TextBoxEmail.Text etc are we are passing the encrypted value too !!
cmd.Parameters.Add(username);
cmd.Parameters.Add(password);
cmd.Parameters.Add(email);
cmd.Parameters.Add(contactnumber);
We are adding those parametes to the sql,opening the connection and closing it once done !!
Notice I have given Response.Redirect("~/Welcome.aspx", true); meaning on clicking the submit button If the registration is successful ,it will be redirected to Welcome page .!!
STEP 8:
Now that's it run the code and you can see the registration page as below !!
JavaScript works well as it throws out the error when I clicked submit without giving user name !! Test other cases also
As you can see Javascript is letting me to submit only if the form is in proper format !!
As soon I clicked 'submit' I am navigated to welcome page !!
TO TEST WHETHER THE DATA HAS BEEN STORED IN DATABASE ...
To test,
Click new query editor window then type
Select *from RegistrationUserTbl (name of table we created)
select the full query and click execute !! You will find it at the top.See image below for details!!
As soon as you click execute you will find the list of registered users and note the password is been encrypted which is all we want !!
CONCLUSION:
That's it folks !! We have successfully stored the data in the database :) Please leave your valuable comments below and hit share button to share with your friends .!! Let them too be benefited .Hope you all like this article !! Thanks a lot for spending your precious time in reading this .Hope it helps you :) For any queries email me and I will be there to help you !! :)
No comments :
Post a Comment