62,046
社区成员
发帖
与我相关
我的任务
分享
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.OracleClient;
using System.Transactions;
namespace Taobao.SqlHelp
{
public class DataAccessProvider
{
private static string connString;
private static string providerName;
static DataAccessProvider()
{
ConnectionStringSettings setting = ConfigurationManager.ConnectionStrings["DSN"];
connString = setting.ConnectionString;
providerName = setting.ProviderName;
System.Diagnostics.Debug.WriteLine(providerName);
}
public static DbConnection GetConneciton()
{
DbProviderFactory factroy = DbProviderFactories.GetFactory(providerName);
DbConnection conn = factroy.CreateConnection();
conn.ConnectionString = connString;
return conn;
}
private static DbDataAdapter GetDataAdapter()
{
DbProviderFactory factroy = DbProviderFactories.GetFactory(providerName);
DbDataAdapter da = factroy.CreateDataAdapter();
return da;
}
public static DbCommand GetCommand(string commandText, CommandType commandType)
{
DbConnection conn = GetConneciton();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = commandText;
cmd.CommandType = commandType;
return cmd;
}
public static DbDataReader GetDataReader(string commandText, CommandType commandType)
{
DbCommand cmd = GetCommand(commandText, commandType);
cmd.Connection.Open();
DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
public static bool ExecuteNonQuery(string commandText, CommandType commandType)
{
DbCommand cmd = GetCommand(commandText, commandType);
DbConnection conn = cmd.Connection;
DbTransaction trans = null;
bool result;
try
{
conn.Open();
trans = conn.BeginTransaction();
cmd.ExecuteNonQuery();
trans.Commit();
result = true;
}
catch (SqlException)
{
if (trans != null)
{
trans.Rollback();
}
result = false;
}
finally
{
if (conn != null && conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return result;
}
public static object ExecScalar(string commandText, CommandType commandType)
{
DbCommand cmd = GetCommand(commandText, commandType);
DbConnection conn = cmd.Connection;
conn.Open();
object obj = cmd.ExecuteScalar();
conn.Close();
return obj;
}
public static DataTable GetDataTable(string commandText, CommandType commandType)
{
return GetDataTable(commandText, commandType, 0, 0);
}
public static DataTable GetDataTable(string commandText, CommandType commandType, int start, int count)
{
DbDataAdapter da = GetDataAdapter();
DbCommand cmd = GetCommand(commandText, commandType);
da.SelectCommand = cmd;
DataTable dt = new DataTable();
DbConnection conn = cmd.Connection;
conn.Open();
if (start == 0 && count == 0)
{
da.Fill(dt);
}
else
{
da.Fill(start, count, dt);
}
conn.Close();
return dt;
}
}
}
<configuration>
<connectionStrings>
<add name="DSN" connectionString="server=(local);DataBase=dataname;uid=user;pwd=" providerName="System.Data.SqlClient"/>
</connectionStrings>
</configuration>
ConnectionStringSettings setting = ConfigurationManager.ConnectionStrings["DSN"];
connString = setting.ConnectionString;