Solved

What object in ASP.net should I use to insert multiple records

Posted on 2010-09-05
8
768 Views
Last Modified: 2013-11-10
I want to create a ASP.ney website that will be used as a form for users to input batch files with customer numbers and transactions and amounts.. The users need to be able to paste in 100 customer numbers, transactions and amounts into the form, click a submit button and then be alerted if any of the numbers are invalid.. The info goes into a SQL Server 2008 database table.. So basically they are entering records into a table via a website.. What is the best object to use to do this? Can a datagrid except multiple records? Also, how should I go about alerting the user if the data is not good? For example, if they try to enter a customer and that customer does not exist.... I am a SQL developer for a large Co.. I have ASP.net and we will use it on our intranet site.. I have done a few simple web pages with SQL attached but I'm not sure the best way to tackle this one..
0
Comment
Question by:cheryl9063
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 30

Expert Comment

by:Reza Rad
ID: 33608797
why you want to do it BATCH?
usually, when you want to use batch, there should be a batch structure behind, this should be the reason for why you use batch.
for example if there is a directory of files. or if there is a .csv file which contains records which should be inserted in database, in these situations you need to use batch of course.
and you can use sql BCP command line to do this, you can use ADO.NET , executenonquery method to run a BCP command to import whole .csv file into sql server.
or also you can use SqlBulkCopy for this purpose.

but if you want to create an asp.net form which user enter records manually, one by one, why you need batch operation? why you didn't process every record at the time of entering new line? this way is better when you want to do validations also.


0
 
LVL 14

Accepted Solution

by:
Dhanasekaran Sengodan earned 400 total points
ID: 33609116
See this code to insert multiple rows from gridview in database
private void StartImport()
{
       SqlBulkCopy bulkCopy = new SqlBulkCopy("Server=ServerName;Database=test;Trusted_Connection=True;",
        SqlBulkCopyOptions.TableLock);
    bulkCopy.DestinationTableName = "dest table Name";

     DataTable dt = CreateDataTableFromFile();
    bulkCopy.WriteToServer(dt);
       }


private DataTable CreateDataTableFromFile()
{

       //create a datatable and fill it with the values from the grridview

DataTable dt = new DataTable();
        //add columns to datatable to display in anothre gridview

        for (int i = 0; i < GridView1.Rows.Count; i++)
        {
            Boolean isInsert = false;
            //cell should not be empty
            if (GridView1.Rows[i].Cells[0].Text.Trim() != "")
            {
                isInsert = false;
            }
            else
            {
                isInsert = true;
            }


            if (isInsert == false)
            {
                //check here for other cell validations
            }
            //do the above logic for all cells condtions.

            if (isInsert == true)
            {
                DataRow dr = dt.NewRow();
                //add values to datarow like below
                dr[0] = GridView1.Rows[i].Cells[0].Text;
                dr[0] = GridView1.Rows[i].Cells[1].Text;
            }

        }

               

        return dt; 
}

Open in new window

0
 
LVL 1

Author Comment

by:cheryl9063
ID: 33610902
Thanks..so I have a ASP.Net form and I will attach a gridview.. Should I do something with the SQLdatasource object and in the SQL command part..? Where do I put this looping?  Some more business rules you need to understand.. THe co. wants to use a  ASP.NET web form for users.. These users are phone reps.. They could enter 1 customer at a time or 200.. They have to be able to copy records from another source and paste into this web form.. They have to be able to click a button and see if the customers are valid numbers. I need help doing this using ASP.NET.. Will your solution (dhansmani) work in ASP.net? If so Where do I put your code? I'm new to ASP.net so I need a little extra 1+1 help:)..DO I have to have the SQLBulk copy part of your code? Please don't get hung up on the bulk part of this exercise. This question is about using a ASP.Net form to enter multiple records at a time ia SQL Server 2008 database and then getting a message letting the users know if specific fields are valid..
0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 9

Expert Comment

by:radcaesar
ID: 33631139
If the user is pasting n number of customer numbers, then there should be some delimiter in that data to split the numbers.

Then we can get that numbers in an array and process it one by one.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33632882
>>What object in ASP.net should I use to insert multiple records<<
You could use Table Valued Parameters.  That would seem ideal in your case, as it would avoid the steep penalty you will pay with round trips.   But if performance is not a big deal and you are more comfortable inserting one row at a time, that is fine, too.

>>Then we can get that numbers in an array and process it one by one. <<
Ouch!
0
 
LVL 1

Author Comment

by:cheryl9063
ID: 33633211
Thiis is great SQL answers but what I need first is the ASP.Net question answered. What is the best object to use to do this? Can a datagrid except multiple records?  What ASP.net object do I use on the ASP.NET web form for allowing users to paste in multiple records?
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
ID: 33641985
>>Thiis is great SQL answers but what I need first is the ASP.Net question answered.<<
It is one and the same.  Just a cursory Google search would have told you that.  Here is a link from MSDN:
Table-Valued Parameters in SQL Server 2008 (ADO.NET)
http://msdn.microsoft.com/en-us/library/bb675163.aspx

>>What is the best object to use to do this? <<
But to answer your specific question you can use the DataTable, for example.
0
 
LVL 1

Author Closing Comment

by:cheryl9063
ID: 33664707
Thanks it worked!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question