Solved

SQL and ODBC in C#

Posted on 2003-12-08
17
2,714 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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

Independent Software Vendors: 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

In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
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…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

687 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