Solved

SQL and ODBC in C#

Posted on 2003-12-08
17
2,712 Views
Last Modified: 2012-08-13
Currently, I have tables in one database that uses a SqlConnection, and tables in another database that uses OdbcConnection.  I need to write some sort of code for a C# program that will allow data from the tables in the SqlConnection to be copied to tables in the OdbcConnection.  At the moment, I've called the SqlConnection conn, and the OdbcConnection conn2. I've tried to write some SQL command:

string select_op = "CREATE TRIGGER op_trig " +
      "ON conn.op_temp " +
      "FOR INSERT " +
      "AS " +
      "INSERT INTO conn2.operation (code, name, etc etc) " +
      "SELECT code, name, etc etc" +
      "FROM inserted " +
      "DELETE FROM conn.op_temp";

The problem is the first error gives "Invalid object name 'conn.op_temp'", the next errors in conn2.operation and conn.op_temp. How can I go about getting the data from a table in one database on a SqlServer to another table on another database on a different server (Ingres in this case)?
0
Comment
Question by:pikapi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 7
17 Comments
 
LVL 6

Expert Comment

by:martinv
ID: 9901067
I'm afraid that this cannot be done this way.

1) triggers runs on the server. Server don't care about which other connection is created by client application. The sql server know about client's SqlConnection, but don't know anything about it's OleDbConnection.

2) SqlConnection and OleDbConnections are totally independent. Even two different OleDbConnections cannot tranfsfer databa between them.

I would try following approaches.

1) If you want to use triggers try to setup "linked server". It connects external database to the server so you can access it from the triggers and other T-SQL code. It doesn't work with all oledb providers, but works good for example with Oracle.

See doc for sp_addlinkedserver and read this article
http://www.microsoft.com/sql/techinfo/tips/administration/addingoracle.asp

than you can use something like INSERT INTO ....  SELECT .. FROM externalserver.externaltable

2) Second option is to transfer data by hand. Load data from the oledbconnection to the databaset or datareader. Than open sql connection and cycle through all dataset lines and create SQL Insert statemensts.

I can post code for converting dataset to buch of T-SQL insert statements but if it's feasible I would prefer first option.
0
 

Author Comment

by:pikapi
ID: 9901132
How do I apply the linked server commnad to my SqlServer (I'm using the free desktop SqlServer from Microsoft's webpage)? thanks...
0
 
LVL 6

Expert Comment

by:martinv
ID: 9901280
Easiest way is to connect to your desktop SQL Server with Enterprise Manager from full MS SQL Server. Than goto security/linked server/add new

If you don't have an access to the Enterprise Manager tool use command line query tool called osql.exe which comes with msde.

Than use
 exec sp_addlinkedserver ...  

command.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp

0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:pikapi
ID: 9901496
when i type in osql.exe into the command line i get "Error: No user selected. Try with -u or -e switches"... was I meant to type in something else with that command?
0
 

Author Comment

by:pikapi
ID: 9901515
also, once a linked server has been set up, will i be able to do:

string select_op = "CREATE TRIGGER op_trig " +
     "ON op_temp " +
     "FOR INSERT " +
     "AS " +
     "INSERT INTO myLinkedServerName.operation (code, name, etc etc) " +
     "SELECT code, name, etc etc" +
     "FROM inserted " +
     "DELETE FROM op_temp";

where op_temp is part of the original SQLServer and operation is the linked server i got from the odbc connection? am i understanding the concept of a linked server correctly?
0
 
LVL 6

Expert Comment

by:martinv
ID: 9903178
for create trigger: yes, you will be able to do it

for osql:
try osql -? for all command line switches

you must supply logon info:

osql -S servername -P password -d databasename

than you can enter commands. Command is sent to the database when you type "GO". E.g. to list all table names in the database try

select name from sysobjects where xtype='U'
go



0
 

Author Comment

by:pikapi
ID: 9907998
thanks, i got the osql command to work...and it seems to be working fine - i get this output: "1> 2> 1> 2> 3> 4> 5> 6> (1 row affected) (1 row affected)"

The problem with the trigger is, when my program tries to run, it pops up an error "Invalid object name 'fish.operation'". The SQL command I'm running with osql is:

USE master
GO
EXEC sp_addlinkedserver
@server = 'fish',
@srvproduct = '',
@provider = 'MSDASQL',
@datasrc = 'fish'

please help me!!
0
 

Author Comment

by:pikapi
ID: 9908107
huh? now i'm even more confused... i tried the osql command the way you said... and... i now get the error that "the server 'fish' already exists"??? but the program is still saying that fish.operation is an invalid object name?
0
 

Author Comment

by:pikapi
ID: 9908404
i'm reading over some of those docs again, and i wonder if the problem lies in the fact that the remote server uses Ingres and my local server uses SQLServer??maybe?
0
 
LVL 6

Expert Comment

by:martinv
ID: 9908531
as for ingress vs ms sql:  it can be the source for the problem.

you are specifiyng @provider MSDASQL which is for externals MS SQL Servers.

You can find some talk about setting up linked servers to ingres databaze here: http://dbforums.com/arch/41/2003/5/786023 
(unfortunately it proves that it's possible but is not providing any pointers to how to do it)

I have no experience with ingres, so it's hard to me to help you with this one. Try posting pointer to this question to SQL Server forum (http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/)

BTW - you may need to create system ODBC datasource named 'fish'.
BTW2: - before adding linked server again call sp_dropserver
BTW3: - sp_addlinkedserverlogin sets username and password for inges server.

(search msdn.microsoft.com for those stored procedures)

0
 

Author Comment

by:pikapi
ID: 9908551
thanks :) ... this may be stupid question, but how do I create a system ODBC datasource?
0
 

