ADO .NET直接操作数据库-1,ORACLE;2,SQL Server;3,MySql。
ADO .NET直接操作数据库,数据库类型分为三种,1,oralce,2,sql server,3,mysql。
操作功能分为:连接数据库,对表进行增删改查。
private static SqlConnection CreateConnectionSQL()
{
string connstr = System.Configuration.ConfigurationManager.AppSettings["sqlserver1"];
SqlConnection conn = new SqlConnection(connstr);
return conn;
}
private static OracleConnection CreateConnectionORA()
{
string orcalestring = System.Configuration.ConfigurationManager.AppSettings["Oracle"];
OracleConnection conn = new OracleConnection(orcalestring);
return conn;
}
private static MySqlConnection CreateConnectionMySql()
{
string connstr = System.Configuration.ConfigurationManager.AppSettings["Mysql"];
MySqlConnection conn = new MySqlConnection(connstr);
return conn;
}
/// <summary>
/// 查询数据返回第一行第一列
/// </summary>
/// <param name="type">所用数据库类型Oracle或者 sql server</param>
/// <param name="sql">sql语句</param>
/// <returns></returns>
public static string DB_SELECT_ONE(string type, string sql)
{
string sql_return = string.Empty;
if (type == "Oracle")
{
OracleConnection conn = CreateConnectionORA();
OracleCommand cmd = new OracleCommand(sql, conn);
cmd.CommandTimeout = 36000;
try
{
conn.Open();
sql_return = cmd.ExecuteScalar().ToString();
}
catch (Exception ex)
{
sql_return = "数据单条出错!原因为:" + ex.Message.ToString().Replace("\n", "\\n").Trim();
}
finally
{
cmd.Dispose();
conn.Close();
}
}
else if (type == "Sql")
{
SqlConnection conn = CreateConnectionSQL();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandTimeout = 36000;
try
{
conn.Open();
sql_return = cmd.ExecuteScalar().ToString();
}
catch (Exception ex)
{
sql_return = "数据单条出错!原因为:" + ex.Message.ToString();
}
finally
{
cmd.Dispose();
conn.Close();
}
}
else if (type == "MySql")
{
MySqlConnection conn = CreateConnectionMySql();
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.CommandTimeout = 36000;
try
{
conn.Open();
sql_return = cmd.ExecuteScalar().ToString();
}
catch (Exception ex)
{
sql_return = "数据单条出错!原因为:" + ex.Message.ToString();
}
finally
{
cmd.Dispose();
conn.Close();
}
}
return sql_return;
}
/// <summary>
/// 进行数据的查询返回datatable
/// </summary>
/// <param name="type">所用数据库类型Oracle或者 sql server</param>
/// <param name="sql">sql语句</param>
/// <returns></returns>
public static DataTable DB_SELECT_ALL(string type, string sql)
{
DataTable dt_return = new DataTable();
if (type == "Oracle")
{
OracleConnection conn = CreateConnectionORA();
OracleCommand cmd = new OracleCommand(sql, conn);
cmd.CommandTimeout = 36000;
OracleDataAdapter dap = new OracleDataAdapter(cmd);
try
{
conn.Open();
dap.Fill(dt_return);
}
catch (Exception ex)
{
dt_return.Columns.Add(new DataColumn("error", Type.GetType("System.String")));
dt_return.Rows.Add("数据多条查询出错!原因为:" + ex.Message.ToString());
}
finally
{
dap.Dispose();
conn.Close();
}
}
else if (type == "Sql")
{
SqlConnection conn = CreateConnectionSQL();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandTimeout = 36000;
SqlDataAdapter dap = new SqlDataAdapter(cmd);
try
{
conn.Open();
dap.Fill(dt_return);
}
catch (Exception ex)
{
dt_return.Columns.Add(new DataColumn("error", Type.GetType("System.String")));
dt_return.Rows.Add("数据多条查询出错!原因为:" + ex.Message.ToString());
}
finally
{
dap.Dispose();
conn.Close();
}
}
else if (type == "MySql")
{
MySqlConnection conn = CreateConnectionMySql();
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.CommandTimeout = 36000;
MySqlDataAdapter dap = new MySqlDataAdapter(cmd);
try
{
conn.Open();
dap.Fill(dt_return);
}
catch (Exception ex)
{
dt_return.Columns.Add(new DataColumn("error", Type.GetType("System.String")));
dt_return.Rows.Add("数据多条查询出错!原因为:" + ex.Message.ToString());
}
finally
{
dap.Dispose();
conn.Close();
}
}
return dt_return;
}
/// <summary>
/// 进行数据的删除及更新
/// </summary>
/// <param name="type">所用数据库类型Oracle或者Sql Server</param>
/// <param name="sql">sql语句</param>
/// <param name="rescount">返回更新行数</param>
/// <param name="resmsg">返回信息</param>
public static void DB_RUN(string type, string sql, out int rescount, out string resmsg)
{
rescount = -1;
resmsg = string.Empty;
if (type == "Oracle")
{
using (OracleConnection conn = CreateConnectionORA())
{
conn.Open();
OracleTransaction transaction = conn.BeginTransaction();
try
{
using (OracleCommand cmd = new OracleCommand())
{
cmd.Connection = conn;
cmd.CommandTimeout = 36000;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
cmd.Transaction = transaction;
rescount = cmd.ExecuteNonQuery();
transaction.Commit();
}
}
catch (Exception ex)
{
transaction.Rollback(); // 事务回滚
resmsg = "ErrCodeX:" + ex.Message.ToString();
}
finally
{
conn.Close();
}
}
}
else if (type == "Sql")
{
using (SqlConnection conn = CreateConnectionSQL())
{
conn.Open();
SqlTransaction transaction = conn.BeginTransaction();
try
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandTimeout = 36000;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
cmd.Transaction = transaction;
rescount = cmd.ExecuteNonQuery();
transaction.Commit();
}
}
catch (Exception ex)
{
transaction.Rollback(); // 事务回滚
resmsg = "ErrCodeX:" + ex.Message.ToString();
}
finally
{
conn.Close();
}
}
}
else if (type == "MySql")
{
using (MySqlConnection conn = CreateConnectionMySql())
{
conn.Open();
MySqlTransaction transaction = conn.BeginTransaction();
try
{
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = conn;
cmd.CommandTimeout = 36000;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
cmd.Transaction = transaction;
rescount = cmd.ExecuteNonQuery();
transaction.Commit();
}
}
catch (Exception ex)
{
transaction.Rollback(); // 事务回滚
resmsg = "ErrCodeX:" + ex.Message.ToString();
}
finally
{
conn.Close();
}
}
}
}
需引用的相关命名空间
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using Oracle.ManagedDataAccess.Client;