Revision: 21227
Updated Code
at December 7, 2009 10:52 by mpcircuitry
Updated Code
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
Revision: 21226
Updated Code
at December 7, 2009 10:45 by mpcircuitry
Updated Code
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
Revision: 21225
Initial Code
Initial URL
Initial Description
Initial Title
Initial Tags
Initial Language
at December 7, 2009 10:38 by mpcircuitry
Initial Code
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=VMDPS2009;Database=MPH_DEV;Uid=mbest01;Pwd=Ace*11111;";
else if (db.ToLower() == "keeneye".ToLower())
connStr = "Driver={MySQL ODBC 5.1 Driver};Server=138.5.41.188;Port=3306;Database=keeneye;User=keeneye;Password=keeneye1; 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)
{
if (DB_Name == null)
{
//return "Data Source=SQL-01;Initial Catalog=CommonDB;User Id=phpWebUser;Password=111";
//Integrated Security=true"
return "Nothing";
}
else if (DB_Name.ToLower() == "GIS".ToLower())
{
return "Data Source=VMDPS2009;Initial Catalog=GIS_DW;Integrated Security=True";
}
else if (DB_Name.ToLower() == "MPH_DEV".ToLower())
{
//return "Data Source=VM_DB_2008;Initial Catalog=GIS_DW; User=mbest01;Password=Ace*11111;";
return "Driver={SQL Server Native Client 10.0};Server=VMDPS2009;Database=MPH_DEV;Uid=mbest01;Pwd=Ace*11111;";
//Returns teh Connection String form the Appsettings tag in web.Config file
//return ConfigurationSettings.AppSettings[DB_Name];
//return "";
}
else if (DB_Name.ToLower() == "Keeneye".ToLower())
{
return "Driver={MySQL ODBC 5.1 Driver};Server=192.168.1.101;Port=8889;Database=keeneye;User=root;Password=root; Option=3;";
}
else
{
return null;
}
}// 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
Initial URL
Initial Description
I used this code to run MySQL commands in ASP.NET
Initial Title
.NET DB Connection class for MySQL
Initial Tags
mysql, database, Net
Initial Language
C#