Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

Database Conversion

Posted on 2004-09-08
4
Medium Priority
?
227 Views
Last Modified: 2010-08-05
The following code works with Sql Server:

using System.Data;
using System.Data.SqlClient;

string connectionString = Database.GetConnectionString();
              System.Data.IDbConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionString);
              
              string queryString = "INSERT INTO [Payments] ([PaymentID], [OrderID], [TransactionID]) VALUES (@PaymentID, @OrderID, @TransactionID)";
              System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand();
              dbCommand.CommandText = queryString;
              dbCommand.Connection = dbConnection;
              
              System.Data.IDataParameter dbParam_paymentID = new System.Data.SqlClient.SqlParameter();
              dbParam_paymentID.ParameterName = "@PaymentID";
              dbParam_paymentID.Value = Guid.NewGuid();
              dbParam_paymentID.DbType = System.Data.DbType.Guid;
              dbCommand.Parameters.Add(dbParam_paymentID);
              System.Data.IDataParameter dbParam_orderID = new System.Data.SqlClient.SqlParameter();
              dbParam_orderID.ParameterName = "@OrderID";
              dbParam_orderID.Value = new Guid(orderID);
              dbParam_orderID.DbType = System.Data.DbType.Guid;
              dbCommand.Parameters.Add(dbParam_orderID);
              System.Data.IDataParameter dbParam_transactionID = new System.Data.SqlClient.SqlParameter();
              dbParam_transactionID.ParameterName = "@TransactionID";
              dbParam_transactionID.Value = txnID;
              dbParam_transactionID.DbType = System.Data.DbType.String;
              dbCommand.Parameters.Add(dbParam_transactionID);
              
              dbConnection.Open();
              try {
                  dbCommand.ExecuteNonQuery();
              }
              finally {
                  dbConnection.Close();
              }

Can someone please convert this code so that it will work with 1) MS Access and 2) MySql?  I will split the points evenly for the two solutions.  Thank you.
0
Comment
Question by:thedude112286
[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
  • 2
  • 2
4 Comments
 
LVL 5

Assisted Solution

by:tomasX2
tomasX2 earned 800 total points
ID: 12011183
Works with Access ... shouldn´t be all that different for mysql if they have an oledb provider.

                  public static void Insert()
            {
                  //string connectionString = Database.GetConnectionString();
                  string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Password=;User ID=Admin;Data Source=C:\Temp\Test1.mdb";
                  System.Data.OleDb.OleDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString);
             
                  string queryString = "INSERT INTO [Payments] ([PaymentID], [OrderID], [TransactionID]) VALUES (?, ?, ?)";

                  System.Data.OleDb.OleDbCommand dbCommand = new System.Data.OleDb.OleDbCommand();
                  dbCommand.CommandText = queryString;
                  dbCommand.Connection = dbConnection;
             
                  System.Data.OleDb.OleDbParameter dbParam_paymentID = new System.Data.OleDb.OleDbParameter();                  
                  dbParam_paymentID.Value = Guid.NewGuid();                  
                  dbCommand.Parameters.Add(dbParam_paymentID);

                  System.Data.OleDb.OleDbParameter dbParam_orderID = new System.Data.OleDb.OleDbParameter();
                  dbParam_orderID.Value = Guid.NewGuid();
                  dbCommand.Parameters.Add(dbParam_orderID);

                  System.Data.OleDb.OleDbParameter dbParam_transactionID = new System.Data.OleDb.OleDbParameter();
                  dbParam_transactionID.Value = Guid.NewGuid().ToString();
                  dbParam_paymentID.OleDbType = System.Data.OleDb.OleDbType.VarChar;
                  dbCommand.Parameters.Add(dbParam_transactionID);
             
                  dbConnection.Open();

                  try
                  {
                        dbCommand.ExecuteNonQuery();
                  }
                  catch( Exception ex )
                  {
                        System.Diagnostics.Debug.Write(ex.ToString());
                        throw;
                  }
                  finally
                  {
                        dbConnection.Close();
                  }
            }
0
 
LVL 22

Accepted Solution

by:
Mohammed Nasman earned 1200 total points
ID: 12014470
Hello

  Using OleDb Data provider you can write the same code for Sql server and access without any changes except the connection string

here's a conversion of your code to OleDb and work with access

string CnStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Tests\TestDB.mdb";
                  IDbConnection  dbConnection  = new OleDbConnection(CnStr);
                  //string queryString = "INSERT INTO [Payments] ([PaymentID], [OrderID], [TransactionID]) VALUES (@PaymentID, @OrderID, @TransactionID)";
                  string queryString = "INSERT INTO [Payments] ([PaymentID], [OrderID], [TransactionID]) VALUES (?, ?, ?)";

                  IDbCommand  dbCommand = new OleDbCommand();
                  dbCommand.CommandText = queryString;
                  dbCommand.Connection = dbConnection;
                  

                  IDataParameter  dbParam_paymentID = new OleDbParameter();
                  //dbParam_paymentID.ParameterName = "@PaymentID";
                  dbParam_paymentID.Value = Guid.NewGuid();
                  dbParam_paymentID.DbType = DbType.Guid;
                  dbCommand.Parameters.Add(dbParam_paymentID);
                  IDataParameter dbParam_orderID = new OleDbParameter();
                  //dbParam_orderID.ParameterName = "@OrderID";
                  dbParam_orderID.Value = new Guid(orderID);
                  dbParam_orderID.DbType = DbType.Guid;
                  dbCommand.Parameters.Add(dbParam_orderID);
                  IDataParameter dbParam_transactionID = new OleDbParameter();
                  //dbParam_transactionID.ParameterName = "@TransactionID";
                  dbParam_transactionID.Value = txnID;
                  dbParam_transactionID.DbType = DbType.String;
                  dbCommand.Parameters.Add(dbParam_transactionID);
             
                  dbConnection.Open();
                  try
                  {
                        dbCommand.ExecuteNonQuery();
                  }
                  finally
                  {
                        dbConnection.Close();
                  }

change the connectionstring to
string CnStr = @"Provider=SQLOLEDB;Integrated Security=SSPI;Data Source=MySrv; Initial Catalog=TestDB";

and it will work perfectly with sql server

HTH

regards,
0
 
LVL 22

Expert Comment

by:Mohammed Nasman
ID: 12014487
forgot to mention that OleDb provider for access work with parameters as "@PaymentID" or just "?" but with sql server you use "?" with oledb provider and "@PaymentID" with sqlclient data provider

so if your code will connect to sql server with SqlClient and access with Access then you could use this type of named paramaters, in other cases you just use question marks for parameters
0
 
LVL 5

Expert Comment

by:tomasX2
ID: 12016620
IDataParameter  dbParam_paymentID = new OleDbParameter();
               //dbParam_paymentID.ParameterName = "@PaymentID";
               dbParam_paymentID.Value = Guid.NewGuid();

               dbParam_paymentID.DbType = DbType.Guid; // This could lead to an exception with access if you have a replication id as a datatype...

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…

647 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