/// <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); } } }