May 26, 2011

Code Sample to Insert Data in Database using SqlDataAdapter

 have a Student table in DB (id (AutoIncrement),FirstName,LastName)

Below is the sample code to insert data using SqlDataAdapter object's Updae Method



AddItemsInDatabase.aspx 

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


<!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>
     <asp:TextBox ID="txtFirstName" runat="server"></asp:TextBox><br />
     <asp:TextBox ID="txtLastName" runat="server"></asp:TextBox><br />
     
     <asp:Button ID="btnAdd" runat="server" Text="Button" onclick="btnAdd_Click" />
     
    
    </div>
   
    </form>
</body>
</html>


AddItemsInDatabase.aspx .cs


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


public partial class AddItemsInDatabase : System.Web.UI.Page
{


    SqlConnection connObj = new SqlConnection();
    DataSet ds = new DataSet();
    SqlCommand objCommand;
    SqlDataAdapter objAdapter;
    protected void Page_Load(object sender, EventArgs e)
    {
        connObj.ConnectionString = ConfigurationManager.ConnectionStrings["DotNetTrainingConnectionString"].ToString();
        connObj.Open();


        
    }
    protected void btnAdd_Click(object sender, EventArgs e)
    {
        SqlCommand objCommand = new SqlCommand("Select * from student", connObj);


        objAdapter = new SqlDataAdapter(objCommand);


        objAdapter.Fill(ds);




        DataRow dr;
        dr = ds.Tables[0].NewRow();
        dr[1] = txtFirstName.Text;
        dr[2] = txtLastName.Text;


        ds.Tables[0].Rows.Add(dr);


        ds.Tables[0].TableName = "student";


        SqlCommand comm = new SqlCommand("insert into student(FirstName,LastName) values(@FirstName,@LastName)", connObj);






        comm.Parameters.Add("@LastName", SqlDbType.VarChar, 255, "LastName");
        comm.Parameters.Add("@FirstName", SqlDbType.VarChar, 255, "FirstName");




        objAdapter.InsertCommand = comm;
        objAdapter.Update(ds, "student");




    }
}

Below is the DotNetTrainingConnectionString in Web.config File


<add name="DotNetTrainingConnectionString" connectionString="Data Source=.\sqlexpress;Initial Catalog=DotNetTraining;Integrated Security=True"
   providerName="System.Data.SqlClient" />

No comments:

Post a Comment