Author Comment

by:pikapi
ID: 9908560
also, would you mind posting code for converting dataset to T-SQL insert statements please?? I might end up doing that if I can't get hte linked server stuff to work... thanks for all your help!!!
0
 
LVL 6

Accepted Solution

by:
martinv earned 235 total points
ID: 9908724
Here it goes... It's tested for converting datasets taken from Oracle to SQL Server.

            /// <summary>
            /// Returns set of "INSERT INTO xxx VALUES (yyy)" for all
            /// dataset tables.
            /// </summary>
            /// <param name="ds"></param>
            /// <returns></returns>
            public string ToSqlInserts(DataSet dataset)
            {
                  string output = "";
                  foreach( DataTable table in dataset.Tables )
                  {
                        output += "\n" +  (string) ToSqlInserts( table ) + "\n";
                  }
                  return output;
            }
            
            public string ToSqlInserts(DataTable table)
            {
                  string output="";
                  string tableName = table.TableName.ToUpper();
                  string columnList = "";
                  string valueList = "";
                  
                  output += "---------------------------------\n";
                  output += "-- " + tableName + "\n";
                  output += "---------------------------------\n";
                  
                  if (UseIdentityInsert (table))                  
                        output += "SET IDENTITY_INSERT " + tableName + " ON \n\n";
                  
                  // create column list
                  foreach( DataColumn col in table.Columns )
                  {
                        if (columnList.Length > 0)
                              columnList += ", ";
                        
                        if (UseQuotedIdentifiers)
                              columnList += "[" + col.Caption.ToUpper() + "]";
                        else
                              columnList += col.Caption.ToUpper();
                  }
                  
                  // add values                  
                  foreach( DataRow row in table.Rows )
                  {
                        output += "insert into " + tableName + " ( " + columnList + ") \n";
                        valueList = "";
                        for( int i = 0; i < table.Columns.Count; i ++ )
                        {
                              if (valueList.Length >0)
                                    valueList+=", ";
                                    
                              valueList += "" + ObjectToSqlValueString( row[i] );
                        }
                        output += "VALUES (" + valueList + ")\n";
                        output += "GO\n\n";
                  }
                  
                  if (UseIdentityInsert(table))
                        output += "SET IDENTITY_INSERT " + tableName + " OFF \n\n";
                  
                  return output;
            }
            private string ObjectToSqlValueString(object o)
            {
                  switch (o.GetType().ToString())
                  {
                              // ---------------
                              // DATETIME
                              // ---------------
                        case "System.DateTime":
                        {
                              // convert tu universal date format
                              DateTime dtValue = (DateTime) o;
                              return
                                    "'" +
                                    ((int)dtValue.Year)  .ToString().PadLeft(4,'0') +
                                    ((int)dtValue.Month) .ToString().PadLeft(2,'0') +
                                    ((int)dtValue.Day)   .ToString().PadLeft(2,'0') + " " +
                                    ((int)dtValue.Hour)  .ToString().PadLeft(2,'0') + ":" +
                                    ((int)dtValue.Minute).ToString().PadLeft(2,'0') + ":" +
                                    ((int)dtValue.Second).ToString().PadLeft(2,'0') +
                                    "'";
                        }
                        
                              // ---------------
                              // string
                              // ---------------
                        case "System.String":
                        {
                              String strValue = (String) o;
                              return "'" + strValue.Replace("'","''") + "'";
                        }

                              // ---------------
                              // boolean
                              // ---------------
                        case "System.Boolean":
                        {
                              System.Boolean b = (System.Boolean) o;
                              if (b)
                                    return "1";
                              else
                                    return "0";
                        }

                              // ---------------
                              // null
                              // ---------------
                        case "System.DBNull":
                              return "NULL";
                  
                              //HACK: float should be always with "."
                        case "System.Decimal":
                        case "System.Double":
                        {
                              string result = o.ToString();
                              //HACK - it's for performance reason. Cleaner solution would use
                              //localization to EN.
                              return result.Replace(",",".");
                        }

                              // ---------------
                              // other values
                              // ---------------      
                        case "System.Int16":   // integer
                        case "System.Int32":   // integer
                        default:
                              return o.ToString();
                  }
            }
0
 
LVL 6

Expert Comment

by:martinv
ID: 9908740
System datasource:

go to control panel and open "Data sources (ODBC)" applet, than select "System DSN" tab.
0
 

Author Comment

by:pikapi
ID: 9908751
control panel where?
0
 
LVL 6

Expert Comment

by:martinv
ID: 9909090
Windows controls panel.

Start menu/Settings/Control panel

If you are using Windows XP you need to switch to "Classic view" - link on the left - to view all avaliable applets
0
 

Author Comment

by:pikapi
ID: 9909235
oh okay...i thought that might be it... but how do I view the applets (i'm in wndows 2000)?
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
bound data table problem 2 41
C# Windows Form Navigation - Total Beginner 9 48
Connect Gridview column to Textbox in C# 2 41
c#, case, if 4 19
Article by: Ivo
C# And Nullable Types Since 2.0 C# has Nullable(T) Generic Structure. The idea behind is to allow value type objects to have null values just like reference types have. This concerns scenarios where not all data sources have values (like a databa…
We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question