SQL Server multiple rows insert from .sql file in C#

Question

Ok, so suppose I have a .sql file (e.g., data.sql) with a couple thousand insert commands, like this:

...
INSERT INTO some_table VALUES ('person_name_1', 31, 'CA');
INSERT INTO some_table VALUES ('person_name_2', 29, 'NC');
INSERT INTO some_table VALUES ('person_name_3', 18, 'NY');
INSERT INTO some_table VALUES ('person_name_4', 21, 'IL');
INSERT INTO some_table VALUES ('person_name_5', 25, 'FL');
...

I wanted to know the best way to insert this data into a SQL Server 2012 database from C# code, and just as additional info, this is something that will be done once daily, but manually, from a web interface.

Right now, what I have is: I parse the .sql file and build this really big INSERT, so I end up with a StringBuilder (sb) that has

INSERT INTO some_table VALUES
    ('person_name_1', 31, 'CA'),
    ('person_name_2', 29, 'NC'),
    ('person_name_3', 18, 'NY'),
    ('person_name_4', 21, 'IL'),
    ('person_name_5', 25, 'FL'),
    ...

And then I run

using (var cmd = new SqlCommand { Connection = conn })
{
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = sb.ToString().Remove(sb.Length - 1);
    cmd.ExecuteNonQuery();
}

Any thoughts on a better way to do this?

From where you are getting the data, are you preparing it or getting it in CSV or text file ? — Milan Raval
– Milan Raval, Commented Feb 7, 2014 at 5:47
@MilanRaval I'm not preparing it. I'm getting it from some source, just like I described: as a .sql file, containing a few thousand INSERT commands. — fduayer
– fduayer, Commented Feb 7, 2014 at 5:52

Damith · Accepted Answer · 2014-02-07 06:06:24Z

2

you can try SQL Server Management Objects (SMO)

using System.Data.SqlClient;
using System.IO;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectionString = "...";
            string script =File.ReadAllText("data.sql");
            SqlConnection conn = new SqlConnection(sqlConnectionString);
            Server server = new Server(new ServerConnection(conn));
            server.ConnectionContext.ExecuteNonQuery(script);
        }
    }
}

If you need to read sql string from a web page

var webClient = new WebClient();
string script = webClient.DownloadString("your_file_path_url");

edited Feb 7, 2014 at 6:06

answered Feb 7, 2014 at 5:39

Damith

63.2k13 gold badges106 silver badges154 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

fduayer Over a year ago

Thank you for the reply. Just one more thing: is it possible, instead of reading from a physical file, read directly from an URL, into a stream?

Ehsan · Accepted Answer · 2014-02-07 05:41:04Z

0

You can add the reference to these 2 SMO dll

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

and then run the command directly by reading all of sql.

string scriptToRun = File.ReadAllText("Filepath");
using (SqlConnection conn = new SqlConnection("Yourconnectionstring"))
{
     Server server = new Server(new ServerConnection(conn));
     server.ConnectionContext.ExecuteNonQuery(scriptToRun);
}

answered Feb 7, 2014 at 5:41

Ehsan

32.8k6 gold badges61 silver badges66 bronze badges

Comments

Milan Raval · Accepted Answer · 2014-02-07 05:55:56Z

0

You can use SQLCMD to insert data to database - direct from an SQL file.

string inputFile = @"a.sql";

ProcessStartInfo info = new ProcessStartInfo("sqlcmd", string.Format(@" -d ""{0}"" -S ""{1}"" -i ""{2}"" -U ""{3}"" -P ""{4}""", database, sqlServer, inputFile, userName, password)

info.UseShellExecute = false;

info.CreateNoWindow = true;

info.WindowStyle = ProcessWindowStyle.Hidden;

info.RedirectStandardOutput = true;

Process p = new Process();

p.StartInfo = info;

p.Start();

answered Feb 7, 2014 at 5:55

Milan Raval

1,8901 gold badge18 silver badges35 bronze badges

Comments

Onots · Accepted Answer · 2014-02-07 06:49:34Z

0

If you're trying to get read (possibly stream?) data from a source and insert into your database I would consider using the SqlBulkCopy class [MSDN link].

The method WriteToServer provides an overload that accepts a IDataReader. So I would implement a DataReader for the source I'm using, and use that.

For more details on how to implement this I suggest reading this article on Michael Bowersox's Blog.

answered Feb 7, 2014 at 6:49

Onots

2,13621 silver badges29 bronze badges

Collectives™ on Stack Overflow

SQL Server multiple rows insert from .sql file in C#

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related