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
and we can specify the table owner for it(is necessary).

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 this

query: 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.


Article by srirama
A Good advice from parent to a Child , Master to a Student , Scholar to an Ignorant is like a doctor prescribed pill it is bitter to take but when they take it will do all good for them --- Bhushan

Follow srirama or read 73 articles authored by srirama

Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: