Database Conversion

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.
LVL 4
thedude112286Asked:
Who is Participating?
 
Mohammed NasmanSoftware DeveloperCommented:
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
 
tomasX2Commented:
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
 
Mohammed NasmanSoftware DeveloperCommented:
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
 
tomasX2Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.