Posted By

mpcircuitry on 12/07/09


Tagged

mysql database db Net


Versions (?)

Who likes this?

3 people have marked this snippet as a favorite

davidt
vali29
Bonitarunner


.NET DB Connection class for MySQL


 / Published in: C#
 

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

  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  

You need to login to post a comment.