CODEWITHSUNDEEP
c#sqlsql-serverwinformsado.net
Pacobart
Pacobart

Reputation: 261

Adding Multiple values to SQL Server table

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();
}

Upvotes: 1

Views: 1114

Answers (3)

Martin McGirk
Martin McGirk

Reputation: 411

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.

Upvotes: 1

Ryan
Ryan

Reputation: 635

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

Upvotes: 0

Suanmeiguo
Suanmeiguo

Reputation: 1405

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

Upvotes: 0

Related Questions