Inserting values into database using ado.net

Susanthampy
Posted by in ADO.NET category on for Beginner level | Points: 250 | Views : 32730 red flag
Rating: 3.75 out of 5  
 4 vote(s)

In this article I am explaining about how to insert data into database using ado.net. This article is helpful for beginners.


 Download source code for Inserting values into database using ado.net

Introduction

For each web application, it contains a database for storing information. In ASP.NET we are commonly using SQL Server for back end. Microsoft .NET Framework provides ADO.NET for to access and modify data stored in relational database systems, though it can also access data in non-relational sources. ADO. NET is a collection of classes and tools it is build in to the .net framework It provides us powerful,reliable and scalable data driven application. ADO stands for Active data objects. 

ado.net










Steps to Insert Data into Sql server Database

  1. Set the connection string in the web.config file.

Connection string includes Server Name, Database Name, User Name and Password.(SQL Server authentication).

Example:(SQL Server Authentication)

<connectionStrings>

<add name="connString" connectionString="Server=TRAINA\SQLEXPRESS;Database=Susan; User ID=susan; Password=trNg*"/>

</connectionStrings>

Example: (Windows Authentication)

<connectionStrings>

<add name="connectionString" connectionString="Initial Catalog=MyDb;Data Source=MyServer;Integrated Security=SSPI;" />

</connectionStrings>

config file






  1. 2. Design a form and Take the code Behind file.

    Aspx Page(Design)

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Application._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>

            <table style="width: 100%;">

                <tr>

                    <td>

                        First Name

                    </td>

                    <td>

                        <asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox>

                    </td>

                    

                </tr>

                <tr>

                    <td>

                        Last Name

                    </td>

                    <td>

                        <asp:TextBox ID="txtLastName" runat="server"></asp:TextBox>

                    </td>

                    

                </tr>

                <tr>

                    <td>

                        Email

                    </td>

                    

                    <td>

                        <asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>

                    </td>

                </tr>

                <tr>

                <td colspan="2" align="center">

                    <asp:Button ID="btnSave" runat="server" Text="Save" onclick="btnSave_Click" /></td>

                </tr>

            </table>

        </div>

        </form>

    </body>

    </html>


Please import the following namespaces in the code behind file.

  1. using System.Data;
  2. using System.Data.SqlClient;
  3. using System.Configuration;

Establishing the connection between the database,

string ConnectionString = ConfigurationManager.ConnectionStrings[“connString”].ToString();

Using ConfigurationManager taking the value of the connection string from web.config file and holding the value in ConnectionString variable.

   public int InsertData()
        {
         
         string FirstName = txtFirstName.Text;
            string LastName = txtLastName.Text;
            string Email = txtEmail.Text;
            int inserted = 0;
           
                string QryString = "insert into Toys values('" + FirstName + "','" + LastName + "','" + Email + "')";
                SqlConnection conn = new SqlConnection(ConnectionString); 
                SqlCommand CmdObj = new SqlCommand(QryString, conn);
                conn.Open();
                inserted = CmdObj.ExecuteNonQuery();
                conn.Close();
                return inserted;
        }

We can call this function(InsertDate()) on Save Button's click event. Here I am using one method named InsertData() to insert data into database. Here we are inserting FirstName, LastName and Email. In Qrystring variable, storing the query for insertion of the above data. Then creating an object for SqlCommand and passing the Qrystring and ConnectionString to it. Then calling the ExecuteNonQuery() method. This method will returns the number of rows affected. That returned value is assigned to variable called inserted and the method(InsertData) will return that value. If this method executed successfully means the data is added to the database. You can check your table and verify. 


Conclusion


Insertion of data into database is a simple task. Simply follow these steps
1. Create Database. 
2. Set the connection string. 
3. Use Ado.net class and its method.  

Reference

Page copy protected against web site content infringement by Copyscape

About the Author

Susanthampy
Full Name: Susan Thampy
Member Level: Bronze
Member Status: Member,MVP
Member Since: 4/25/2011 3:55:56 AM
Country: India
Regards, Susan
http://www.dotnetfunda.com
I am completed my graduation in BSc Computer Science from MG University on 2010 . Currently I am working as a software Engineer in ASSYST International, Cochin, Kerala.

Login to vote for this post.

Comments or Responses

Posted by: CS1401 on: 7/7/2011 | Points: 25
Its really a tough one. Hey thanks. I searched it in google but i didn't get that. Really more complex to understand.. Thankx.. Hereafter all are know to insert data into their database.
Posted by: Susanthampy on: 7/7/2011 | Points: 25
Thnk u friend
Posted by: Certifiedkiller2000 on: 9/10/2011 | Points: 25
string QryString = "insert into Toys values('" + FirstName + "','" + LastName + "','" + Email + "')";

your query string is vulnerable to sql injection , so we can use parameters for this
u can follow the link

http://www.dotnetfunda.com/codes/code1728-insertion-using-parameters-.aspx

Login to post response

Comment using Facebook(Author doesn't get notification)