An Effective Way To Store Some Values In Database In ASP.NET

In this tutorial, we will learn a way to store some specific data at one instance of button click in Datatable; and on another instance of button click, we will store some other data to our Datatable. The approach we are using here is something like- we had two Views. On first View, we will save the name and city, and on the other View, we will save course and semester in our same datatable.

INITIAL CHAMBER

Step 1

Open your Visual Studio 2010 and create an Empty Website. Give a suitable name [data_demo].

Step 2

In Solution Explorer, you get your empty website. Add a web form and SQL Database by going like this –

For Web Form

data_demo (Your Empty Website) -> Right Click -> Add New Item -> Web Form. Name it as -> data_demo.aspx.

For SQL Server Database

data_demo (Your Empty Website) -> Right Click -> Add New Item -> SQL Server Database. [Add Database inside the App_Data_folder].

DATABASE CHAMBER

Step 3

Get to your Database [Database.mdf], we will create a table - -> tbl_data1. Go to the database.mdf - -> Table - -> Add New table, and design your table like this.

Relevant Store Procedure - sp_insertdata 

CREATE PROCEDURE sp_inserdata
(


    @id int output,
    @name varchar(50)= 'Nilesh',
    @city varchar(50)= 'Rajkot'
)
AS

insert into tbl_data1 (name,city) values (@name,@city)

select @id = id from tbl_data1 where name=@name and city=@city

RETURN

We cannot save all the data at one time, our requirement is to save two of the data fields at one instance, and other two at other instance on button click. Also, we want to save these 4 data fields in one single table. Even if you go with a traditional way of inserting, it will show you null values in your datatable.

With a traditional way, on the first save- course and sem will be shown as null values, but let’s say you don’t want to add these null values, you need to enter the default values, at that time I guess you will take another field in your view, yes it is a simple approach as we are here storing 4 datafields, but we are talking here for large data let’s say 100-200 at that time too if you add these fields than it is not a reliable way to do it.

We don’t need any null values, so how to do it? We will provide an insert store procedure, where we will return the table id, with the help of sqlparameter class, we will store that id value in the first view. In the second view we will not write any insert query instead we will just update the datafield with the help of the same id.

DESIGN CHAMBER

Step 4

Open your data_demo.aspx file to create our design, we will drag a Mutiview Control> Inside that, take two Viewa as Student Details and Student Course.

Data_demo.aspx

CODE CHAMBER

Step 5

Open data_demo.aspx.cs file to write code for saving the partial data in our database table. 

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

public partial class Multiview : System.Web.UI.Page
{
    static int id1 = 0;
    SqlConnection con = new SqlConnection(@"Data Source=Nilesh;Initial Catalog=test_db;Integrated Security=True");
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            MultiView1.ActiveViewIndex = 0;
        }

    }

    protected void Button2_Click(object sender, EventArgs e)
    {
        MultiView1.ActiveViewIndex = 1;


        SqlCommand cmd = new SqlCommand("sp_insert1", con);

        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter outpara = new SqlParameter("@id", SqlDbType.Int);
        outpara.ParameterName = "@id";
        outpara.SqlDbType = System.Data.SqlDbType.Int;
        outpara.Direction = System.Data.ParameterDirection.Output;
        cmd.Parameters.Add(outpara);
        cmd.Parameters.AddWithValue("@name", textname.Text);
        cmd.Parameters.AddWithValue("@city", textcity.Text);
        con.Open();
        cmd.ExecuteNonQuery();
        id1 = Convert.ToInt32(outpara.Value is DBNull ? default(int) : outpara.Value);
        con.Close();
        refreshdata();

    }

    public void refreshdata()
    {
        SqlCommand cmd = new SqlCommand("select * from tbl_stu", con);
        SqlDataAdapter sda = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        sda.Fill(dt);
        GridView1.DataSource = dt;
        GridView1.DataBind();

    }

    protected void Button4_Click(object sender, EventArgs e)
    {
        MultiView1.ActiveViewIndex = 2;

    }

    protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        GridView1.EditIndex = -1;
        refreshdata();

    }

    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
       int id = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values["id"].ToString());
        con.Open();
        SqlCommand cmd = new SqlCommand("delete from tbl_stu where id =@id", con);
        cmd.Parameters.AddWithValue("id", id);
        int i = cmd.ExecuteNonQuery();
        con.Close();
        refreshdata();

    }

    protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
    {
        GridView1.EditIndex = e.NewEditIndex;
        refreshdata();

    }

    protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        TextBox txtname = GridView1.Rows[e.RowIndex].FindControl("TextBox4") as TextBox;
        TextBox txtcity = GridView1.Rows[e.RowIndex].FindControl("TextBox5") as TextBox;
        TextBox txtcourse = GridView1.Rows[e.RowIndex].FindControl("TextBox1") as TextBox;
        TextBox txtsem = GridView1.Rows[e.RowIndex].FindControl("TextBox2") as TextBox;
        int id = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values["id"].ToString());
        con.Open();
        SqlCommand cmd = new SqlCommand("sp_update", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("name", txtname.Text);
        cmd.Parameters.AddWithValue("city", txtcity.Text);
        cmd.Parameters.AddWithValue("course", txtcourse.Text);
        cmd.Parameters.AddWithValue("sem", txtsem.Text);
        cmd.Parameters.AddWithValue("id", id);

        int i = cmd.ExecuteNonQuery();
        con.Close();
        GridView1.EditIndex = -1;
        refreshdata();

    }

    protected void Button3_Click(object sender, EventArgs e)
    {
        MultiView1.ActiveViewIndex = 2;
        SqlCommand cmd = new SqlCommand("update tbl_stu set course= @course,sem= @sem where id =" + id1.ToString(), con);
        cmd.Parameters.AddWithValue("@course", TextBox6.Text);
        cmd.Parameters.AddWithValue("@sem", TextBox7.Text);
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
        refreshdata();

    }

    protected void Button4_Click1(object sender, EventArgs e)
    {
        MultiView1.ActiveViewIndex = 0;
    }
}

OUTPUT CHAMBER

Okay! Here, we will insert name and city from the first View and see if it is saving in the database or not.

First View


Tbl_data - Name and City

Second View

Okay, it is working fine,  and saving all the data at one time. Hope you like it. Thank you for reading.