Fill SQL data from a .sql file downloaded automatically

Question

I have a C# project where I am trying to autofill a SQL table with a file downloaded from the net.

The file is http://ts1.travian.com/map.sql and you will see that it has data already set for SQL database manipulations.

Here's a little snipet of how they look:

INSERT INTO `x_world`
    VALUES (1517,315,399,3,181705,'New Remial',82126,'Tale',0,'',273);
INSERT INTO `x_world`
    VALUES (67792,107,316,1,175829,'3.Clay',35440,'salvaje_jabali',0,'',244);
INSERT INTO `x_world`
    VALUES (32790,349,360,1,189271,'Lumeria',9702,'Aquifel',90,'APE',20);
INSERT INTO `x_world`
    VALUES (72449,-42,310,1,194313,'New village',2634,'paloc',0,'',24);
INSERT INTO `x_world`
    VALUES (75669,-26,306,1,170802,'Yao Ming',2634,'paloc',0,'',330);

I've inserted a break line before each INSERT code for readability.

I have recreated the Table in my project with all the required columns but how can I have the project download the file on the click of a Button (named CommandButton1) and then parse all of that data into the required columns in the table in my project (the data in the downloaded file is separated by a comma it seems all the way through).

Where does this "file downloaded from the net" come from? Is there any way an attacker could inject text into the file? What if someone injected "; DROP DATABASE"? — Dour High Arch, Nov 21 '12 at 22:10

score 1 · Accepted Answer · answered Nov 21 '12 at 22:16

1

Using the code from Robbie's answer and elaborating on that:

using System.Net;

//Download
WebClient Client = new WebClient ();
Client.DownloadFile("http://ts1.travian.com/map.sql", @"C:\folder\file.sql");

// Read into a file
var sqltext = System.IO.File.ReadAllText(@"c:\folder\file.sql");
// Split the sql statements up
var sqlStatements = sqltext.Split(';'); 

// Insert
using (SqlConnection connection = new SqlConnection(connectionParameters))
    {   
        connection.Open();
        foreach (var sqlStatement in sqlStatements)  
        {
            SqlCommand command = new SqlCommand(sqlStatement, connection);
            command.ExecuteNonQuery();
        }
    }

answered Nov 21 '12 at 22:16

Matt Roberts

26,371
31
103
180

Hi Matt,thanks, That works really well but, and this may show my noobiness but it says: "The name 'connectionParameters' does not exist in the current context". I have a feeling I am missing something noobie-like – touyets Nov 22 '12 at 09:36
To be clear, I have set up the table already, It is named x_world. It has all of the required columns, no problem. I have put it in a dataset (database1DataSet) and I have put a data grid view (DataGridView1) in a form so that i can see the data uploaded afterwards but it still says my connection parameters do not exist. – touyets Nov 22 '12 at 09:49
Hi again (again), I think i've sorted that issue out by changing that line to: using (SqlConnection connection = new SqlConnection(Properties.Settings.Default.x_worldConnectionString)) – touyets Nov 22 '12 at 11:18
Yeah thats it, "connectionParameters" is a placeholder for your connection details - you cracked it – Matt Roberts Nov 22 '12 at 17:48

score 0 · Answer 2 · edited May 23 '17 at 11:52

0

There is some sample code here that should get you started with the downloading of the file:

How to download a file from a website in C#

You can then either run an SSIS/SQL Server Agent/batch job to do the data load. Or if you wanted to do it in code:

How to execute an .SQL script file using c#

edited May 23 '17 at 11:52

Community

1
1

answered Nov 21 '12 at 22:01

Robbie Dee

1,939
16
43

Hi Robbie, That absolutely sorted the first part of my issue but now to add the file to the table. I'll try the second link you sent and will let you know. Thank you so much. – touyets Nov 21 '12 at 22:13
If there is a question you're looking to get answered on here, please take the time to search the question archive using some keywords and "stackoverflow" in Google or whatever your search engine of choice is. We here on Stackoverflow are happy to assist with any programming question but we want to keep the archives clean of any similar or duplicated questions that may have been answered previously... – Robbie Dee Nov 22 '12 at 00:01

Fill SQL data from a .sql file downloaded automatically

2 Answers2