Posted By

mpcircuitry on 12/07/09

Tagged

Versions (?)

Last Edited at 12/07/09 10:38am

Statistics

Viewed 989 times

Favorited by 1 user(s)

Related snippets

.NET DB Connection class for MySQL

/ Published in: C#

I used this code to run MySQL commands in ASP.NET

Highlighted TextExpand

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Odbc;
using System.Configuration;

public class DB_Connect
    {
          // GLOBAL ACCESS TO COMMAND OBJECT
          public static OdbcCommand cmd = new OdbcCommand();
          private OdbcDataAdapter _Adapter = new OdbcDataAdapter();
          private DataSet           _ds = new DataSet();
          private DataTable         _table = new DataTable();
          private OdbcConnection _Conn = new OdbcConnection();
          private OdbcDataReader     _Reader;

public static object runQuery(string db,  bool isProc, bool wantLastID)
          {
              DataTable DT = new DataTable();
              string connStr = null;

//SETS THE CONNECTION STRING
              if (db.ToLower() == "MPH_DEV".ToLower())
                  connStr = "Driver={SQL Server Native Client 10.0};Server=myserver;Database=mydb;Uid=mydb;Pwd=mydb;";
              else if (db.ToLower() == "keeneye".ToLower())
                  connStr = "Driver={MySQL ODBC 5.1 Driver};Server=myserver;Port=3306;Database=mydb;User=myuser;Password=mypass; Option=3;";
              else    
                return null;

//SETTING .NET CONNECTION OBJECTS
              OdbcConnection conn = new OdbcConnection(connStr);
              OdbcDataAdapter adapter = new OdbcDataAdapter();
              DataSet ds = new DataSet();

adapter.SelectCommand = new OdbcCommand();
              adapter.SelectCommand = cmd;
              adapter.SelectCommand.Connection = conn;

//SPECIFYING IF THIS IS A STORED PROCEDURE
              if (isProc)
                  adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
              else
                  adapter.SelectCommand.CommandType = CommandType.Text;

// IF THE USER WANTS THE LAST ID OF THE LAST PREVIOUS INSERT
              // OR THEY WANT A DATATABLE OBJECT
              if (wantLastID)
              {
                  object rt;
                  conn.Open();
                  cmd.ExecuteNonQuery();
                  cmd.CommandText = "select LAST_INSERT_ID()";
                  rt = Convert.ToString(cmd.ExecuteScalar());
                  return rt;
              }
              else
              {
                  conn.Open();
                  adapter.Fill(ds, "getData");
                  DT = ds.Tables["getData"];
                  conn.Close();
                  return DT;
              }
              
            
             
          }
        
        public DB_Connect(string DB_String)
        {
          _Conn.ConnectionString = ConnString(DB_String);
          _Adapter.SelectCommand = new OdbcCommand();
          _Adapter.SelectCommand.Connection = _Conn;
         
        }// End Contructor DB_Connect

public DataTable Execute(String table_Name)
        {
            _Conn.Open();
            _Adapter.Fill(_ds, table_Name);
            _table = _ds.Tables[table_Name];
            _Conn.Close();
            return _table;

}// End execute Function

private String ConnString(string DB_Name)
        {
// setup your own connection strings via if statements

if (DB_Name == null)
            {
                
                return "Nothing";
            }
  
        }// Ends the Connection String Method

#region Properties
        public OdbcDataAdapter Adapter
        {
            get { return _Adapter; }
            set { _Adapter = value; }
        }
        public DataSet DS
        {

get { return _ds; }
            set { _ds = value; }
        }
        public DataTable DT_Table
        {

get { return _table; }
            set { _table = value; }
        }
        public OdbcConnection Connection
        {

get { return _Conn; }
            set { _Conn = value; }
        }
        public OdbcDataReader Reader
        {

get { return _Reader; }
            set { _Reader = value; }
        }

#endregion

//EXAMPLE ON HOW TO USE THIS CLASS

//DB_Connect.cmd = new System.Data.Odbc.OdbcCommand();
      //DB_Connect.cmd.CommandText = "Insert into tbl_patient_surgical_history(patient_id,procedure_id) values(?,?)";
      //DB_Connect.cmd.Parameters.AddWithValue("?patient_id", pt_id);
      //DB_Connect.cmd.Parameters.AddWithValue("?procedure_id", item.Value.ToString());
      //DB_Connect.runQuery("keeneye", false, false);

}// End Class DB_Connect

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.