c#串連各種資料庫的代碼

using System;<br />using System.Collections.Generic;<br />using System.Linq;<br />using System.Web;<br />using System.Data.OleDb;<br />using System.Data;<br />using System.Data.SqlClient;</p><p>/// <summary><br />///SqlConnDb類,適用於Sql資料庫操作<br />/// </summary><br />public class SqlConnDb<br />{<br /> SqlConnection conn = null; //串連資料庫的對象</p><p> /// <summary><br /> /// 建構函式,串連資料庫，資料庫連接字元在web.Config檔案的AppSettings下的conStr<br /> /// </summary><br /> public SqlConnDb()<br /> {<br /> if (conn == null)//判斷串連是否為空白<br /> {<br /> //string conString = "provider=sqloledb.1;data source=.;initial catalog=capucivar;user id=sa;pwd=";//串連資料庫的字串<br /> string conString = System.Configuration.ConfigurationManager.AppSettings["conStr"];//串連資料庫的字串<br /> conn = new SqlConnection(conString);</p><p> if (conn.State == ConnectionState.Closed)<br /> {<br /> conn.Open();//開啟資料庫連接</p><p> }<br /> }<br /> }</p><p> /// <summary><br /> /// 從資料庫中查詢資料的,返回為DataSet<br /> /// </summary><br /> /// <param name="sql"></param><br /> /// <returns></returns><br /> public DataSet query(string sql)<br /> {<br /> DataSet ds = new DataSet();//DataSet是表的集合</p><p> SqlDataAdapter da = new SqlDataAdapter(sql, conn);//從資料庫中查詢</p><p> da.Fill(ds);//將資料填充到DataSet</p><p> connClose();//關閉串連</p><p> return ds;//返回結果</p><p> }</p><p> /// <summary><br /> /// 更新資料庫<br /> /// </summary><br /> /// <param name="sql"></param><br /> /// <returns></returns><br /> public int update(string sql)<br /> {<br /> SqlCommand oc = new SqlCommand();//表示要對資料來源執行的SQL語句或預存程序</p><p> oc.CommandText = sql;//設定命令的文本</p><p> oc.CommandType = CommandType.Text;//設定命令的類型</p><p>oc.Connection = conn;//設定命令的串連</p><p> int x = oc.ExecuteNonQuery();//執行SQL語句</p><p> connClose();//關閉串連</p><p> return x; //返回一個影響行數</p><p> }</p><p> /// <summary><br /> /// 關閉資料庫連接<br /> /// </summary><br /> public void connClose()<br /> {<br /> if (conn.State == ConnectionState.Open)<br /> {//判斷資料庫的串連狀態，如果狀態是開啟的話就將它關閉</p><p> conn.Close();<br /> }<br /> }</p><p>}</p><p>/// <summary><br />///OleDbConnDb類,適用於Accsee資料庫操作<br />/// </summary><br />public class OleDbConnDb<br />{<br /> OleDbConnection conn = null; //串連資料庫的對象</p><p> /// <summary><br /> /// 建構函式,串連資料庫，資料庫連接字元在web.Config檔案的AppSettings下的conStr<br /> /// </summary><br /> public OleDbConnDb()<br /> {<br /> if (conn == null)//判斷串連是否為空白<br /> {<br /> //string conString = "provider=sqloledb.1;data source=.;initial catalog=capucivar;user id=sa;pwd=";//串連資料庫的字串<br /> string conString = System.Configuration.ConfigurationManager.AppSettings["conStr"];//串連資料庫的字串<br /> conn = new OleDbConnection(conString);</p><p> if (conn.State == ConnectionState.Closed)<br /> {<br /> conn.Open();//開啟資料庫連接</p><p> }<br /> }<br /> }</p><p> /// <summary><br /> /// 從資料庫中查詢資料的,返回為DataSet<br /> /// </summary><br /> /// <param name="sql"></param><br /> /// <returns></returns><br /> public DataSet query(string sql)<br /> {<br /> DataSet ds = new DataSet();//DataSet是表的集合</p><p> OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);//從資料庫中查詢</p><p> da.Fill(ds);//將資料填充到DataSet</p><p> connClose();//關閉串連</p><p> return ds;//返回結果</p><p> }</p><p> /// <summary><br /> /// 更新資料庫<br /> /// </summary><br /> /// <param name="sql"></param><br /> /// <returns></returns><br /> public int update(string sql)<br /> {<br /> OleDbCommand oc = new OleDbCommand();//表示要對資料來源執行的SQL語句或預存程序</p><p> oc.CommandText = sql;//設定命令的文本</p><p> oc.CommandType = CommandType.Text;//設定命令的類型</p><p> oc.Connection = conn;//設定命令的串連</p><p> int x = oc.ExecuteNonQuery();//執行SQL語句</p><p> connClose();//關閉串連</p><p> return x; //返回一個影響行數</p><p> }</p><p> /// <summary><br /> /// 關閉資料庫連接<br /> /// </summary><br /> public void connClose()<br /> {<br /> if (conn.State == ConnectionState.Open)<br /> {//判斷資料庫的串連狀態，如果狀態是開啟的話就將它關閉</p><p> conn.Close();<br /> }<br /> }</p><p>}<br />using System;<br />using System.Collections.Generic;<br />using System.Text;<br />using System.Data.OleDb;<br />using System.Data; </p><p>namespace CheckMaterial<br />{<br />static class SqlHelp<br />{<br />/// <summary><br />/// link access<br />/// </summary><br />public static string connstring=@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=cost.mdb;Jet OLEDB:Database Password=1234567"; </p><p>/// <summary><br />/// link sql<br />/// </summary><br />////public static string connstring = @"Provider=SQLOLEDB; data source=server1;user id=sa;password=;initial catalog=ytdb"<br />/// <summary><br />/// link oracle<br />/// </summary><br />//public static string connstring = @"Provider=OraOLEDB.Oracle;Persist Security Info=false;User ID=;Password=;Data Source=";<br />//public static string connstring = @"Provider=msdaora.1;Persist Security Info=false;User ID=;Password=;Data Source="; </p><p>/// <summary><br />/// link excel<br />/// </summary><br />//public static string connstring = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelPath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'" </p><p>/// <summary><br />/// SqlExecuteNonQuery<br />/// </summary><br />/// <param name="sql"></param><br />/// <returns>using try catch to catch the error msg</returns><br />public static int SqlExecuteNonQuery(string sql)<br />{<br />//OleDbConnection conn = new OleDbConnection(connstring);<br />using (OleDbConnection conn = new OleDbConnection(connstring))<br />{<br />try<br />{<br />conn.Close();<br />conn.Open();<br />OleDbCommand cmd = new OleDbCommand(sql, conn);<br />int i = cmd.ExecuteNonQuery();<br />conn.Close();<br />return i;<br />}<br />catch (Exception e)<br />{<br />conn.Close();<br />//System.Windows.Forms.MessageBox.Show(eee.Message);<br />throw new Exception(e.Message);<br />}<br />}<br />} </p><p>/// <summary><br />/// SqlExecuteNonQuery<br />/// </summary><br />/// <param name="sql"></param><br />/// <param name="_connstring"></param><br />/// <returns>using try catch to catch the error msg</returns><br />public static int SqlExecuteNonQuery(string sql, string _connstring)<br />{<br />//OleDbConnection conn = new OleDbConnection(connstring);<br />using (OleDbConnection conn = new OleDbConnection(_connstring))<br />{<br />try<br />{<br />conn.Close();<br />conn.Open();<br />OleDbCommand cmd = new OleDbCommand(sql, conn);<br />int i = cmd.ExecuteNonQuery();<br />conn.Close();<br />return i;<br />}<br />catch (Exception e)<br />{<br />conn.Close();<br />//System.Windows.Forms.MessageBox.Show(eee.Message);<br />throw new Exception(e.Message);<br />}<br />}<br />} </p><p>/// <summary><br />/// SqlExcuteScalar<br />/// </summary><br />/// <param name="sql"></param><br />/// <returns>using try catch to catch the error msg</returns><br />public static object SqlExcuteScalar(string sql)<br />{<br />using (OleDbConnection conn = new OleDbConnection(connstring))<br />{<br />try<br />{<br />conn.Close();<br />conn.Open();<br />OleDbCommand cmd = new OleDbCommand(sql, conn);<br />object obj = cmd.ExecuteScalar();<br />conn.Close();<br />return obj;<br />}<br />catch (Exception e)<br />{<br />conn.Close();<br />throw new Exception(e.Message);<br />}<br />}<br />} </p><p>/// <summary><br />/// SqlExcuteScalar<br />/// </summary><br />/// <param name="sql"></param><br />/// <param name="_connstring"></param><br />/// <returns>using try catch to catch the error msg</returns><br />public static object SqlExcuteScalar(string sql, string _connstring)<br />{<br />using (OleDbConnection conn = new OleDbConnection(_connstring))<br />{<br />try<br />{<br />conn.Close();<br />conn.Open();<br />OleDbCommand cmd = new OleDbCommand(sql, conn);<br />object obj = cmd.ExecuteScalar();<br />conn.Close();<br />return obj;<br />}<br />catch (Exception e)<br />{<br />conn.Close();<br />throw new Exception(e.Message);<br />}<br />}<br />} </p><p>/// <summary><br />/// getDataTableFormSql<br />/// </summary><br />/// <param name="sql"></param><br />/// <returns>using try catch to catch the error msg</returns><br />public static DataTable getDataTableFormSql(string sql)<br />{<br />DataTable dt = new DataTable();<br />using (OleDbConnection conn = new OleDbConnection(connstring))<br />{<br />try<br />{<br />OleDbCommand cmd = new OleDbCommand(sql, conn);<br />OleDbDataAdapter oda = new OleDbDataAdapter(cmd);<br />oda.Fill(dt);<br />cmd.Dispose();<br />return dt;<br />}<br />catch (Exception e)<br />{<br />conn.Close();<br />throw new Exception(e.Message);<br />}<br />}<br />} </p><p>/// <summary><br />/// getDataTableFormSql<br />/// </summary><br />/// <param name="sql"></param><br />/// <param name="_connstring"></param><br />/// <returns>using try catch to catch the error msg</returns><br />public static DataTable getDataTableFormSql(string sql, string _connstring)<br />{<br />DataTable dt = new DataTable();<br />using (OleDbConnection conn = new OleDbConnection(_connstring))<br />{<br />try<br />{<br />OleDbCommand cmd = new OleDbCommand(sql, conn);<br />OleDbDataAdapter oda = new OleDbDataAdapter(cmd);<br />oda.Fill(dt);<br />cmd.Dispose();<br />return dt;<br />}<br />catch (Exception e)<br />{<br />conn.Close();<br />throw new Exception(e.Message);<br />}<br />}<br />} </p><p>/// <summary><br />/// SQLExecuteTransaction<br />/// </summary><br />/// <param name="sqls"></param><br />/// <returns>using try catch to catch the error msg</returns><br />public static bool SQLExecuteTransaction(string[] sqls)<br />{<br />using (OleDbConnection conn = new OleDbConnection(connstring))<br />{<br />try<br />{<br />conn.Open();<br />OleDbTransaction trans = conn.BeginTransaction();<br />try<br />{ </p><p>OleDbCommand cmd = conn.CreateCommand();<br />cmd.Transaction = trans;<br />//cmd.CommandText<br />foreach (string s in sqls)<br />{<br />cmd.CommandText = s;<br />cmd.ExecuteNonQuery();<br />}<br />trans.Commit();<br />return true;<br />}<br />catch (Exception e)<br />{<br />trans.Rollback();<br />throw new Exception(e.Message);<br />}<br />finally<br />{<br />conn.Close();<br />}<br />}<br />catch (Exception e)<br />{<br />//trans.Rollback();<br />throw new Exception(e.Message);<br />}<br />}<br />} </p><p>/// <summary><br />/// SQLExecuteTransaction<br />/// </summary><br />/// <param name="sqls"></param><br />/// <param name="_connstring"></param><br />/// <returns>using try catch to catch the error msg</returns><br />public static bool SQLExecuteTransaction(string[] sqls, string _connstring)<br />{<br />using (OleDbConnection conn = new OleDbConnection(_connstring))<br />{<br />try<br />{<br />conn.Open();<br />OleDbTransaction trans = conn.BeginTransaction();<br />try<br />{ </p><p>OleDbCommand cmd = conn.CreateCommand();<br />cmd.Transaction = trans;<br />//cmd.CommandText<br />foreach (string s in sqls)<br />{<br />cmd.CommandText = s;<br />cmd.ExecuteNonQuery();<br />}<br />trans.Commit();<br />return true;<br />}<br />catch (Exception e)<br />{<br />trans.Rollback();<br />throw new Exception(e.Message);<br />}<br />finally<br />{<br />conn.Close();<br />}<br />}<br />catch (Exception e)<br />{<br />//trans.Rollback();<br />throw new Exception(e.Message);<br />}<br />}<br />} </p><p>}<br />}<br />