/// <summary>
/// 根据数据库类型获得某种数据库系列对象创建工厂
/// </summary>
/// <param name="databaseType">数据库类型</param>
/// <returns>创建工厂</returns>
public static DbProviderFactory GetDbProviderFactory(JlDatabaseType databaseType = JlDatabaseType.SqlServer)
{
//PostgreSql需要config中添加
//<system.data >
// <DbProviderFactories >
// <add name = "Npgsql Data Provider" invariant = "Npgsql" description = "Data Provider for PostgreSQL" type = "Npgsql.NpgsqlFactory, Npgsql" />
// </DbProviderFactories >
//</system.data >
DbProviderFactory instance = null;
switch (databaseType)
{
case JlDatabaseType.SqlServer:
instance = SqlClientFactory.Instance;
break;
case JlDatabaseType.OleDb:
instance = OleDbFactory.Instance;
break;
case JlDatabaseType.MySql:
instance = MySqlClientFactory.Instance;
break;
case JlDatabaseType.PostgreSql:
instance = DbProviderFactories.GetFactory("Npgsql");
break;
}
return(instance);
}
public static string Map4J(string dbType, bool isNullable = false, JlDatabaseType databaseType = JlDatabaseType.MySql)
{
switch (databaseType)
{
case JlDatabaseType.MySql:
return(Map4J_Mysql(dbType, isNullable));
case JlDatabaseType.PostgreSql:
return(Map4J_PostgreSql(dbType, isNullable));
default:
return(Map4J_Mysql(dbType, isNullable));
}
}
public static List <string> GetDatabaseTables(string connectionString, JlDatabaseType dbType)
{
switch (dbType)
{
case JlDatabaseType.MySql:
{
return(GetDatabaseTables_MySql(connectionString));
}
case JlDatabaseType.SqlServer:
{
return(GetDatabaseTables_SqlServer(connectionString));
}
case JlDatabaseType.PostgreSql:
{
return(GetDatabaseTables_PostgreSql(connectionString));
}
default: return(null);
}
}
public static List <JlFieldDescription> GetDatabaseColumns(string connectionString, string tableName, JlDatabaseType dbType)
{
switch (dbType)
{
case JlDatabaseType.MySql:
{
return(GetDatabaseColumns_MySql(connectionString, tableName));
}
case JlDatabaseType.SqlServer:
{
return(GetDatabaseColumns_SqlServer(connectionString, tableName));
}
case JlDatabaseType.PostgreSql:
{
return(GetDatabaseColumns_PostgreSql(connectionString, tableName));
}
default: return(null);
}
}
/// <summary>
/// 返回数据集的第一行第一列。数据库中为Null或空，都返回Null
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="sentence">SQL命令或存储过程名</param>
/// <param name="parameters">参数数组</param>
/// <param name="databaseType">数据库类型</param>
/// <returns>数据集的第一行第一列</returns>
public static object ExecuteScalar(string connectionString, string sentence, DbParameter[] parameters = null, JlDatabaseType databaseType = JlDatabaseType.SqlServer)
{
object result = null;
DbProviderFactory factory = GetDbProviderFactory(databaseType);
using (DbConnection dbConnection = factory.CreateConnection())
{
dbConnection.ConnectionString = connectionString;
using (DbCommand dbCommand = dbConnection.CreateCommand())
{
dbCommand.CommandText = sentence;
dbCommand.CommandTimeout = 0;
if (parameters != null)
{
dbCommand.Parameters.AddRange(parameters);
}
dbConnection.Open();
result = dbCommand.ExecuteScalar();
}
}
//如果返回的是DBNull类型或者是空，则返回null
if (result == JlObject.NullObject || result == DBNull.Value || result.ToString().Trim().Length == 0)
{
result = null;
}
return(result);
}
/// <summary>
/// 执行SELECT以及返回数据集的存储过程，返回读取器。读取器关闭时，连接会自动关闭。不建议在BS项目中使用，因为传统的DataSet方式要更容易控制缓存。
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="sentence">SQL命令或存储过程名</param>
/// <param name="parameters">参数数组</param>
/// <param name="databaseType">数据库类型</param>
/// <returns>读取器</returns>
public static DbDataReader ExecuteReader(string connectionString, string sentence, DbParameter[] parameters = null, JlDatabaseType databaseType = JlDatabaseType.SqlServer)
{
DbProviderFactory factory = GetDbProviderFactory(databaseType);
DbConnection dbConnection = factory.CreateConnection();
dbConnection.ConnectionString = connectionString;
try
{
using (DbCommand dbCommand = dbConnection.CreateCommand())
{
dbCommand.CommandText = sentence;
dbCommand.CommandTimeout = 0;
if (parameters != null)
{
dbCommand.Parameters.AddRange(parameters);
}
dbConnection.Open();
return(dbCommand.ExecuteReader(CommandBehavior.CloseConnection));
}
}
catch (Exception exception)
{
//如果发生异常才关闭连接，然后抛出异常信息。如果未发生异常，不能关闭连接，连接是由客户端程序员控制。
dbConnection.Close();
throw exception;
}
}
/// <summary>
/// 执行INSERT、UPDATE、DELETE以及不返回数据集的存储过程
/// </summary>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="sentence">SQL命令或存储过程名</param>
/// <param name="parameters">参数数组</param>
/// <param name="databaseType">数据库类型</param>
/// <returns>影响的行数</returns>
public static int ExecuteNonQuery(string connectionString, string sentence, DbParameter[] parameters = null, JlDatabaseType databaseType = JlDatabaseType.SqlServer)
{
DbProviderFactory factory = GetDbProviderFactory(databaseType);
using (DbConnection dbConnection = factory.CreateConnection())
{
dbConnection.ConnectionString = connectionString;
using (DbCommand dbCommand = dbConnection.CreateCommand())
{
dbCommand.CommandText = sentence;
dbCommand.CommandTimeout = 0;
if (parameters != null)
{
dbCommand.Parameters.AddRange(parameters);
}
dbConnection.Open();
return(dbCommand.ExecuteNonQuery());
}
}
}
/// <summary>
/// 填充数据集
/// </summary>
/// <param name="dataTable">数据表（可以是强类型的数据表）</param>
/// <param name="connectionString">数据库连接字符串</param>
/// <param name="sentence">SQL语句或存储过程名</param>
/// <param name="parameters">参数数组</param>
/// <param name="databaseType">数据库类型</param>
public static void Fill(string connectionString, string sentence, DataTable dataTable, DbParameter[] parameters = null, JlDatabaseType databaseType = JlDatabaseType.SqlServer)
{
DbProviderFactory factory = GetDbProviderFactory(databaseType);
DbConnection dbConnection = factory.CreateConnection();
dbConnection.ConnectionString = connectionString;
using (DbCommand dbCommand = dbConnection.CreateCommand())
{
dbCommand.CommandText = sentence;
dbCommand.CommandTimeout = 0;
if (parameters != null)
{
dbCommand.Parameters.AddRange(parameters);
}
using (DbDataAdapter dbDataAdapter = factory.CreateDataAdapter())
{
dbDataAdapter.SelectCommand = dbCommand;
dbDataAdapter.Fill(dataTable);
}
}
}
