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:
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:
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:
Clicking on the Save Button will insert the data to the database. Table a look at the Table below:
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)