In my previous article, I have demonstrated on how to add dynamic rows in GridView control with TextBoxes. Now, seems that most of the developers are asking if how to save all the data that was entered from the dynamic textbox in the GridView to the database. So in this example, I’m going to show on how to save them all in the database.

 

To get started then lets create a sample Table in SQL Server. In this example, I named the table as “SampleTable” with the following fields below:


SQL Server Database Table design



Note:

I set the Id to auto increment so that the id will be automatically generated for every new added row in the table. To do this select the Column name “Id” and in the column properties set the “Identity Specification” to yes.

 

Now let’s go ahead and proceed to ASPX source and add a Button for saving the data to the database. Take a look at the screen shot below:


ASP.Net GridView HTML Mark Up



Now let’s create the method for saving the data to the database. The first thing we need here is to set up the connection string so that we can connect to the Sql server from our codes. In this example we are going to use the web.config file for setting up the connection string. See the mark up below:

 

<connectionStrings>

            <add name="DBConnection" connectionString="Data Source=SERVERNAME\SQLEXPRESS;Initial Catalog=SampleDB;Integrated Security=SSPI;" providerName="System.Data.SqlClient"/>     

</connectionStrings>

 

Since the connection string is already set up then we now proceed in creating the method for saving the data to the database. Here are the code blocks below:

 

First, add the following namespaces below:

 

using System.Collections.Specialized;

using System.Text;

using System.Data.SqlClient;

 

We need to declare the namespaces above so that we can use the SqlClient, String Collections and StringBuilder built-in methods in our codes later.

 

Second, create the method for calling the connection strings that was set up in the web.config file.

 

    //A method that returns a string which calls the connection string from the web.config

    private string GetConnectionString()

    {

        //"DBConnection" is the name of the Connection String

        //that was set up from the web.config file

        return System.Configuration.ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;

    }

 

And here’s the code block for the Insert method:

 

//A method that Inserts the records to the database

    private void InsertRecords(StringCollection sc)

    {

        SqlConnection conn = new SqlConnection(GetConnectionString());

        StringBuilder sb = new StringBuilder(string.Empty);

        string[] splitItems = null;

        foreach (string item in sc)

        {

 

            const string sqlStatement = "INSERT INTO SampleTable (Column1,Column2,Column3) VALUES";

            if (item.Contains(","))

            {

                splitItems = item.Split(",".ToCharArray());

                sb.AppendFormat("{0}('{1}','{2}','{3}'); ", sqlStatement, splitItems[0], splitItems[1], splitItems[2]);

            }

 

        }

 

        try

        {

            conn.Open();

            SqlCommand cmd = new SqlCommand(sb.ToString(), conn);

            cmd.CommandType = CommandType.Text;

            cmd.ExecuteNonQuery();

 

           //Display a popup which indicates that the record was successfully inserted

            Page.ClientScript.RegisterClientScriptBlock(typeof(Page), "Script", "alert('Records Successfuly Saved!');", true);

 

        }

        catch (System.Data.SqlClient.SqlException ex)

        {

            string msg = "Insert Error:";

            msg += ex.Message;

            throw new Exception(msg);

 

        }

        finally

        {

            conn.Close();

        }

    }

 

Now, at Button Click event we can the method “InsertRecords” after extracting the dynamic TextBox values. To make it more clear then take a look at this code block below:

 

protected void Button1_Click(object sender, EventArgs e)

{

        int rowIndex = 0;

        StringCollection sc = new StringCollection();

        if (ViewState["CurrentTable"] != null)

        {

            DataTable dtCurrentTable = (DataTable)ViewState["CurrentTable"];

            if (dtCurrentTable.Rows.Count > 0)

            {

                for (int i = 1; i <= dtCurrentTable.Rows.Count; i++)

                {

                    //extract the TextBox values

                    TextBox box1 = (TextBox)Gridview1.Rows[rowIndex].Cells[1].FindControl("TextBox1");

                    TextBox box2 = (TextBox)Gridview1.Rows[rowIndex].Cells[2].FindControl("TextBox2");

                    TextBox box3 = (TextBox)Gridview1.Rows[rowIndex].Cells[3].FindControl("TextBox3");

 

                    //get the values from the TextBoxes

                    //then add it to the collections with a comma "," as the delimited values

                    sc.Add(box1.Text + "," + box2.Text + "," + box3.Text);

                    rowIndex++;

                }

                //Call the method for executing inserts

                InsertRecords(sc);

            }

        }

}

 

Running the code above will show something like below with the entered values in the TextBox:


ASP.Net GridView with Dynamic TextBoxes



Clicking on the Save Button will insert the data to the database. Table a look at the Table below:


ASP.Net GridView Records stored in SQL Server Database

That’s it! Hope you will find this example useful! You can download the sample using the link below.

Dynamic TextBox values from GridView to Database.zip (2.90 kb)