Solved

Database Conversion

Posted on 2004-09-08
4
220 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:
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

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Extention Methods in C# 3.0 by Ivo Stoykov C# 3.0 offers extension methods. They allow extending existing classes without changing the class's source code or relying on inheritance. These are static methods invoked as instance method. This…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

809 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