Adding Multiple values to SQL Server table

Question

First off I'd like to apologize if this is more of a question than an example but I'm really lost here. I have a Windows Form that loads info from a text file. In each text file there is all the cities and counties in a given state, each section is separated by the .Split. I have a SQL Server 2008 database, 2 columns, Name and type. What I'd like to do is take all of the information and add it too individual rows with the name column being the name and the type column being state or county. Here is how I have the information split. How would I add a new row for each entry in the text?

void PopulateZones()
{
    ofdFile.Filter = "Text File (.txt)|*.txt|All Files (*.*|*.*";
    ofdFile.FilterIndex = 1;
    ofdFile.Multiselect = true;

    ofdFile.FileName = String.Empty;
    if (ofdFile.ShowDialog() == DialogResult.OK)
    {
        ofdFileLocTextBox.Text = ofdFile.FileName.ToString();
        string groups = File.ReadAllText(ofdFile.FileName);
        string[] parts = groups.Split(new char[] { '*' }, StringSplitOptions.RemoveEmptyEntries);
        stateTextBox.Text = parts[0];
        citiesTextBox.Text = parts[1];
        countiesTextBox.Text = parts[2];
        AddtoSQL(parts[0], parts[1]);
    }
}

void AddtoSQL(string cities, string counties)
{
    Sqlconnection conn = new SqlConnection(connString)
    Sqlcommand comm = new Sqlcommand("INSERT into [Table] (Name, Type) Values (@Name, @Type))";
    comm.Parameters.Add(@Name, each line of textbox);
    comm.Parameters.Add(@Type, City or County);
    comm.ExecuteNonQuery();
}

Not the source of your problem, but you should have conn and comm in using blocks: using (SqlConnection conn = ...){using (SqlCommand comm = new SqlCommand(...)){}} — John Saunders
– John Saunders, Commented Feb 6, 2013 at 1:57
I have edited your title. Please see, "Should questions include “tags” in their titles?", where the consensus is "no, they should not". — John Saunders
– John Saunders, Commented Feb 6, 2013 at 1:58
How is your data formatted in the text file? Can you post a few rows as an example? — Martin McGirk
– Martin McGirk, Commented Feb 6, 2013 at 15:15

Martin McGirk · Accepted Answer · 2013-02-07 09:46:27Z

So, the first problem you have is that your code is not doing what you think it does.The big problem is that you are reading in all the text and then only ever selecting the first three values of it.You don't give the format of your data, but suppose it looks like this:

Scotland*Edinburgh*Midlothian*
Scotland*Perth*Perthshire*

Your code

string groups = File.ReadAllText(ofdFile.FileName);

Reads the whole file into one string, such that it will look like this

Scotland*Edinburgh*Midlothian*\r\nScotland*Perth*Perthshire*

So splitting it using the following

string[] parts = groups.Split(new char[] { '*' }, 
         StringSplitOptions.RemoveEmptyEntries);

gives you a string array of 6 parts. Inserting multiple lines from this is doable but won't be very neat. You'd be much better to read your text files in by lines, and then iterate over the array of lines, splitting each one as you go and then adding the relevant parts to SQL. Something like

string[] lines = System.IO.File.ReadAllLines(ofdFile.FileName);
foreach (var line in lines)
{
    string[] parts = line.Split('*');
    AddtoSQL(parts[0], parts[1]);
}

That should insert all the data, but as an aside, if you are looking to execute numerous inserts at once, I'd recommend housing those inserts inside of a SQL Transaction.

I'd direct you to have a look at this MSDN article on the SqlTransaction Class

The gist of it is that you declare a transaction first, then loop over your inserts executing those against the transaction. Finally, when you commit your transaction the queries are all written to the database en mass. The reason I'd do this is that it will be much quicker and safer.

Suanmeiguo · Accepted Answer · 2013-02-05 23:40:24Z

0

Does it work if you change your sql statement into "insert into [table] (name, type) values(@name, @type)"? with the bracket.

answered Feb 5, 2013 at 23:40

Suanmeiguo

1,4153 gold badges19 silver badges30 bronze badges

1 Comment

Pacobart Over a year ago

No. I can add individual values just fine. I am trying to add multiple values at the same time.

Ryan · Accepted Answer · 2013-02-06 00:32:24Z

0

In SQL Server 2008 you can insert multiple line (records) with one query. All you need to do is a loop to extract row values and construct query string. So in AddToSQL method make your query like:

INSERT INTO [Table](Name, Type)
VALUES ('First',State1),
('Second',State2),
('Third',State3),
('Fourth',State4),

Insert Multiple Records Using One Insert Statement

answered Feb 6, 2013 at 0:32

Ryan

6558 silver badges19 bronze badges

Collectives™ on Stack Overflow

Adding Multiple values to SQL Server table

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related