How to save the data for multiple checked items in a Grid to your Database
When we are programming in .Net.We come across the scenario (i.e)we need to store the selected items in a grid .Some times the selected items are more for saving the data into database.It become a tedious way of storing the data in database.Because looping the selected items and inserting the data row by row in database will lead to stuck or unresponsive of the application for the end user.for this Scenario i had a better way of handling the things.This is the one I want to discuss in this Article.
Generally this type of Scenarios will be there in Medical Domain Projects.Where the patient information or blood or Urine samples collected in a test lab and other activities that are viewed and selected in a grid by the User and save it into database.If the programmer writes a code by looping those selected items in a grid and inserting it into the remote database. Assume that if the grid has 100 or 1000 selected items in a grid.It will take really long time to save the selected items of the grid.And some times the Sql-server connection will break down and that leads to unresponsive of the application will be more on cards.So in order to save such data in database the sql-server with the combination of .net Plays a vital role in handling these things .
I will brief out the following steps :
There are two ways to handle this issue..
Ex: I am creating an address for a table in which it contains following fields and datatypes.Create Table Address (Name varchar(10),Full Name varchar(40),Street varchar(40),City varchar(10),State varchar(30) )
So with the datatable the grid was bound and we need to save into database for the checked items in a grid.
There are two ways of writing query in sql-server 1st Method of query :
Using Sql server(Retrieve only necessary column Name) query: SELECT Column_Name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Address'
2 nd Method of query using Sql-server :
Sp_columns table name
With Out Using Sqlserver use datagrid columns
for(int i=0;i<dggrid.columns.count;i++)
{
// write a code adding columns to the datatable using datagrid columns But the column name should exactly match with the database columns otherwise we need to map the columns using sqlbulkcopymapping. And loop will be varied because of the checkbox item or use foreach loop
}
After successfully retriving the data from the sqlserver or from the Grid fill into datatable than the rows of the datatable will filled with Column names .Take another datatable change the rows(Column names) in to columns of the orginal table
Now loop the selected items in the grid where check boxes are selected or datagrid items.
Datarow []dr;
foreach(Datagriditem dgi in Datagrid.items)
{
// Check for the check box if it is a checkbox checked
If(checked)
{
dr = datatable.newrow();
dr[0]= get the data from the grid;
dr[1]=Fullname column data from the grid;
………
add datarow to the adressdatatable
}
}
Now we come to the final part that is inserting in to the database by using sqlBulkcopy you can use the sqltransaction in the constructor of a bulk copy
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(destinationConnection.ConnectionString))
{
bulkCopy.BatchSize = 500;//not Mandatory
bulkCopy.NotifyAfter = 1000;//not Mandatory
bulkCopy.DestinationTableName = "Address";
bulkCopy.WriteToServer(yourdatatable);
}
Note :This code will also be useful to save Excel sheet data in your database
What are the Advantages of using this type of code ?
1)I eliminate the multiple times of hitting the database after clicking button_save
2)The Columns are fetched from the database so that You will not get any wrong column notation like(spelling mistake of a column,extra space added to a column). e.t.c
3)If you modify a column in a table or added a new column of the table in the database than it is very easy for maintenance of your code
Ex:Say a new Pin code Column is added in the Address table you just Mention the code in your coding dr[position of the column]= data; (or) you just Modify a spelling column or change the column name of the existed column in the database table than there is no need of changing any code.Because we are fetching the columns from the database
4)The important one is sqlbulkmappings. If we use all the columns in a shown grid to insert the data that exactly matches to the database columns than there is no need of Bulkcopymappins.SQLBULKCOPYMAPPINGS
Consider a scenario that this Address datatable information i want insert in the patient information
so i am creating table for Patient information
Create table patientinfo
(
P_Id int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
Now the most complicated task is i want to insert the data which is in the address table to patient information table.
but the ordinal_positions are of the address table are different from the ordinal postions of the patientinfotable.So for that i will write a query like thisquery: SELECT Column_Name,Ordinal_Positions FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'patientinfotable'
Fill the data in the datatable with this above query using adapter
So you manually eliminate the mappings.. like here
bulkCopy.ColumnMappings.Add("ProductID", "ProdID");
bulkCopy.ColumnMappings.Add("Name", "ProdName");
bulkCopy.ColumnMappings.Add("ProductNumber", "ProdNum");
Now my Code will loop the fetched address table of the information_schema query.Fetch the information_schema of the parent table in another data table. check the column exists in the patient table against the adresstable
if found add to the bulk copy mappings either by thier column name Mappings or Column Ordinal Positions.
Need not manually search each column Dynamically we can add it.
bulkCopy.ColumnMappings.add(i index of the address table,j index of the parent info table)
Note:You can filter Information_schema query by where condition or in clause and you use order by clause as per your requirement
For Excel Data:
open a connection through oledb fill the data in to the datatable using dataadapter rest of the things are similar like grid items saving.
Follow