Advanced Search

.NET DB Connection class for MySQL

/ Published in: C#
Save to your folder(s)
I used this code to run MySQL commands in ASP.NET

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Text;
  4. using System.Data;
  5. using System.Data.Odbc;
  6. using System.Configuration;
  7.  
  8.   public class DB_Connect
  9.     {
  10.           // GLOBAL ACCESS TO COMMAND OBJECT
  11.           public static OdbcCommand cmd = new OdbcCommand();
  12.           private OdbcDataAdapter _Adapter = new OdbcDataAdapter();
  13.           private DataSet           _ds = new DataSet();
  14.           private DataTable         _table = new DataTable();
  15.           private OdbcConnection _Conn = new OdbcConnection();
  16.           private OdbcDataReader     _Reader;
  17.  
  18.  
  19.           public static object runQuery(string db,  bool isProc, bool wantLastID)
  20.           {
  21.               DataTable DT = new DataTable();
  22.               string connStr = null;
  23.  
  24.               //SETS THE CONNECTION STRING
  25.               if (db.ToLower() == "MPH_DEV".ToLower())
  26.                   connStr = "Driver={SQL Server Native Client 10.0};Server=myserver;Database=mydb;Uid=mydb;Pwd=mydb;";
  27.               else if (db.ToLower() == "keeneye".ToLower())
  28.                   connStr = "Driver={MySQL ODBC 5.1 Driver};Server=myserver;Port=3306;Database=mydb;User=myuser;Password=mypass; Option=3;";
  29.               else    
  30.                 return null;
  31.  
  32.               //SETTING .NET CONNECTION OBJECTS
  33.               OdbcConnection conn = new OdbcConnection(connStr);
  34.               OdbcDataAdapter adapter = new OdbcDataAdapter();
  35.               DataSet ds = new DataSet();
  36.  
  37.               adapter.SelectCommand = new OdbcCommand();
  38.               adapter.SelectCommand = cmd;
  39.               adapter.SelectCommand.Connection = conn;
  40.  
  41.               //SPECIFYING IF THIS IS A STORED PROCEDURE
  42.               if (isProc)
  43.                   adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
  44.               else
  45.                   adapter.SelectCommand.CommandType = CommandType.Text;
  46.  
  47.               // IF THE USER WANTS THE LAST ID OF THE LAST PREVIOUS INSERT
  48.               // OR THEY WANT A DATATABLE OBJECT
  49.               if (wantLastID)
  50.               {
  51.                   object rt;
  52.                   conn.Open();
  53.                   cmd.ExecuteNonQuery();
  54.                   cmd.CommandText = "select LAST_INSERT_ID()";
  55.                   rt = Convert.ToString(cmd.ExecuteScalar());
  56.                   return rt;
  57.               }
  58.               else
  59.               {
  60.                   conn.Open();
  61.                   adapter.Fill(ds, "getData");
  62.                   DT = ds.Tables["getData"];
  63.                   conn.Close();
  64.                   return DT;
  65.               }
  66.  
  67.  
  68.  
  69.           }
  70.  
  71.         public DB_Connect(string DB_String)
  72.         {
  73.           _Conn.ConnectionString = ConnString(DB_String);
  74.           _Adapter.SelectCommand = new OdbcCommand();
  75.           _Adapter.SelectCommand.Connection = _Conn;
  76.  
  77.         }// End Contructor DB_Connect
  78.  
  79.         public DataTable Execute(String table_Name)
  80.         {
  81.             _Conn.Open();
  82.             _Adapter.Fill(_ds, table_Name);
  83.             _table = _ds.Tables[table_Name];
  84.             _Conn.Close();
  85.             return _table;
  86.  
  87.         }// End execute Function
  88.  
  89.         private String ConnString(string DB_Name)
  90.         {
  91. // setup your own connection strings via if statements
  92.  
  93.             if (DB_Name == null)
  94.             {
  95.  
  96.                 return "Nothing";
  97.             }
  98.  
  99.         }// Ends the Connection String Method
  100.  
  101.  
  102.  
  103.  #region Properties
  104.         public OdbcDataAdapter Adapter
  105.         {
  106.             get { return _Adapter; }
  107.             set { _Adapter = value; }
  108.         }
  109.         public DataSet DS
  110.         {
  111.  
  112.             get { return _ds; }
  113.             set { _ds = value; }
  114.         }
  115.         public DataTable DT_Table
  116.         {
  117.  
  118.             get { return _table; }
  119.             set { _table = value; }
  120.         }
  121.         public OdbcConnection Connection
  122.         {
  123.  
  124.             get { return _Conn; }
  125.             set { _Conn = value; }
  126.         }
  127.         public OdbcDataReader Reader
  128.         {
  129.  
  130.             get { return _Reader; }
  131.             set { _Reader = value; }
  132.         }
  133.  
  134.  #endregion
  135.  
  136.       //EXAMPLE ON HOW TO USE THIS CLASS
  137.  
  138.       //DB_Connect.cmd = new System.Data.Odbc.OdbcCommand();
  139.       //DB_Connect.cmd.CommandText = "Insert into tbl_patient_surgical_history(patient_id,procedure_id) values(?,?)";
  140.       //DB_Connect.cmd.Parameters.AddWithValue("?patient_id", pt_id);
  141.       //DB_Connect.cmd.Parameters.AddWithValue("?procedure_id", item.Value.ToString());
  142.       //DB_Connect.runQuery("keeneye", false, false);
  143.  
  144.  
  145.  
  146.    }// End Class DB_Connect

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.