輕鬆操作MSSQL、SQL、Oracle

加入using

1. using System.Data.SqlClient;
2. using MySql.Data.MySqlClient;
3. using System.Data.OracleClient;

MSSQL：

1. public static SqlConnection OpenSqlConn(string Server, string Database, string dbuid, string dbpwd)
2. {
3. string cnstr = string.Format("server={0};database={1};uid={2};pwd={3};Connect Timeout = 180", Server, Database, dbuid, dbpwd);
4. SqlConnection icn = new SqlConnection();
5. icn.ConnectionString = cnstr;
6. if (icn.State == ConnectionState.Open) icn.Close();
7. icn.Open();
8. return icn;
9. }
11. public static DataTable GetSqlDataTable(string Server, string Database, string dbuid, string dbpwd, string SqlString)
12. {
13. DataTable myDataTable = new DataTable();
14. SqlConnection icn = null;
15. icn = OpenSqlConn(Server, Database, dbuid, dbpwd);
16. SqlCommand isc = new SqlCommand();
17. SqlDataAdapter da = new SqlDataAdapter(isc);
18. isc.Connection = icn;
19. isc.CommandText = SqlString;
20. isc.CommandTimeout = 600;
21. DataSet ds = new DataSet();
22. ds.Clear();
23. da.Fill(ds);
24. myDataTable = ds.Tables[0];
25. if (icn.State == ConnectionState.Open) icn.Close();
26. return myDataTable;
27. }
29. public static void SqlInsertUpdateDelete(string Server, string Database, string dbuid, string dbpwd, string SqlSelectString)
30. {
31. SqlConnection icn = OpenSqlConn(Server, Database, dbuid, dbpwd);
32. SqlCommand cmd = new SqlCommand(SqlSelectString, icn);
33. SqlTransaction mySqlTransaction = icn.BeginTransaction();
34. try
35. {
36. cmd.Transaction = mySqlTransaction;
37. cmd.ExecuteNonQuery();
38. mySqlTransaction.Commit();
39. }
40. catch (Exception ex)
41. {
42. mySqlTransaction.Rollback();
43. throw (ex);
44. }
45. if (icn.State == ConnectionState.Open) icn.Close();
46. }

MySQL：

1. public static MySqlConnection MyOpenConn(string Server, string Database, string dbuid, string dbpwd)
2. {
3. string cnstr = string.Format("server={0};database={1};uid={2};pwd={3};Connect Timeout = 180; CharSet=utf8", Server, Database, dbuid, dbpwd);
4. MySqlConnection icn = new MySqlConnection();
5. icn.ConnectionString = cnstr;
6. if (icn.State == ConnectionState.Open) icn.Close();
7. icn.Open();
8. return icn;
9. }
11. public static DataTable GetMyDataTable(string Server, string Database, string dbuid, string dbpwd, string SqlString)
12. {
13. DataTable myDataTable = new DataTable();
14. MySqlConnection icn = null;
15. icn = MyOpenConn(Server, Database, dbuid, dbpwd);
16. MySqlCommand isc = new MySqlCommand();
17. MySqlDataAdapter da = new MySqlDataAdapter(isc);
18. isc.Connection = icn;
19. isc.CommandText = SqlString;
20. isc.CommandTimeout = 600;
21. DataSet ds = new DataSet();
22. ds.Clear();
23. da.Fill(ds);
24. myDataTable = ds.Tables[0];
25. if (icn.State == ConnectionState.Open) icn.Close();
26. return myDataTable;
27. }
29. public static void MySqlInsertUpdateDelete(string Server, string Database, string dbuid, string dbpwd, string SqlSelectString)
30. {
31. MySqlConnection icn = MyOpenConn(Server, Database, dbuid, dbpwd);
32. MySqlCommand cmd = new MySqlCommand(SqlSelectString, icn);
33. MySqlTransaction mySqlTransaction = icn.BeginTransaction();
34. try
35. {
36. cmd.Transaction = mySqlTransaction;
37. cmd.ExecuteNonQuery();
38. mySqlTransaction.Commit();
39. }
40. catch (Exception ex)
41. {
42. mySqlTransaction.Rollback();
43. throw (ex);
44. }
45. if (icn.State == ConnectionState.Open) icn.Close();
46. }

Oracle：

1. public static OracleConnection OpenConn(string Server, string Database, string dbuid, string dbpwd)
2. {
3. string cnstr = string.Format("server={0};Data Source={1};User ID={2};Password={3}", Server, Database, dbuid, dbpwd);
4. OracleConnection icn = new OracleConnection();
5. icn.ConnectionString = cnstr;
6. if (icn.State == ConnectionState.Open) icn.Close();
7. icn.Open();
8. return icn;
9. }
11. public static DataTable GetDataTable(string Server, string Database, string dbuid, string dbpwd, string SqlString)
12. {
13. DataTable myDataTable = new DataTable();
14. OracleConnection icn = null;
15. icn = OpenConn(Server, Database, dbuid, dbpwd);
16. OracleCommand isc = new OracleCommand();
17. OracleDataAdapter da = new OracleDataAdapter(isc);
18. isc.Connection = icn;
19. isc.CommandText = SqlString;
20. isc.CommandTimeout = 600;
21. DataSet ds = new DataSet();
22. ds.Clear();
23. da.Fill(ds);
24. myDataTable = ds.Tables[0];
25. if (icn.State == ConnectionState.Open) icn.Close();
26. return myDataTable;
27. }
29. public static void OracleInsertUpdateDelete(string Server, string Database, string dbuid, string dbpwd, string SqlSelectString)
30. {
31. OracleConnection icn = OpenConn(Server, Database, dbuid, dbpwd);
32. OracleCommand cmd = new OracleCommand(SqlSelectString, icn);
33. OracleTransaction mySqlTransaction = icn.BeginTransaction();
34. try
35. {
36. cmd.Transaction = mySqlTransaction;
37. cmd.ExecuteNonQuery();
38. mySqlTransaction.Commit();
39. }
40. catch (Exception ex)
41. {
42. mySqlTransaction.Rollback();
43. throw (ex);
44. }
45. if (icn.State == ConnectionState.Open) icn.Close();
46. }

應用(以Oracle為例)：

1. string sql = "select * from TableName";
2. DataTable myDataTable = GetDataTable("ServerName","DataBaseName","UserName","PassWord",sql);