Programatically create database and insert data from CSV files

Question

I have a huge amount of Data in 360 CSV files that I want to store in an SQL Server Database by using C#.

I have installed Visual Studio 2010 but I have not installed any version of SQL Server.

Now I have two questions:

Is it possible to create an SQL Server database using C# (Though SQL Server is not available)?
If yes, how can I create the database and insert data to the database using C#?

I have tried some codes like the following, found from different sources (blindly copy-pasted as I am not clear):

SqlConnection con = new SqlConnection(@"Data Source=SHAWHP\SQLEXPRESS;Initial Catalog=FOO;Persist Security Info=True;User ID=sa");
string filepath = "C:\\Users\\NKB\\Desktop\\Catchments\\ALKANDA.csv";
StreamReader sr = new StreamReader(filepath);
string line = sr.ReadLine();
string[] value = line.Split(',');
DataTable dt = new DataTable();
DataRow row;

foreach (string dc in value)
{
    dt.Columns.Add(new DataColumn(dc));
}

while (!sr.EndOfStream)
{
    value = sr.ReadLine().Split(',');
    if (value.Length == dt.Columns.Count)
    {
        row = dt.NewRow();
        row.ItemArray = value;
        dt.Rows.Add(row);
    }
}

SqlBulkCopy bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock);
bc.DestinationTableName = "tblparam_test";
bc.BatchSize = dt.Rows.Count;
con.Open();
bc.WriteToServer(dt);
bc.Close();
con.Close();

However, it throws the following exception (related to instance):

The error is "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"

That exception means that your SQL connection string is off. Double check it against the server information — tyh, Jul 10 '14 at 04:35
Why do you want the CSV files in a database? What's your goal here? — Enigmativity, Jul 10 '14 at 07:17

score 1 · Answer 1 · answered Jul 10 '14 at 04:52

Based on my experience, if an application stores or connects to a database, the database must be physically set, meaning, the database and corresponding tables and its fields must be set before connection can be established.

Yes, you can create a database (as an mdf file) using Visual Studio that you can use in your application to import your csv data.

source: http://www.youtube.com/watch?v=3w2JkLcp-UA

or programmatically http://support.microsoft.com/kb/307283

nsb · Accepted Answer · 2014-07-11T09:32:17.013

SQL server and Database are quite different. SQL Server must be installed and accessible to be able to connect to. Your error tells that that your server is not accessible. It is possible to create Database using C# ADO.NET, but I'll discourage to deal with it, unless you are not writing Server management tool. In Visual studio you can explore your servers (View/SQL Server Object Explorer) and create a database if needed. In properties you'll see the correct ConnectionString you'll need. Evevn if you have no Access to a Server Instance, Visual Studio is coming with file based SQL Server:

   <connectionStrings>
    <add name="ConnectionName" 
         connectionString="Data Source=(LocalDb)\v11.0;;AttachDbFilename=|DataDirectory|\MVC5_pr-2-1-alpha1;Initial Catalog=MVC5_pr-2-1-alpha1;Integrated Security=SSPI"
         providerName="System.Data.SqlClient" />
   </connectionStrings>

tables can be created with something like

  SqlCommand command = new SqlCommand
    ("CREATE TABLE MyTableName(MyCol char(50),Last null,MyNCol char(50));", con);
    command.ExecuteNonQuery();

and yes - SqlBulkCopy is one of the best ways to import big data.

score 0 · Answer 3 · edited May 23 '17 at 10:32

0

What do you mean by SQL Server based database. Just install SQL Server 2008 R2 and above. Now coming to your two points :

1) Yes, It is absolutely possible to create database from C#

2) Refer to the Codeproject Link and similar question on this site

edited May 23 '17 at 10:32

Community

1
1

answered Jul 11 '14 at 08:11

tom

719
1
11
30

Programatically create database and insert data from CSV files

3 Answers3