c#连接各种数据库的代码

using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Data.OleDb; using System.Data; using System.Data.SqlClient; /// <summary> ///SqlConnDb类,适用于Sql数据库操作 /// </summary> public class SqlConnDb { SqlConnection conn = null; //连接数据库的对象 /// <summary> /// 构造函数,连接数据库，数据库连接字符在web.Config文件的AppSettings下的conStr /// </summary> public SqlConnDb() { if (conn == null)//判断连接是否为空 { //string conString = "provider=sqloledb.1;data source=.;initial catalog=capucivar;user id=sa;pwd=";//连接数据库的字符串 string conString = System.Configuration.ConfigurationManager.AppSettings["conStr"];//连接数据库的字符串 conn = new SqlConnection(conString); if (conn.State == ConnectionState.Closed) { conn.Open();//打开数据库连接 } } } /// <summary> /// 从数据库中查询数据的,返回为DataSet /// </summary> /// <param name="sql"></param> /// <returns></returns> public DataSet query(string sql) { DataSet ds = new DataSet();//DataSet是表的集合 SqlDataAdapter da = new SqlDataAdapter(sql, conn);//从数据库中查询 da.Fill(ds);//将数据填充到DataSet connClose();//关闭连接 return ds;//返回结果 } /// <summary> /// 更新数据库 /// </summary> /// <param name="sql"></param> /// <returns></returns> public int update(string sql) { SqlCommand oc = new SqlCommand();//表示要对数据源执行的SQL语句或存储过程 oc.CommandText = sql;//设置命令的文本 oc.CommandType = CommandType.Text;//设置命令的类型 oc.Connection = conn;//设置命令的连接 int x = oc.ExecuteNonQuery();//执行SQL语句 connClose();//关闭连接 return x; //返回一个影响行数 } /// <summary> /// 关闭数据库连接 /// </summary> public void connClose() { if (conn.State == ConnectionState.Open) {//判断数据库的连接状态，如果状态是打开的话就将它关闭 conn.Close(); } } } /// <summary> ///OleDbConnDb类,适用于Accsee数据库操作 /// </summary> public class OleDbConnDb { OleDbConnection conn = null; //连接数据库的对象 /// <summary> /// 构造函数,连接数据库，数据库连接字符在web.Config文件的AppSettings下的conStr /// </summary> public OleDbConnDb() { if (conn == null)//判断连接是否为空 { //string conString = "provider=sqloledb.1;data source=.;initial catalog=capucivar;user id=sa;pwd=";//连接数据库的字符串 string conString = System.Configuration.ConfigurationManager.AppSettings["conStr"];//连接数据库的字符串 conn = new OleDbConnection(conString); if (conn.State == ConnectionState.Closed) { conn.Open();//打开数据库连接 } } } /// <summary> /// 从数据库中查询数据的,返回为DataSet /// </summary> /// <param name="sql"></param> /// <returns></returns> public DataSet query(string sql) { DataSet ds = new DataSet();//DataSet是表的集合 OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);//从数据库中查询 da.Fill(ds);//将数据填充到DataSet connClose();//关闭连接 return ds;//返回结果 } /// <summary> /// 更新数据库 /// </summary> /// <param name="sql"></param> /// <returns></returns> public int update(string sql) { OleDbCommand oc = new OleDbCommand();//表示要对数据源执行的SQL语句或存储过程 oc.CommandText = sql;//设置命令的文本 oc.CommandType = CommandType.Text;//设置命令的类型 oc.Connection = conn;//设置命令的连接 int x = oc.ExecuteNonQuery();//执行SQL语句 connClose();//关闭连接 return x; //返回一个影响行数 } /// <summary> /// 关闭数据库连接 /// </summary> public void connClose() { if (conn.State == ConnectionState.Open) {//判断数据库的连接状态，如果状态是打开的话就将它关闭 conn.Close(); } } } using System; using System.Collections.Generic; using System.Text; using System.Data.OleDb; using System.Data; namespace CheckMaterial { static class SqlHelp { /// <summary> /// link access /// </summary> public static string connstring=@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=cost.mdb;Jet OLEDB:Database Password=1234567"; /// <summary> /// link sql /// </summary> ////public static string connstring = @"Provider=SQLOLEDB; data source=server1;user id=sa;password=;initial catalog=ytdb" /// <summary> /// link oracle /// </summary> //public static string connstring = @"Provider=OraOLEDB.Oracle;Persist Security Info=false;User ID=;Password=;Data Source="; //public static string connstring = @"Provider=msdaora.1;Persist Security Info=false;User ID=;Password=;Data Source="; /// <summary> /// link excel /// </summary> //public static string connstring = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelPath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'" /// <summary> /// SqlExecuteNonQuery /// </summary> /// <param name="sql"></param> /// <returns>using try catch to catch the error msg</returns> public static int SqlExecuteNonQuery(string sql) { //OleDbConnection conn = new OleDbConnection(connstring); using (OleDbConnection conn = new OleDbConnection(connstring)) { try { conn.Close(); conn.Open(); OleDbCommand cmd = new OleDbCommand(sql, conn); int i = cmd.ExecuteNonQuery(); conn.Close(); return i; } catch (Exception e) { conn.Close(); //System.Windows.Forms.MessageBox.Show(eee.Message); throw new Exception(e.Message); } } } /// <summary> /// SqlExecuteNonQuery /// </summary> /// <param name="sql"></param> /// <param name="_connstring"></param> /// <returns>using try catch to catch the error msg</returns> public static int SqlExecuteNonQuery(string sql, string _connstring) { //OleDbConnection conn = new OleDbConnection(connstring); using (OleDbConnection conn = new OleDbConnection(_connstring)) { try { conn.Close(); conn.Open(); OleDbCommand cmd = new OleDbCommand(sql, conn); int i = cmd.ExecuteNonQuery(); conn.Close(); return i; } catch (Exception e) { conn.Close(); //System.Windows.Forms.MessageBox.Show(eee.Message); throw new Exception(e.Message); } } } /// <summary> /// SqlExcuteScalar /// </summary> /// <param name="sql"></param> /// <returns>using try catch to catch the error msg</returns> public static object SqlExcuteScalar(string sql) { using (OleDbConnection conn = new OleDbConnection(connstring)) { try { conn.Close(); conn.Open(); OleDbCommand cmd = new OleDbCommand(sql, conn); object obj = cmd.ExecuteScalar(); conn.Close(); return obj; } catch (Exception e) { conn.Close(); throw new Exception(e.Message); } } } /// <summary> /// SqlExcuteScalar /// </summary> /// <param name="sql"></param> /// <param name="_connstring"></param> /// <returns>using try catch to catch the error msg</returns> public static object SqlExcuteScalar(string sql, string _connstring) { using (OleDbConnection conn = new OleDbConnection(_connstring)) { try { conn.Close(); conn.Open(); OleDbCommand cmd = new OleDbCommand(sql, conn); object obj = cmd.ExecuteScalar(); conn.Close(); return obj; } catch (Exception e) { conn.Close(); throw new Exception(e.Message); } } } /// <summary> /// getDataTableFormSql /// </summary> /// <param name="sql"></param> /// <returns>using try catch to catch the error msg</returns> public static DataTable getDataTableFormSql(string sql) { DataTable dt = new DataTable(); using (OleDbConnection conn = new OleDbConnection(connstring)) { try { OleDbCommand cmd = new OleDbCommand(sql, conn); OleDbDataAdapter oda = new OleDbDataAdapter(cmd); oda.Fill(dt); cmd.Dispose(); return dt; } catch (Exception e) { conn.Close(); throw new Exception(e.Message); } } } /// <summary> /// getDataTableFormSql /// </summary> /// <param name="sql"></param> /// <param name="_connstring"></param> /// <returns>using try catch to catch the error msg</returns> public static DataTable getDataTableFormSql(string sql, string _connstring) { DataTable dt = new DataTable(); using (OleDbConnection conn = new OleDbConnection(_connstring)) { try { OleDbCommand cmd = new OleDbCommand(sql, conn); OleDbDataAdapter oda = new OleDbDataAdapter(cmd); oda.Fill(dt); cmd.Dispose(); return dt; } catch (Exception e) { conn.Close(); throw new Exception(e.Message); } } } /// <summary> /// SQLExecuteTransaction /// </summary> /// <param name="sqls"></param> /// <returns>using try catch to catch the error msg</returns> public static bool SQLExecuteTransaction(string[] sqls) { using (OleDbConnection conn = new OleDbConnection(connstring)) { try { conn.Open(); OleDbTransaction trans = conn.BeginTransaction(); try { OleDbCommand cmd = conn.CreateCommand(); cmd.Transaction = trans; //cmd.CommandText foreach (string s in sqls) { cmd.CommandText = s; cmd.ExecuteNonQuery(); } trans.Commit(); return true; } catch (Exception e) { trans.Rollback(); throw new Exception(e.Message); } finally { conn.Close(); } } catch (Exception e) { //trans.Rollback(); throw new Exception(e.Message); } } } /// <summary> /// SQLExecuteTransaction /// </summary> /// <param name="sqls"></param> /// <param name="_connstring"></param> /// <returns>using try catch to catch the error msg</returns> public static bool SQLExecuteTransaction(string[] sqls, string _connstring) { using (OleDbConnection conn = new OleDbConnection(_connstring)) { try { conn.Open(); OleDbTransaction trans = conn.BeginTransaction(); try { OleDbCommand cmd = conn.CreateCommand(); cmd.Transaction = trans; //cmd.CommandText foreach (string s in sqls) { cmd.CommandText = s; cmd.ExecuteNonQuery(); } trans.Commit(); return true; } catch (Exception e) { trans.Rollback(); throw new Exception(e.Message); } finally { conn.Close(); } } catch (Exception e) { //trans.Rollback(); throw new Exception(e.Message); } } } } }