Solved

Database Conversion

Posted on 2004-09-08
4
216 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
  • 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:
mnasman 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:mnasman
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Article by: Ivo
Anonymous Types in C# by Ivo Stoykov Anonymous Types are useful when  we do not need to follow usual work-flow -- creating object of some type, assign some read-only values and then doing something with them. Instead we can encapsulate this read…
Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

708 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now