How to copy datatable into SQL Server?

Question

I need to copy a datatable into a new SQL Server table. I don't have any schema but I know what columns the datatable has (Year,Month,Day,MeanTemp,TotalPrecip,MaxGustSpeed)

From my researches I found that I need to use SqlBulkCopy but couldn't make it work. The project is a win application in VS 2010. Wonder if anyone here can help me out.

Thanks a lot

ps: it's my first post here, not sure if I've included enough details, plz let me know if any thing else needs to be mentioned. Also I am a beginner, so if you could possibly help me in an easy to understand manner

This is what I tried:

string connectionString = "server=localhost;database=CPSdata;Trusted_Connection=True";

//Open a connection with destination database;
using (SqlConnection connection = new SqlConnection(connectionString))
{
   connection.Open();

   using (SqlBulkCopy bulkcopy = new SqlBulkCopy(connection))
   {
      bulkcopy.DestinationTableName = "dbo.climateRawDataTable";

      try
      {
         bulkcopy.WriteToServer(dt);
      }
      catch (Exception ex)
      {
      }

      connection.Close();
   }
}

The error I get is:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

You said you couldn't make it work. Can you share what you tried and what issue/error you hit? — bryanmac, Dec 18 '11 at 05:06
@Febe Welcome.see [http://stackoverflow.com/questions/187770/copy-tables-from-one-database-to-another-in-sql-server]. — 4b0, Dec 18 '11 at 05:10
Thanks Shree Khanal. I'll check it out. bryanmac I updated the question and added the code :) — Febe, Dec 18 '11 at 05:14
it WAS sqlexpress but I moved it to SQL server 2008 but still same problem — Febe, Dec 18 '11 at 11:40

score 2 · Answer 1 · answered Dec 18 '11 at 07:01

2

The first problem is that your app can't connect to the database. Possible reasons:

You don't have TCP connections enabled
You are using SQL Express (use "server=.\SQLEXPRESS")
The identity your app is running under doesn't have permission to connect
You are running a named instance (your code tries to connect to the default instance)

Can you connect using Management Studio?

answered Dec 18 '11 at 07:01

RickNZ

18,448
3
51
66

thanks RickNZ. I was using sqlexpress ZI tried moving it to sql2008(instead of adding server=localhost\sqlserver), but still getting the same error. About TCP connection I have no idea how to check it. for the permission I tried changing the permissions (security->login->sqlserver2008->mapping->dbowener selected), but still no success. and about the named instance, I remember that I when i was installing, I had to add a named instance. but i cannot remember the name. How can i undertand the what name it has? – Febe Dec 18 '11 at 11:49
You enable TCP connections from SQL Server Configuration Manager. Select SQL Server Network Configuration on the left, double-click on your instance on the right, then right-click on TCP/IP and select Enable, and re-start SQL Server. You can also see all installed instances from Configuration Manager. Connecting to just "localhost" is the same as connecting to the default instance, which is called MSSQLSERVER. For any other instance (including SQL Express), you will need to explicitly include its name in the "server" part of the conn string, as in "localhost\YourInstanceName". – RickNZ Dec 19 '11 at 00:58

score 0 · Answer 2 · answered Dec 18 '11 at 08:02

The exception you are getting is simply because you aren't yet able to connect to data in Visual Studio. You said you're using VS 2010, I recommend that you create and test a new database connection in VS.

This msdn guide will help you on Connecting to Data in Visual Studio. The SqlBulkCopy is an efficient way to copy big data from any Data Source to SQL Server, like the name says, lets you bulk load a MSSQL table from other sources. The code you posted should work fine.

If you're using SQL Express, the connection string will be like:

SqlConnection con = new SqlConnection(@"Data Source="InstanceName"\SQLEXPRESS;Initial Catalog=CPSdata;Integrated Security=True");

By default the instance name, is your computer name, I agree with Rick on connecting using Management Studio first.

How to copy datatable into SQL Server?

2 Answers2