Import text file into SQL Server table using SqlBulkCopy in C# (How to use it?)

Question

I'm new to coding, I'm trying to import a small text file into a SQL Srever table using SqlBulkCopy class, but I failed. It's an exercise, but it's very important for me.

This is the only code I've done. Please some body can help me to find what next.

string CadenaCon = "Data Source=CH-PC;Initial Catalog=Importar;Integrated Security=True";
SqlConnection Con = new SqlConnection(CadenaCon);

The File path is 'C:\ok\test1.txt'

If you have your data in a textfile you would be better off using bcp.exe instead. Also you need to narrow your question down to a specific problem. Right now your question is basically "Teach me C# from scratch" and this site is not for those kinds of questions. — Scott Chamberlain
– Scott Chamberlain, Commented Jul 20, 2015 at 5:42
Check this link technet.microsoft.com/en-us/library/aa173839(v=sql.80).aspx You can use bulk insert in sql. Hope this is important information for you. happy Coding :) — Bhavin
– Bhavin, Commented Jul 20, 2015 at 6:38

Sreedhar · Accepted Answer · 2015-07-20 06:10:23Z

6

With C# code you can do something as below:

        int i = 0;
        const string connectionString = "Data Source=CH-PC;Initial Catalog=Importar;Integrated Security=True";
        var dbConn = new SqlConnection(connectionString);
        var sr = new StreamReader(@"C:\sourcefiles\test.txt");
        string line = sr.ReadLine();

        string[] strArray = line.Split(',');
        var dt = new DataTable();

        for (int index = 0; index < strArray.Length; index++)
            dt.Columns.Add(new DataColumn());

        do
        {
            DataRow row = dt.NewRow();

            string[] itemArray = line.Split(',');
            row.ItemArray = itemArray;
            dt.Rows.Add(row);
            i = i + 1;
            line = sr.ReadLine();
        } while (!string.IsNullOrEmpty(line));


        var bc = new SqlBulkCopy(dbConn, SqlBulkCopyOptions.TableLock, null)
        {
            DestinationTableName = "TestData",
            BatchSize = dt.Rows.Count
        };
        dbConn.Open();
        bc.WriteToServer(dt);
        dbConn.Close();
        bc.Close();

But as Scott mentioned, you should be having some basic understanding of C# coding to start with.

answered Jul 20, 2015 at 6:10

Sreedhar

30.2k39 gold badges128 silver badges198 bronze badges

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

2 Comments

Scott Chamberlain Over a year ago

If a execption is thrown by bc.WriteToServer(dt); how would dbConn get closed? You should not teach a new programmer bad habits like not using a using statement on his disposable objects. (btw, using a using statement would fix that first problem I asked about)

Yoa Over a year ago

Thanks to Screedhar, your code worked perfect. I also appreciate Scott and Tonny's comments. I had already done this exercise using bcp.exe and Bulk in SQL Server's Stored Procedure. I just needed to explore this option. Thanks to all of you who invested time in helping me to find the solution. And excuse my English, this is not my native language. I'm learning it too.

Collectives™ on Stack Overflow

Import text file into SQL Server table using SqlBulkCopy in C# (How to use it?)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related