Connect to SQL Server and Oracle server and save the data to SQL Server

Question

I need to write a console app that queries two databases on two different servers: one is SQL Server, and the other is Oracle; and saves the data to another SQL Server.

I'm very confused about connections I did the connection well.

Can I open connection inside another connection to write Select and Insert to?

I write the connection to the other servers as also and it's connected. My problem is how to write insert to (to the other server)?

sqlconn.Open();

SqlCommand com = new SqlCommand("SELECT Email, Badge, Name FROM PublishedWorks", sqlconn);

using (SqlDataReader reader = com.ExecuteReader())
{
    while (reader.Read())
    {
        Console.WriteLine(reader[0]);
    }
}

Where do I have to write the insert?

Can anyone help me please?

Thanks,

Izzy · Accepted Answer · 2017-08-23 09:37:51Z

2

You could try something as follows - Create a class which you will use when you get the data from the reader.

public class Foo
{
   public string Email { get; set; }
   public string Badge { get; set; }
   public string Name { get; set; }
}

Then we will get the data first from our first db as follows:

public List<Foo> GetData()
{
   List<Foo> dataList = new List<Foo>();
   string connectionString = "Connection String A";
   string selectStatement = "SELECT Email, Badge, Name FROM PublishedWorks";

   using (var con = new SqlConnection(connectionString))
   {
     using (var cmd = new SqlCommand(selectStatement, con))
     {
       con.Open();

       using (var reader = cmd.ExecuteReader())
       {
          dataList.Add(new Foo
          {
            Email = reader.GetString(0),
            Badge = reader.GetString(1),
            Name = reader.GetString(2)
           });
         }
        }
      }
    return dataList;
}

Now That we have the data we will insert it into another database for that we can do:

public void InsertData()
{
   string connectionString = "Connection String B";
   string insertStatment = "INSERT INTO SOMETABLE (Email, Badge, Name) VALUES (@Email, @Badge, @Name)";
   List<Foo> dataList = GetData();

   if(dataList.Count > 0)
   {
     using (var con = new SqlConnection(connectionString))
     {
       using (var cmd = new SqlCommand(insertStatment, con))
       {
         con.Open();

         foreach (var items in dataList)
         {
           cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = items.Email;
           cmd.Parameters.Add("@Badge", SqlDbType.NVarChar).Value = items.Badge;
           cmd.Parameters.Add("@Name", SqlDbType.NVarChar).Value = items.Name;
          }
           cmd.ExecuteNonQuery();
        }
      }
    }
 }

answered Aug 23, 2017 at 9:37

Izzy

6,8988 gold badges44 silver badges92 bronze badges

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

3 Comments

Tuta Over a year ago

thank you so much you your answer, It's helped me alot. but please, Do I have to create Foo class?? I getting the data from SQL ??

Izzy Over a year ago

You're welcome! you do not have to create a class called Foo it was just an example which you can build off. When you read the data using SqlReader you need to store the data coming back somewhere so you can use it again and for that I used a List<T> but you can easily store it in a DataTable and then read it back and insert it into another database as required.

Tuta Over a year ago

can you please give me an example for datatable... thanks for your help

Jonathan Willcock · Accepted Answer · 2017-08-23 10:27:37Z

Since you are going from Oracle to SqlServer, I encourage you to use SqlBulkCopy; it will be much faster. As a console app, you will need something like:

class Program
{
    static void Main(string[] args)
    {
        string orclString = "Data Source=orclservername:/XE;Persist Security Info=True;User Id = \"Jonathan\"; Password=safepassword";
        string sqlString = "Integrated Security = SSPI; Initial Catalog = sqldemo; Data Source =sqlservername";
        using (var orclConn = new OracleConnection(orclString))
        {
            using (var orclCmd = new OracleCommand("SELECT * FROM yourschema.yourorcltable", orclConn))
            {
                DataTable dT = new DataTable();
                var dA = new OracleDataAdapter(orclCmd);
                dA.Fill(dT);
                using (SqlConnection sqlConn = new SqlConnection(sqlString))
                {
                    sqlConn.Open();
                    using (var bC = new SqlBulkCopy(sqlConn))
                    {
                        bC.DestinationTableName = "yoursqltable";
                        bC.WriteToServer(dT);
                    }
                }
            }
        }
    }
}

To run this, you will need to include references to Oracle.ManagedDataAccess.Client and System.Data.SqlClient. Obviously you will need your own connection strings. If you need any help with these, then try this wonderful website.

In my example you can see how the second connection to SqlServer is nested within the using call to Oracle. The alternative is to declare the DataTable before the using (OracleConnection), and then have the using (SqlConnection) as a separate block afterwards. In practice, this is what I would do, but I wanted to demonstrate how it is perfectly possible to have two different connections open at the same time.

Please note when copying from Oracle via the .Net libraries you need to be particularly careful about data types. For example, although Oracle documentation tells you that the equivalent of a .Net Int32 is NUMBER(10,0), in practice the library will treat this as a Int64. For ints I therefore only use NUMBER(9,0) in Oracle.

Mahmoud Alam · Accepted Answer · 2017-08-23 09:12:52Z

0

It is ok to declare another connection and use it .

sqlconn.Open();

SqlCommand com = new SqlCommand("SELECT Email, Badge, Name FROM PublishedWorks", sqlconn);

using (SqlDataReader reader = com.ExecuteReader())
{
    while (reader.Read())
    {
        // you can create another method to insert and call it from here
    }
}

Also i suggest to use SSIS package instead of console application.

answered Aug 23, 2017 at 9:12

Mahmoud Alam

4301 gold badge5 silver badges14 bronze badges

Collectives™ on Stack Overflow

Connect to SQL Server and Oracle server and save the data to SQL Server

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related