CODEWITHSUNDEEP
c#sql-serverdatabase
Rob R
Rob R

Reputation: 3

Efficiënt writing to a SQL Server from C#

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:

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

Sincerely, Rob.

Upvotes: 0

Views: 80

Answers (1)

Glorin Oakenfoot
Glorin Oakenfoot

Reputation: 2623

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.

Upvotes: 2

Related Questions