Solved

Database Conversion

Posted on 2004-09-08
4
223 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 200 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 300 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

688 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