Efficiënt writing to a SQL Server from C#

Question

I am new to programming in c# and have no experience with SQL at all. But I used to do some programming when VB6 came out. I am writing a program that creates a SQL Server database from a textfile which is a dump, generated by a computer. It has data in it for which I need to create +- 200 tables and then I read the file again to populate all the tables.

I have Microsoft SQL Server 2014 installed and Visual Studio Express 2015.

I am able to read the textfile and determine the tables I need, I can open the database and create a table. I have three questions, two of them I can sort of find the answer to on this forum and one question that still mystifies me.

The first question is if it is efficient to create a new connection every time to create a new table. The C# program "designs" the table which I put in the string "NewType" then the CreateBlockType is called to create the new table. After that a new table is created and send to CreateBlockType again. This will happen 200 times and later when filling the actual data itself maybe a couple of 45000 times.

static void CreateBlockType(string NewType)
{
    using (SqlConnection conn = new SqlConnection("Server=WIJ-PC\\SQLEXPRESS;Initial Catalog=FoxData;Integrated Security=True;"))
    {
        conn.Open();
        using (SqlCommand command = new SqlCommand(NewType, conn)) 
              command.ExecuteNonQuery();
        conn.Close();
    }

The two other questions are:

How do I check if a table exists (did not find a good answer)
And the last is a bit difficult to explain but lets say the file contains two Customer types of data, they have the same name but the columns might be slightly different, so I need to create two tables but what is a fast method to compare an existing table in the database with the new one the c# program just created. Or to put it more basic: does this table in this format exists, yes or no.

I am sorry for being long winded, bad habit of me :-(

Sincerely, Rob.

Welcome to SO! Please consider splitting your question into individual posts. Avoid questions that ask for people's opinions. Feel free to review SO help about [asking](https://stackoverflow.com/help/asking) — ghybs, Nov 11 '15 at 21:24

score 2 · Accepted Answer · answered Nov 11 '15 at 21:17

2

In the future, if you have multiple questions, you should really break them up into multiple posts.

For question #1: .NET will transparently cache SqlConnections, so there should be very little overhead associated with rapidly creating and closing connections, so long as the connection string is always the same.

For questions #2 and #3, you should look into the INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS views respectively.

answered Nov 11 '15 at 21:17

Glorin Oakenfoot

2,455
1
17
19

SQL Server has another way to check if objects exist: the function `OBJECT_ID`. See [this question](http://stackoverflow.com/questions/167576/check-if-table-exists-in-sql-server) – ventiseis Nov 11 '15 at 21:19
[This question](http://stackoverflow.com/questions/5244126/net-connection-pooling) gives some hints about creating connections and connection pooling. – ventiseis Nov 11 '15 at 21:22
All three parts of this question are already answered in other questions. – ventiseis Nov 11 '15 at 21:23
First of all, thanks for replying, I will look into the links you provided. One thing I knew was comming is, me asking something already beeing asked. – Rob R Nov 11 '15 at 21:56
Problem is that its not easy to prhase a question so it is uniform to all others with the same question. – Rob R Nov 11 '15 at 22:15

Efficiënt writing to a SQL Server from C#

1 Answers1