private static void CreateExtensionTable(DbConnection connection, ExtendedTable extendedTable)
{
using (var queryCommand = connection.CreateCommand())
{
string primaryKeyColumn = GetPrimaryKey(connection, extendedTable.BaseTableName);
queryCommand.CommandText = string.Format(
"SELECT {0} FROM {1}",
primaryKeyColumn,
extendedTable.BaseTableName);
string dataType = null;
string properties =
string.Join(
", ",
new [] { string.Format("{0} uniqueidentifier PRIMARY KEY", primaryKeyColumn) }
.Union(
from property in extendedTable.ExtendedProperties
where TypeMapping.TryGetValue(property.PropertyType, out dataType)
select string.Format("{0} {1}", property.PropertyName, dataType)));
var creationCommand = connection.CreateCommand();
creationCommand.CommandText = string.Format(
"CREATE TABLE {0} ({1});",
extendedTable.TableName,
properties);
creationCommand.ExecuteNonQuery();
using (DbDataReader reader = queryCommand.ExecuteReader())
{
while (reader.Read())
{
object keyValue = reader[primaryKeyColumn];
creationCommand = connection.CreateCommand();
creationCommand.CommandText = string.Format(
"INSERT INTO {0} ({1}) VALUES (@value);",
extendedTable.TableName,
primaryKeyColumn);
var parameter = creationCommand.CreateParameter();
parameter.DbType = DbType.Guid;
parameter.ParameterName = "@value";
parameter.Value = keyValue;
creationCommand.Parameters.Add(parameter);
creationCommand.ExecuteNonQuery();
}
}
}
}
public void Insert(DbConnection con)
{
DbCommand cmd = con.CreateCommand();
cmd.CommandText = "INSERT INTO WordInstance ( InsWrdCod , InsDocCod , InsCount , InsPos ) VALUES( ? , ? , ? , ? )";
cmd.Connection = con;
DbParameter parm = cmd.CreateParameter();
parm.DbType = System.Data.DbType.Int64;
parm.Value = WordCode;
cmd.Parameters.Add(parm);
parm = cmd.CreateParameter();
parm.DbType = System.Data.DbType.Int64;
parm.Value = DocumentCode;
cmd.Parameters.Add(parm);
parm = cmd.CreateParameter();
parm.DbType = System.Data.DbType.Int64;
parm.Value = Count;
cmd.Parameters.Add(parm);
parm = cmd.CreateParameter();
parm.DbType = System.Data.DbType.Int64;
parm.Value = Positions;
cmd.Parameters.Add(parm);
if (cmd.ExecuteNonQuery() != 1)
throw new Exception("Error inserting WordInstance");
}
public static void UpgradeDatabase(DbConnection connection, DatabaseType databaseType)
{
// Figure out the current version of the schema
int currentVersion;
using (DbCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT val FROM setup WHERE `key`='schemaVersion';";
using (DbDataReader reader = cmd.ExecuteReader())
{
if (!reader.Read())
currentVersion = 1;
else
currentVersion = int.Parse(reader.GetString(reader.GetOrdinal("val")));
}
}
// Now get the set of migrations we have avaiable
List<int> migrationVersions = GetMigrationVersions(databaseType);
foreach (int migration in migrationVersions)
{
if (currentVersion < migration)
{
PerformMigration(connection, databaseType, migration);
}
}
}
/// <summary>
/// Creates a select command.
/// </summary>
/// <param name="connection">The connection to create the command with.</param>
/// <param name="status">The job status to filter results on.</param>
/// <param name="count">The maximum number of results to select.</param>
/// <param name="before">The queued-after date to filter on.</param>
/// <returns>A select command.</returns>
protected override DbCommand CreateSelectCommand(DbConnection connection, JobStatus status, int count, DateTime before)
{
const string SqlStart = "SELECT{0} * FROM {1} WHERE {2} = {3} AND {4} < {5} ORDER BY {4};";
DbCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.Parameters.Add(this.ParameterWithValue(this.ParameterName("Status"), status.ToString()));
command.Parameters.Add(this.ParameterWithValue(this.ParameterName("Before"), before));
string countString = String.Empty;
if (count > 0)
{
countString = String.Format(CultureInfo.InvariantCulture, " TOP ({0})", this.ParameterName("Count"));
command.Parameters.Add(this.ParameterWithValue(this.ParameterName("Count"), count));
}
command.CommandText = String.Format(
CultureInfo.InvariantCulture,
SqlStart,
countString,
this.TableName,
this.ColumnName("Status"),
this.ParameterName("Status"),
this.ColumnName("QueueDate"),
this.ParameterName("Before"));
return command;
}
/// <summary>
/// 初次使用创建数据库
/// </summary>
public static void CreateDataBase()
{
try
{
bool isExist = File.Exists(DataPath);
if (!isExist)
{
SQLiteConnection.CreateFile(DataPath);
}
conn = new SQLiteConnection(ConnStr);
conn.Open();
comm = conn.CreateCommand();
if (!isExist)
{
String sql = "Create table ReceiveData(DeviceNum INTEGER , Temperature INTEGER , Humidity INTEGER , Tyrere INTEGER , MotorState INTEGER , ErrorNum INTEGER , CreateDate DateTime);";
comm.CommandText = sql;
comm.CommandType = CommandType.Text;
comm.ExecuteNonQuery();
}
}
catch (Exception e)
{
}
}
public override DbCommand CreateCommand(DbConnection conn)
{
var cmd = conn.CreateCommand();
var pi = cmd.GetType().GetProperty("BindByName");
if (pi != null) pi.SetValue(cmd, true, null);
return cmd;
}
/// <summary>
/// 构造函数--重载方法二
/// </summary>
/// <param name="connectionstring">数据库链接</param>
/// <param name="databasetype">数据库的类型</param>
public DBHelper(string connectionstring, string databasetype)
{
MyFactory = DbProviderFactories.GetFactory(databasetype);
MyConnection = MyFactory.CreateConnection();
MyConnection.ConnectionString = (databasetype.ToString() == "System.Data.OleDb") ? ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + GetDataPath(connectionstring) + ";") : (connectionstring);
MyCommand = MyConnection.CreateCommand();
}
protected override int FindVersion(DbConnection conn, string type)
{
int version = 0;
using (DbCommand cmd = conn.CreateCommand())
{
try
{
cmd.CommandText = "select top 1 version from migrations where name = '" + type + "' order by version desc"; //Must be
using (IDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
version = Convert.ToInt32(reader["version"]);
}
reader.Close();
}
}
catch
{
// Return -1 to indicate table does not exist
return -1;
}
}
return version;
}
public static string GenerateAllTables(this System.Data.Common.DbConnection connection, GeneratorBehavior generatorBehavior = GeneratorBehavior.Default)
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
var conneciontName = connection.GetType().Name.ToLower();
var tables = new List <string>();
var sql = generatorBehavior.HasFlag(GeneratorBehavior.View) ? TableSchemaSqls[conneciontName].Split("where")[0] : TableSchemaSqls[conneciontName];
using (var command = connection.CreateCommand(sql))
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
tables.Add(reader.GetString(0));
}
}
var sb = new StringBuilder();
sb.AppendLine("namespace Models { ");
tables.ForEach(table => sb.Append(connection.GenerateClass(
string.Format(QuerySqls[conneciontName], table), table, generatorBehavior: generatorBehavior
)));
sb.AppendLine("}");
return(sb.ToString());
}
protected override DataTable IdentityColumns(string tableName, DbConnection connection)
{
DataTable dt = CreateDataTable(IdentityColumnsCollectionName);
const string sqlCommand = @"SELECT
TABLE_SCHEMA AS tabschema,
TABLE_NAME As TableName,
COLUMN_NAME As ColumnName
FROM QSYS2.SYSCOLUMNS
WHERE TABLE_NAME = @tableName or @tableName Is NULL
AND TABLE_SCHEMA = @schemaOwner or @schemaOwner Is NULL
AND HAS_DEFAULT = 'I' OR HAS_DEFAULT = 'J'";
//I: The column is defined with the AS IDENTITY and GENERATED ALWAYS attributes.
//J: The column is defined with the AS IDENTITY and GENERATED BY DEFAULT attributes.
//create a dataadaptor and fill it
using (DbDataAdapter da = Factory.CreateDataAdapter())
{
da.SelectCommand = connection.CreateCommand();
da.SelectCommand.CommandText = sqlCommand;
AddTableNameSchemaParameters(da.SelectCommand, tableName);
da.Fill(dt);
return dt;
}
}
protected static void Exec(DbConnection conn, string sql)
{
using(var cmd = conn.CreateCommand()) {
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
public int ExecuteSqlNonQuery(StringBuilder sbSql, string providerName, string connectionString, out string strError)
{
int intEffected = -1; // return number rows effected
strError = "";
try
{
conn = CreateDbConnection(providerName, connectionString, out strError);
conn.Open();
}
catch (Exception ex)
{
strError = ex.Message;
return(-1);
}
try
{
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = sbSql.ToString();
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
intEffected = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
strError = ex.Message;
return(-1);
}
return(intEffected);
}
}//Execute(string sql)
/// <summary>
/// 执行Sql语句
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="Conn">数据库连接对象</param>
/// <param name="param">参数</param>
/// <returns>返回受影响行数</returns>
static public int Execute(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbParameter[] param)
{
if (Conn == null)
{
DBClassHelper.ErrLog("DBClassHelper.Execute(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbParameter[] param):连接对象为空!");
return(0);
}
if (Conn.State == System.Data.ConnectionState.Closed)
{
Conn.Open();
}
System.Data.IDbCommand cmd = Conn.CreateCommand();
cmd.CommandTimeout = 180;
cmd.CommandText = sql;
for (int i = 0; i < param.Length; i++)
{
cmd.Parameters.Add(param[i]);
}
try
{
return(cmd.ExecuteNonQuery());
}
catch (Exception ex)
{
DBClassHelper.ErrLog("DBClassHelper.Execute(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbParameter[] param):" + ex.Message + "/nsql=" + sql);
return(0);
}
}//Execute(string sql,System.Data.IDataParameter[] param)
private static void CreateTable(DbConnection connection)
{
using (var command = connection.CreateCommand())
{
command.CommandText = @"
create table Person (
PersonId integer,
Name nvarchar(100),
EMail nvarchar(100),
ZipCode nvarchar(100),
City nvarchar(100),
primary key(PersonId)
);
create table History (
PersonId integer,
HistoryNo integer,
Content nvarchar(100),
primary key(PersonId, HistoryNo)
);
create table IdManege (
TableName nvarchar(100),
Id integer,
Guid nvarchar(100),
primary key (TableName, Id)
);
";
var ret = command.ExecuteNonQuery();
}
}
}//ExecuteQueryToDataTable(string sql)
/// <summary>
/// 执行查询返回DataReader
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="Conn">连接对象</param>
/// <returns>成功时返回Reader对象，失败时返回null</returns>
static public System.Data.IDataReader ExecuteQuery(string sql, System.Data.Common.DbConnection Conn)
{
System.Data.IDataReader reader = null;
if (Conn == null)
{
return(null);
}
try
{
if (Conn.State == System.Data.ConnectionState.Closed)
{
Conn.Open();
}
System.Data.IDbCommand cmd = Conn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandTimeout = 180;
reader = cmd.ExecuteReader();
return(reader);
}
catch (Exception ex)
{
if (ex.Message.Contains("死锁"))
{
WriteLog(ex.Message + " 再做列锁循环！ExecuteQuery");
System.Threading.Thread.Sleep(200);
return(ExecuteQuery(sql, Conn));
}
else
{
DBClassHelper.ErrLog("DBClassHelper.ExecuteQuery(string sql, System.Data.Common.DbConnection Conn):" + ex.Message + "; \n sql =" + sql);
}
return(null);
}
}//ExecuteQuery(string sql)
public void select(DbConnection conn)
{
if (conn.State != ConnectionState.Open)
conn.Open();
DataTable tb = new DataTable();
DbCommand dbCommand = conn.CreateCommand();
string selectQuery = DISCOUNTCURVESETTING_Table_DAO.SelectQuery_;
selectQuery = selectQuery.Replace("@KeyColumnValue@", this.KeyColumn_);
dbCommand.CommandText = selectQuery;
//DbDataAdapter dataAdapter = new DbDataAdapter(dbCommand, conn);
DbDataAdapter dataAdapter = DataBaseConnectManager.CreateDataAdapter(dbCommand, conn);
dataAdapter.Fill(tb);
DataRow[] dr = tb.Select();
int rowCount = dr.Length;
if (!(rowCount == 0 || rowCount == 1))
{ throw new Exception(); }
foreach (DataRow item in dr)
{
this.CURRENCY_ = item[0].ToString();
this.CURVECODE_ = item[1].ToString();
this.PROVIDER_ = item[2].ToString();
}
}
//Keo dai thoi gian ket noi khac phuc TimeOut
internal static DbCommand CreateCommand()
{
DbCommand x = DbConnection.CreateCommand();
x.CommandTimeout = int.MaxValue;
return(x);
}
protected virtual DbCommand CreateCreateCommand(DbConnection connection)
{
var command = connection.CreateCommand();
command.CommandType = System.Data.CommandType.Text;
command.CommandText = Settings.CreateCommandText;
return command;
}
public DatabaseCommand(string query, DbConnection connection, Profiler profiler = null, params object[] arguments)
{
CommandProfiler = profiler;
Query = string.Format(query, arguments);
Command = connection.CreateCommand();
Command.CommandText = query;
}
/// <summary>
/// Create a new data parameter from the data source.
/// </summary>
/// <param name="dbConnection"> The database connection. </param>
/// <param name="dbTransaction"> An optional local database transaction. </param>
/// <param name="sourceColumn"> Specifies the source column. </param>
/// <param name="parameterDirection"> Specifies the parameter direction. </param>
/// <param name="parameterDbType"> Specifies the parameter provider-(in)dependent type. </param>
/// <param name="parameterSize"> Specifies the parameter size. </param>
/// <param name="parameterPrecision"> Specifies the parameter precision. </param>
/// <param name="parameterScale"> Specifies the parameter scale. </param>
/// <param name="parameterNullable"> Specifies the parameter nullable-ness. </param>
/// <param name="parameterName"> Specifies the parameter name. </param>
/// <param name="parameterValue"> Specifies the parameter value. </param>
/// <returns> The data parameter with the specified properties set. </returns>
public DbParameter CreateParameter(DbConnection dbConnection, DbTransaction dbTransaction, string sourceColumn, ParameterDirection parameterDirection, DbType parameterDbType, int parameterSize, byte parameterPrecision, byte parameterScale, bool parameterNullable, string parameterName, object parameterValue)
{
DbParameter dbParameter;
OnlyWhen._PROFILE_ThenPrint(string.Format("{0}::CreateParameter(...): enter", typeof(AdoNetStreamingFascade).Name));
if ((object)dbConnection == null)
throw new ArgumentNullException(nameof(dbConnection));
using (DbCommand dbCommand = dbConnection.CreateCommand())
dbParameter = dbCommand.CreateParameter();
dbParameter.ParameterName = parameterName;
dbParameter.Size = parameterSize;
dbParameter.Value = parameterValue;
dbParameter.Direction = parameterDirection;
dbParameter.DbType = parameterDbType;
this.ReflectionFascade.SetLogicalPropertyValue(dbParameter, "IsNullable", parameterNullable, true, false);
dbParameter.Precision = parameterPrecision;
dbParameter.Scale = parameterScale;
dbParameter.SourceColumn = sourceColumn;
OnlyWhen._PROFILE_ThenPrint(string.Format("{0}::CreateParameter(...): return parameter", typeof(AdoNetStreamingFascade).Name));
return dbParameter;
}
public void Delete( User user )
{
using ( connection = factory.CreateDbConnection () )
{
try
{
DbCommand cmd = connection.CreateCommand ();
cmd.CommandText = "DELETE FROM [" + TABLENAME + @"]
WHERE [ContactId] like @contactId AND
[Login] like @login AND
[Password] like @password AND
[Admin] like @isAdmin";
FactoryUtility.AddParameterWithValue ( cmd, "@contactId", user.Contact.Id );
FactoryUtility.AddParameterWithValue ( cmd, "@login", user.Login );
FactoryUtility.AddParameterWithValue ( cmd, "@password", user.Password );
FactoryUtility.AddParameterWithValue ( cmd, "@isAdmin", user.IsAdmin );
cmd.Connection = connection;
cmd.ExecuteNonQuery ();
}
catch ( Exception ex )
{
MessageBox.Show ( ex.Message );
}
}
}
public bool isLoginExists( User user )
{
using ( connection = factory.CreateDbConnection () )
{
try
{
if ( isTableExists () == true )
{
DbCommand cmd = connection.CreateCommand ();
cmd.CommandText = "SELECT COUNT( *) from [" + TABLENAME + "] where [Login] like @login";
FactoryUtility.AddParameterWithValue ( cmd, "@login", user.Login );
cmd.Connection = connection;
int loginCount = ( int ) cmd.ExecuteScalar ();
if ( loginCount > 0 )
{
return true;
}
return false;
}
else
{
return false;
}
}
catch ( Exception ex )
{
MessageBox.Show ( ex.Message );
return true;
}
}
}
protected override void Update( DbConnection connection )
{
DbCommand command = connection.CreateCommand();
command.CommandText =
@"UPDATE Contacts SET FirstName = @FirstName,
LastName = @LastName,
Company = @Company,
Position = @Position,
AddressLine1 = @AddressLine1,
AddressLine2 = @AddressLine2,
Zip = @Zip,
Town = @Town,
CountryId = @CountryId,
Notes = @Notes
WHERE ContactId = @ContactId";
command.AddParameter( "FirstName", DbType.String, this.FirstName );
command.AddParameter( "LastName", DbType.String, this.LastName );
command.AddParameter( "Company", DbType.String, this.Company );
command.AddParameter( "Position", DbType.String, this.Position );
command.AddParameter( "AddressLine1", DbType.String, this.AddressLine1 );
command.AddParameter( "AddressLine2", DbType.String, this.AddressLine2 );
command.AddParameter( "Zip", DbType.String, this.Zip );
command.AddParameter( "Town", DbType.String, this.Town );
command.AddParameter( "CountryId", DbType.Int32, this.CountryId );
command.AddParameter( "Notes", DbType.String, this.Notes );
command.AddParameter( "ContactId", DbType.Int32, this.Id );
command.ExecuteNonQuery();
}
protected override DataTable GetForeignKeys(DbConnection connection)
{
var t = new DataTable("ForeignKeys");
using (var c = connection.CreateCommand())
{
c.CommandText = @"
SELECT
rc.CONSTRAINT_NAME AS 'CONSTRAINT_NAME',
'FOREIGN KEY' AS 'CONSTRAINT_TYPE',
rcu_from.TABLE_NAME AS 'TABLE_NAME',
rcu_from.TABLE_SCHEMA AS 'TABLE_SCHEMA',
rcu_from.COLUMN_NAME AS 'FKEY_FROM_COLUMN',
rcu_to.TABLE_SCHEMA AS 'FKEY_TO_SCHEMA',
rcu_to.TABLE_NAME AS 'FKEY_TO_TABLE',
rcu_to.COLUMN_NAME AS 'FKEY_TO_COLUMN'
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
INNER JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE rcu_from ON
rc.CONSTRAINT_CATALOG = rcu_from.CONSTRAINT_CATALOG AND
rc.CONSTRAINT_NAME = rcu_from.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE rcu_to ON
rc.UNIQUE_CONSTRAINT_CATALOG = rcu_to.CONSTRAINT_CATALOG AND
rc.UNIQUE_CONSTRAINT_NAME = rcu_to.CONSTRAINT_NAME
";
using (var r = c.ExecuteReader())
t.Load(r);
}
return t;
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="dbConnectString">链接字符串</param>
/// <param name="dbProviderNamespace">数据提供者的命名空间,例如:"System.Data.SqlClient"</param>
public DBHelper(string dbConnectString, string dbProviderNamespace)
{
dbProviderFactory = DbProviderFactories.GetFactory(dbProviderNamespace);
dbConnection = dbProviderFactory.CreateConnection();
dbConnection.ConnectionString = dbConnectString;
dbCommand = dbConnection.CreateCommand();
}
private DbCommand CreateCommand(DbConnection connection, string commandText, object[] parameters) {
DbCommand command = connection.CreateCommand();
command.CommandText = commandText;
if (parameters != null) {
for (int i = 0; i < parameters.Length; i++) {
object value = parameters[i];
if (value is string) {
DateTime dt;
if (DateTime.TryParse((string)value, CultureInfo.InvariantCulture, DateTimeStyles.RoundtripKind, out dt)) {
value = dt;
}
}
DbParameter parameter = _dbProvider.CreateParameter();
parameter.ParameterName = i.ToString(CultureInfo.InvariantCulture);
parameter.Value = parameters[i] ?? DBNull.Value;
command.Parameters.Add(parameter);
}
}
return command;
}
protected override void BatchInsertRows(System.Data.Common.DbConnection conn, IEnumerable <ReceiveLogEntity> rows)
{
DbCommand command = conn.CreateCommand();
command.CommandText = @"INSERT INTO ReceiveLog(ReceiveDate,ReceiveFile,MonitorIP,MonitorAlias,ReceiveState) VALUES(@ReceiveDate,@ReceiveFile,@MonitorIP,@MonitorAlias,@ReceiveState)";
DbParameter dateParam = new SQLiteParameter("@ReceiveDate", DbType.DateTime);
DbParameter fileParam = new SQLiteParameter("@ReceiveFile", DbType.String);
DbParameter ipParam = new SQLiteParameter("@MonitorIP", DbType.String, 30);
DbParameter dictParam = new SQLiteParameter("@MonitorAlias", DbType.String);
DbParameter stateParam = new SQLiteParameter("@ReceiveState", DbType.String, 10);
command.Parameters.Add(dateParam);
command.Parameters.Add(fileParam);
command.Parameters.Add(ipParam);
command.Parameters.Add(dictParam);
command.Parameters.Add(stateParam);
foreach (var r in rows)
{
dateParam.Value = r.ReceiveDate;
fileParam.Value = r.ReceiveFile;
ipParam.Value = r.MonitorIP;
dictParam.Value = r.MonitorAlias;
stateParam.Value = r.ReceiveState;
command.ExecuteNonQuery();
}
}
public static void Setup(DbConnection connection, string schemaName)
{
const string sql =
"IF (NOT EXISTS ( " +
"SELECT 1 " +
"FROM INFORMATION_SCHEMA.TABLES " +
"WHERE TABLE_SCHEMA = '{0}' " +
"AND TABLE_NAME = '__DeploymentHistory')) " +
"BEGIN " +
"CREATE TABLE [{0}].[__DeploymentHistory]( " +
"[DeploymentId][NVARCHAR](150) NOT NULL, " +
"[ContextKey] [NVARCHAR](300) NOT NULL, " +
"[AssemblyFileName] [NVARCHAR](255) NOT NULL, " +
"[Binaries] [VARBINARY](MAX) NOT NULL, " +
"[Hashes] [VARBINARY](MAX) NOT NULL, " +
"[DeployerVersion] [NVARCHAR](32) NOT NULL, " +
"CONSTRAINT[PK___DeploymentHistory] PRIMARY KEY CLUSTERED " +
"(" +
"[DeploymentId] ASC " +
")) " +
"END";
using (var command = connection.CreateCommand())
{
command.CommandText = string.Format(sql, schemaName);
command.ExecuteNonQuery();
}
}
public DbBase(ConnObject co)
{
this.connObject = co;
this.conn = co.Master.Conn;
this.providerName = co.Master.ProviderName;
dalType = co.Master.ConnDalType;
_fac = GetFactory(providerName);
_con = _fac.CreateConnection();
_con.ConnectionString = DalCreate.FormatConn(dalType, conn);
_com = _con.CreateCommand();
if (_com != null)//Txt| Xml 时返回Null
{
_com.Connection = _con;
_com.CommandTimeout = AppConfig.DB.CommandTimeout;
}
if (IsAllowRecordSql)//开启秒表计算
{
_watch = new Stopwatch();
}
//if (AppConfig.DB.LockOnDbExe && dalType == DalType.Access)
//{
// string dbName = DataBase;
// if (!_dbOperator.ContainsKey(dbName))
// {
// try
// {
// _dbOperator.Add(dbName, false);
// }
// catch
// {
// }
// }
//}
//_com.CommandTimeout = 1;
}
public void Insert(DbConnection con)
{
DbCommand cmd = con.CreateCommand();
cmd.CommandText = "INSERT INTO Document ( DocCod , DocPat , DocDes , DocLen ) VALUES( ? , ? , ? , ? )";
cmd.Connection = con;
Code = ++LastCode;
DbParameter parm = cmd.CreateParameter();
parm.DbType = System.Data.DbType.Int64;
parm.Value = Code;
cmd.Parameters.Add(parm);
parm = cmd.CreateParameter();
parm.DbType = System.Data.DbType.String;
parm.Value = Path;
cmd.Parameters.Add(parm);
parm = cmd.CreateParameter();
parm.DbType = System.Data.DbType.String;
parm.Value = Description;
cmd.Parameters.Add(parm);
parm = cmd.CreateParameter();
parm.DbType = System.Data.DbType.Int64;
parm.Value = NumberOfCharacters;
cmd.Parameters.Add(parm);
if (cmd.ExecuteNonQuery() != 1)
throw new Exception("Error inserting document");
}
public static DBViewDataTable ExecuteQuery(DbConnection dbConnection, string query)
{
var dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = query;
DbDataReader reader = null;
var dbViewDataTable = new DBViewDataTable();
try
{
reader = dbCommand.ExecuteReader();
dbViewDataTable.DataTable.Load(reader, LoadOption.OverwriteChanges);
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (reader != null)
{
reader.Close();
dbViewDataTable.RecordsAffected = reader.RecordsAffected;
}
}
return dbViewDataTable;
}
public bool CreateTable(string tablename, IEnumerable <string> columns)
{
if (!IsOpen())
{
return(false);
}
TableNames();
if ((_tablenames != null) && (_tablenames.Contains(tablename)))
{
return(false);
}
StringBuilder nt = new StringBuilder(1024);
nt.AppendFormat("CREATE TABLE {0} (", tablename);
bool firstThru = true;
foreach (string col in columns)
{
if (!firstThru)
{
nt.Append(", ");
}
//if (col.ToLower().EndsWith("id"))
//{
// nt.Append(col).Append(" INT");
//}
//else
//{
nt.Append(col).Append(" VARCHAR(250)");
//}
firstThru = false;
}
nt.Append("); ");
DbCommand cmd = _conn.CreateCommand();
cmd.CommandText = nt.ToString();
int numResults = cmd.ExecuteNonQuery();
_tablenames.Add(tablename);
return(true);
}
private static void ClearData(DbConnection conn)
{
DbTransaction tx = conn.BeginTransaction();
DbCommand clearCmd = conn.CreateCommand();
clearCmd.CommandText = "DELETE FROM data";
clearCmd.ExecuteNonQuery();
tx.Commit();
}
/// <summary>
/// DbCommand クラスを実装しているプロバイダーのクラスの新しいインスタンスを返します。
/// </summary>
/// <param name="connection">データベースへの接続。</param>
/// <param name="transaction">トランザクション。</param>
/// <returns></returns>
public DbCommand CreateCommand(DbConnection connection, DbTransaction transaction)
{
var command = connection.CreateCommand();
command.Transaction = transaction;
return command;
}
private static void PerformMigration(DbConnection connection, DatabaseType databaseType, int migrationVersion)
{
using (DbCommand cmd = connection.CreateCommand())
{
cmd.CommandText = GetMigration(databaseType, migrationVersion);
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Initializes a new instance of the <see cref="AppLock" /> class.
/// </summary>
/// <param name="db">The database.</param>
/// <param name="lockResourceName">The lock resource.</param>
/// <param name="disposeDbContext">if set to <c>true</c> dispose the database context when the AppLock is disposed.</param>
public AppLock(
DbContext db,
string lockResourceName,
bool disposeDbContext = true)
{
disposables = Disposable.Create(OnDispose);
this.db = db;
this.lockResourceName = lockResourceName;
this.disposeDbContext = disposeDbContext;
connection = (DbConnection) db.OpenConnection();
const string cmd = @"
DECLARE @result int;
EXEC @result = sp_getapplock @Resource = @lockResource,
@LockMode = 'Exclusive',
@LockOwner = 'Session',
@LockTimeout = 60000;
SELECT @result";
var result = -1000;
using (var getAppLock = connection.CreateCommand())
{
getAppLock.Parameters.Add(new SqlParameter("lockResource", lockResourceName));
getAppLock.CommandText = cmd;
try
{
Debug.WriteLineIf(WriteDebugOutput, $"Trying to acquire app lock '{lockResourceName}' (#{GetHashCode()})");
result = (int) getAppLock.ExecuteScalar();
}
catch (SqlException exception)
when (exception.Message.StartsWith("Timeout expired."))
{
Debug.WriteLineIf(WriteDebugOutput, $"Timeout expired waiting for sp_getapplock. (#{GetHashCode()})");
DebugWriteLocks();
return;
}
}
resultCode = result;
if (result >= 0)
{
Debug.WriteLineIf(WriteDebugOutput, $"Acquired app lock '{lockResourceName}' with result {result} (#{GetHashCode()})");
}
else
{
Debug.WriteLineIf(WriteDebugOutput, $"Failed to acquire app lock '{lockResourceName}' with code {result} (#{GetHashCode()})");
}
#if DEBUG
Active[this] = this;
#endif
}
internal static void CreateTable(DbConnection cnn)
{
using (DbCommand cmd = cnn.CreateCommand())
{
cmd.CommandText = "CREATE TABLE TestCase (ID integer primary key autoincrement, Field1 Integer, Field2 Float, Field3 VARCHAR(50), Field4 CHAR(10), Field5 DateTime, Field6 Image)";
//cmd.CommandText = "CREATE TABLE TestCase (ID bigint primary key identity, Field1 Integer, Field2 Float, Field3 VARCHAR(50), Field4 CHAR(10), Field5 DateTime, Field6 Image)";
cmd.ExecuteNonQuery();
}
}
protected override void BatchInsertRows(System.Data.Common.DbConnection conn, IEnumerable <MonitorLogEntity> rows)
{
DbCommand command = conn.CreateCommand();
command.CommandText = @"INSERT INTO MonitorLog(MonitorDate,ChangedFile) VALUES(@MonitorDate,@ChangedFile)";
DbParameter dateParam = new SQLiteParameter("@MonitorDate", DbType.DateTime);
DbParameter fileParam = new SQLiteParameter("@ChangedFile", DbType.String);
command.Parameters.Add(dateParam);
command.Parameters.Add(fileParam);
foreach (var r in rows)
{
dateParam.Value = r.MonitorDate;
fileParam.Value = r.ChangedFile;
command.ExecuteNonQuery();
}
}
}//Execute(string sql,System.Data.IDataParameter[] param)
/// <summary>
/// 执行一个事务
/// </summary>
/// <param name="sqls">Sql语句组</param>
/// <returns>成功时返回true</returns>
static public bool ExecuteTrans(string[] sqls, System.Data.Common.DbConnection Conn)
{
System.Data.IDbTransaction myTrans;
if (Conn == null)
{
DBClassHelper.ErrLog("DBClassHelper.ExecuteTrans(string[] sqls):连接对象为空!");
return(false);
}
if (Conn.State == System.Data.ConnectionState.Closed)
{
Conn.Open();
}
System.Data.IDbCommand cmd = Conn.CreateCommand();
cmd.CommandTimeout = 180;
myTrans = Conn.BeginTransaction();
cmd.Transaction = myTrans;
int i = 0;
var wrongsql = string.Empty;
try
{
foreach (string sql in sqls)
{
if (sql != null)
{
wrongsql = sql;
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
myTrans.Commit();
}
catch (Exception ex)
{
myTrans.Rollback();
DBClassHelper.ErrLog("错误位置：" + i + "。错误sql:" + wrongsql + "。DBClassHelper.ExecuteTrans(string[] sqls):" + ex.Message);
return(false);
}
finally
{
Conn.Close();
}
return(true);
}//Execute(string sql)
protected override void BatchInsertRows(System.Data.Common.DbConnection conn, IEnumerable<ErrorLogEntity> rows)
{
DbCommand command = conn.CreateCommand();
command.CommandText = @"INSERT INTO ErrorLog(LogDate,LogLevel,LogMessage) VALUES(@LogDate,@LogLevel,@LogMessage)";
DbParameter dateParam = new SQLiteParameter("@LogDate", DbType.DateTime);
DbParameter levelParam = new SQLiteParameter("@LogLevel", DbType.String, 10);
DbParameter messageParam = new SQLiteParameter("@LogMessage", DbType.String);
command.Parameters.Add(dateParam);
command.Parameters.Add(levelParam);
command.Parameters.Add(messageParam);
foreach (var r in rows)
{
dateParam.Value = r.LogDate;
levelParam.Value = r.LogLevel;
messageParam.Value = r.LogMessage;
command.ExecuteNonQuery();
}
}
/// <summary>
/// This function initiate DbCommand for current instance - we MUST run it first ...
/// </summary>
/// <param name="strError"></param>
/// <param name="connectionName"></param>
/// <returns></returns>
public DbCommand commandDB(out String strError, String connectionName = "")
{
if (connectionName == "") // Default Name
{
connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["connectionStr"].ConnectionString;
providerName = System.Configuration.ConfigurationManager.ConnectionStrings["connectionStr"].ProviderName;
}
else // New Name
{
connectionString = System.Configuration.ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
providerName = System.Configuration.ConfigurationManager.ConnectionStrings[connectionName].ProviderName;
}
conn = CreateDbConnection(out strError);
_commandDB = conn.CreateCommand();
_commandDB.CommandTimeout = gedSQLTimeOut;
return(_commandDB);
}
/// <summary>
/// Determines if a database is in read only mode.
/// </summary>
/// <param name="connection">The database connection</param>
/// <returns>True if readonly, otherwise false.</returns>
private static async Task <bool> IsDatabaseReadOnly(System.Data.Common.DbConnection connection)
{
bool isReadOnly = false;
DbDataReader reader = null;
var cmd = connection.CreateCommand();
cmd.CommandText = "SELECT is_read_only FROM sys.databases WHERE name = @databaseName";
cmd.CommandType = CommandType.Text;
var p = cmd.CreateParameter();
p.ParameterName = "@databaseName";
p.Value = connection.Database;
//p.Value = "FailoverTest";
cmd.Parameters.Add(p);
try
{
reader = await cmd.ExecuteReaderAsync();
if (!reader.IsClosed && reader.HasRows)
{
await reader.ReadAsync();
isReadOnly = reader.GetBoolean(0);
}
}
catch (Exception oops)
{
System.Diagnostics.Debug.WriteLine(oops.Message);
}
finally
{
if (null != reader)
{
reader.Close();
}
}
return(isReadOnly);
}
/// <summary>
/// 执行查询返回DataTable
/// </summary>
/// <param name="sql">Sql语句</param>
/// <returns>成功返回DataTable,失败则返回 null</returns>
static public System.Data.DataTable ExecuteQueryToDataTable(string sql, System.Data.Common.DbConnection Conn)
{
if (Conn is SQLiteConnection)
{
var cmd = Conn.CreateCommand() as SQLiteCommand;
cmd.CommandText = sql;
cmd.CommandTimeout = 180;
var da = new SQLiteDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return(dt);
}
else
{
System.Data.DataTable dt = new System.Data.DataTable();
System.Data.IDataReader reader = ExecuteQuery(sql, Conn);
dt.Load(reader);
return(dt);
}
}//ExecuteQueryToDataTable(string sql)
}//ExecuteQuery(string sql)
/// <summary>
/// 执行Sql语句
/// </summary>
/// <param name="sql">Sql语句</param>
/// <param name="Conn">数据库连接对象</param>
/// <returns>返回受影响行数</returns>
static public int Execute(string sql, System.Data.Common.DbConnection Conn)
{
if (Conn == null)
{
DBClassHelper.ErrLog("DBClassHelper.Execute(string sql, System.Data.Common.DbConnection Conn):连接对象为空!");
//return 0;
}
if (Conn.State == System.Data.ConnectionState.Closed)
{
Conn.Open();
}
System.Data.IDbCommand cmd = Conn.CreateCommand();
cmd.CommandTimeout = 180;
cmd.CommandText = sql;
try
{
var count = cmd.ExecuteNonQuery();
cmd.Dispose();
return(count);
}
catch (Exception ex)
{
cmd.Dispose();
if (ex.Message.Contains("死锁"))
{
WriteLog(ex.Message + " 再做列锁循环！Execute");
System.Threading.Thread.Sleep(200);
return(Execute(sql, Conn));
}
else
{
DBClassHelper.ErrLog("DBClassHelper.Execute(string sql, System.Data.Common.DbConnection Conn):" + ex.Message + "/nsql=" + sql);
return(0);
}
}
finally
{
Conn.Close();
}
}//Execute(string sql)
public DataSet GetDataSet(ref StringBuilder sbSql, string providerName, string connectionString, out string strError, out int intEffected)
{
intEffected = -1;
strError = "";
try
{
conn = CreateDbConnection(providerName, connectionString, out strError);
conn.Open();
}
catch (Exception ex)
{
strError = ex.Message;
return(null);
}
DataSet dataSet = new DataSet();
DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);
try
{ //System.Data.Common.DbCommand
DbDataAdapter dbAdapter = factory.CreateDataAdapter();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = sbSql.ToString();
cmd.CommandType = CommandType.Text; // If procedure you can use -> CALL procName (@param1,...,@paramN)
cmd.Connection = conn;
dbAdapter.SelectCommand = cmd;
dbAdapter.FillLoadOption = LoadOption.PreserveChanges;
intEffected = dbAdapter.Fill(dataSet);
}
catch (Exception ex)
{
strError = ex.Message;
}
return(dataSet);
}
protected override void BatchInsertRows(System.Data.Common.DbConnection conn, IEnumerable <SendLogEntity> rows)
{
DbCommand command = conn.CreateCommand();
command.CommandText = @"INSERT INTO SendLog(SendDate,SendFile,SubscribeIP,SendState) VALUES(@SendDate,@SendFile,@SubscribeIP,@SendState)";
DbParameter dateParam = new SQLiteParameter("@SendDate", DbType.DateTime);
DbParameter fileParam = new SQLiteParameter("@SendFile", DbType.String);
DbParameter ipParam = new SQLiteParameter("@SubscribeIP", DbType.String, 30);
DbParameter stateParam = new SQLiteParameter("@SendState", DbType.String, 10);
command.Parameters.Add(dateParam);
command.Parameters.Add(fileParam);
command.Parameters.Add(ipParam);
command.Parameters.Add(stateParam);
foreach (var r in rows)
{
dateParam.Value = r.SendDate;
fileParam.Value = r.SendFile;
ipParam.Value = r.SubscribeIP;
stateParam.Value = r.SendState;
command.ExecuteNonQuery();
}
}
private bool ExecuteTest()
{
this.errorMsgs = new System.Collections.Generic.List <string>();
System.Data.Common.DbTransaction dbTransaction = null;
System.Data.Common.DbConnection dbConnection = null;
string item;
try
{
if (this.ValidateConnectionStrings(out item))
{
System.Data.Common.DbConnection dbConnection2;
dbConnection = (dbConnection2 = new System.Data.SqlClient.SqlConnection(this.GetConnectionString()));
try
{
dbConnection.Open();
System.Data.Common.DbCommand dbCommand = dbConnection.CreateCommand();
dbTransaction = dbConnection.BeginTransaction();
dbCommand.Connection = dbConnection;
dbCommand.Transaction = dbTransaction;
dbCommand.CommandText = "CREATE TABLE installTest(Test bit NULL)";
dbCommand.ExecuteNonQuery();
dbCommand.CommandText = "DROP TABLE installTest";
dbCommand.ExecuteNonQuery();
dbTransaction.Commit();
dbConnection.Close();
goto IL_94;
}
finally
{
if (dbConnection2 != null)
{
((System.IDisposable)dbConnection2).Dispose();
}
}
}
this.errorMsgs.Add(item);
IL_94 :;
}
catch (System.Exception ex)
{
this.errorMsgs.Add(ex.Message);
if (dbTransaction != null)
{
try
{
dbTransaction.Rollback();
}
catch (System.Exception ex2)
{
this.errorMsgs.Add(ex2.Message);
}
}
if (dbConnection != null && dbConnection.State != System.Data.ConnectionState.Closed)
{
dbConnection.Close();
dbConnection.Dispose();
}
}
string folderPath = base.Request.MapPath(Globals.ApplicationPath + "/config/test.txt");
if (!Install.TestFolder(folderPath, out item))
{
this.errorMsgs.Add(item);
}
try
{
Configuration configuration = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration(base.Request.ApplicationPath);
if (configuration.ConnectionStrings.ConnectionStrings["HidistroSqlServer"].ConnectionString == "none")
{
configuration.ConnectionStrings.ConnectionStrings["HidistroSqlServer"].ConnectionString = "required";
}
else
{
configuration.ConnectionStrings.ConnectionStrings["HidistroSqlServer"].ConnectionString = "none";
}
configuration.Save();
}
catch (System.Exception ex3)
{
this.errorMsgs.Add(ex3.Message);
}
folderPath = base.Request.MapPath(Globals.ApplicationPath + "/storage/test.txt");
if (!Install.TestFolder(folderPath, out item))
{
this.errorMsgs.Add(item);
}
return(this.errorMsgs.Count == 0);
}
public void CallBackTBJ(decimal packageno)
{
using (Entities en = new Entities())
{
// OracleParameter[] para = new OracleParameter[3];
// OracleParameter inpara = new OracleParameter();
//inpara.Direction = ParameterDirection.Input;
//inpara.ParameterName = "p_packageNo";
//inpara.Value = packageno;
//OracleParameter outpara1 = new OracleParameter();
//outpara1.Direction = ParameterDirection.Input;
//outpara1.ParameterName = "p_ErrCode";
//OracleParameter outpara2 = new OracleParameter();
//outpara2.Direction = ParameterDirection.Input;
//outpara2.ParameterName = "p_ErrMsg";
//para[0] = inpara;
//para[1] = outpara1;
//para[2] = outpara2;
//en.ExecuteStoreCommand("P_PACKAGE_CALLBACK", para);
//获取当前包装机的数据
//en.Configuration.AutoDetectChangesEnabled = false;
//en.Configuration.ValidateOnSaveEnabled = false;
//获取当前包装机最大条烟流水号
var cALLBACKs = (from item in en.T_PACKAGE_CALLBACK where item.EXPORT == packageno select item).ToList();
decimal maxCigNum = 1;
decimal maxSortnum = 0;
if (cALLBACKs.Any())
{
maxSortnum = cALLBACKs.Max(a => a.SORTNUM);//获取最大的任务号
}
//System.Data.EntityClient.EntityConnection entityConnection = (System.Data.EntityClient.EntityConnection)en.Connection;
//entityConnection.Open();
//System.Data.Common.DbConnection storeConnection = entityConnection.StoreConnection;
//System.Data.Common.DbCommand cmd = storeConnection.CreateCommand();
//cmd.CommandType = System.Data.CommandType.StoredProcedure;
//cmd.CommandText =
/*String sql=@"select t.billcode,
* t.regioncode,
* PACKTASKNUM,
* normalqty,
* t.PACKAGESEQ,
* CIGARETTECODE,
* CIGARETTENAME,
* CIGTYPE,
* PACKAGEQTY,
* ORDERDATE,
* MIANBELT,
* ORDERQTY,
* ALLPACKAGESEQ,
* SORTNUM,
* synseq,var_orderPagNum,var_shaednum,var_ordercount,var_NormalTPagNum,
* var_UnnormalTPagNum,pCount,var_UnionTPagNum
* from t_package_task t left join
* ( select a.regioncode,a.PACKAGENO,a.billcode,var_orderPagNum,decode(var_shaednum,null,0,var_shaednum)var_shaednum,var_ordercount,
* decode(var_NormalTPagNum,null,0,var_NormalTPagNum)var_NormalTPagNum,
* decode(var_UnnormalTPagNum,null,0,var_UnnormalTPagNum)var_UnnormalTPagNum,pCount,decode(var_UnionTPagNum,null,0,var_UnionTPagNum)var_UnionTPagNum from
* (
* select regioncode,PACKAGENO,billcode, max(PACKAGESEQ)var_orderPagNum from t_package_task group by regioncode,PACKAGENO,billcode
* ) a left join (select regioncode,PACKAGENO,billcode, sum(normalqty)var_shaednum from t_package_task
*
* where CIGTYPE = '2' group by regioncode,PACKAGENO,billcode)b
*
* on a.regioncode=b.regioncode and a.PACKAGENO=b.PACKAGENO and a.billcode=b.billcode
* left join ( select regioncode,count(distinct billcode)var_ordercount from t_package_task
* group by regioncode)c
* on c.regioncode=a.regioncode left join (select regioncode,PACKAGENO,billcode, count(distinct PACKTASKNUM ) var_NormalTPagNum from t_package_task
*
* where CIGTYPE = '1' group by regioncode,PACKAGENO,billcode)e
* on a.regioncode=e.regioncode and a.PACKAGENO=e.PACKAGENO and a.billcode=e.billcode
* left join (select regioncode,PACKAGENO,billcode, count(distinct PACKTASKNUM ) var_UnnormalTPagNum from t_package_task
*
* where CIGTYPE = '2' group by regioncode,PACKAGENO,billcode)f
* on a.regioncode=f.regioncode and a.PACKAGENO=f.PACKAGENO and a.billcode=f.billcode
* left join (select regioncode,PACKAGENO,billcode, count(distinct PACKTASKNUM ) pCount from t_package_task
*
* group by regioncode,PACKAGENO,billcode)g
* on a.regioncode=g.regioncode and a.PACKAGENO=g.PACKAGENO and a.billcode=g.billcode
* left join (select regioncode,PACKAGENO,billcode,count(1) var_UnionTPagNum from(
* select * from
* (
* select distinct regioncode,PACKAGENO,billcode, PACKTASKNUM from t_package_task
*
* where CIGTYPE = '1' group by regioncode,PACKAGENO,billcode,PACKTASKNUM)
* intersect
* (
*
* select distinct regioncode,PACKAGENO,billcode, PACKTASKNUM from t_package_task
*
* where CIGTYPE = '2' group by regioncode,PACKAGENO,billcode,PACKTASKNUM
* )) group by regioncode,PACKAGENO,billcode) h
* on a.regioncode=h.regioncode and a.PACKAGENO=h.PACKAGENO and a.billcode=h.billcode
* ) d
* on t.regioncode=d.regioncode and t.packageno=d.PACKAGENO and t.billcode=d.billcode
* where t.packageNo = "+packageno+@"
* and sortnum > "+maxSortnum+@"
* order by sortnum, packtasknum, cigtype, cigseq, SYNSEQ";*/
String sql = @"select t.billcode,
t.regioncode,
PACKTASKNUM,
normalqty,
t.PACKAGESEQ,
CIGARETTECODE,
CIGARETTENAME,
CIGTYPE,
PACKAGEQTY,
ORDERDATE,
MIANBELT,
ORDERQTY,
ALLPACKAGESEQ,
SORTNUM,
synseq,var_orderPagNum,var_shaednum,var_ordercount,var_NormalTPagNum,
var_UnnormalTPagNum,pCount,var_UnionTPagNum
from t_package_task t
left join
(select a.regioncode,a.PACKAGENO,a.billcode,var_orderPagNum,decode(var_shaednum,null,0,var_shaednum)var_shaednum,var_ordercount,
decode(var_NormalTPagNum,null,0,var_NormalTPagNum)var_NormalTPagNum,
decode(var_UnnormalTPagNum,null,0,var_UnnormalTPagNum)var_UnnormalTPagNum,pCount,decode(var_UnionTPagNum,null,0,var_UnionTPagNum)var_UnionTPagNum
from (select regioncode,PACKAGENO,billcode, max(PACKAGESEQ)var_orderPagNum
from t_package_task group by regioncode,PACKAGENO,billcode) a
left join
(select regioncode,PACKAGENO,billcode, sum(normalqty)var_shaednum from t_package_task
where CIGTYPE = '2' group by regioncode,PACKAGENO,billcode)b
on a.regioncode=b.regioncode and a.PACKAGENO=b.PACKAGENO and a.billcode=b.billcode
left join
( select regioncode,count(distinct billcode)var_ordercount from t_package_task
group by regioncode)c
on c.regioncode=a.regioncode
left join (select regioncode,PACKAGENO,billcode, (select count(distinct packtasknum)
from t_package_task where regioncode=aa.regioncode
and packageno = " + packageno + @"
) pCount from t_package_task aa
group by regioncode,PACKAGENO,billcode)g
on a.regioncode=g.regioncode and a.PACKAGENO=g.PACKAGENO and a.billcode=g.billcode
left join
(select billcode,max(var_UnionTPagNum) var_UnionTPagNum,max(var_NORMALPACKAGENUM) var_NormalTPagNum
,max(var_UNNORMALPACKAGENUM) var_UnnormalTPagNum from (
(select billcode,count(*) as var_UnionTPagNum,0 as var_NORMALPACKAGENUM, 0 as var_UNNORMALPACKAGENUM from (
select packtasknum,billcode,count(cigtype) countype from (
select packtasknum,cigtype,billcode
from t_package_task group by packtasknum,billcode,cigtype
) group by packtasknum,billcode
) where countype = 2 group by billcode)
union
(select billcode, 0 as var_UnionTPagNum ,count(*) as var_NORMALPACKAGENUM,0 as var_UNNORMALPACKAGENUM from (
select packtasknum,billcode,cigtype
from t_package_task where cigtype = '1'
and packtasknum not in (select packtasknum
from t_package_task where cigtype = '2' group by packtasknum,cigtype)
group by packtasknum ,cigtype,billcode) group by billcode)
union
(select billcode,0 as var_UnionTPagNum,0 as var_NORMALPACKAGENUM,count(*) as var_UNNORMALPACKAGENUM from (
select packtasknum,billcode,cigtype
from t_package_task where cigtype = '2'
and packtasknum not in (select packtasknum
from t_package_task where cigtype = '1' group by packtasknum,cigtype)
group by packtasknum ,cigtype,billcode) group by billcode))
group by billcode) y
on a.billcode = y.billcode)d
on t.regioncode=d.regioncode and t.packageno=d.PACKAGENO and t.billcode=d.billcode
where t.packageNo = " + packageno + @"
and sortnum > " + maxSortnum + @"
order by sortnum, packtasknum, cigtype, cigseq, SYNSEQ";
List <TBJModel> list = en.ExecuteStoreQuery <TBJModel>(sql, null).ToList();
var needInfo = (from item in en.V_PRODUCE_PACKAGEINFO where item.EXPORT == packageno orderby item.TASKNUM select item).ToList();
int mCopunt = 0, cigseq = 0;
decimal temppackagenum = 0;
V_PRODUCE_PACKAGEINFO firstTask = null;
String tempbillcode = "";
// var sql_text = @" insert into T_PACKAGE_CALLBACK(BILLCODE, ROUTEPACKAGENUM, ORDERPACKAGENUM, PACKAGESEQ, CIGARETTEQTY, SHAPEDNUM, CIGARETTECODE,
// CIGARETTENAME, CIGARETTETYPE, ROUTECODE, PACKAGEQTY, ORDERDATE, LINECODE, ORDERCOUNT, ORDERSEQ, CIGSEQ, EXPORT, PACKAGENUM,
// ORDERQUANTITY, ADDRESS, CUSTOMERNAME, CUSTOMERNO, ORDERURL, ORDERAMOUNT, PAYFLAG, SEQ, NORMALPACKAGENUM, UNNORMALPACKAGENUM,
// UNIONTASKPACKAGENUM, SORTNUM, CIGNUM, SYNSEQ)";
StringBuilder sql_text = new StringBuilder(@"insert into T_PACKAGE_CALLBACK(BILLCODE, ROUTEPACKAGENUM, ORDERPACKAGENUM, PACKAGESEQ, CIGARETTEQTY, SHAPEDNUM, CIGARETTECODE,
CIGARETTENAME, CIGARETTETYPE, ROUTECODE, PACKAGEQTY, ORDERDATE, LINECODE, ORDERCOUNT, ORDERSEQ, CIGSEQ, EXPORT, PACKAGENUM,
ORDERQUANTITY, ADDRESS, CUSTOMERNAME, CUSTOMERNO, ORDERURL, ORDERAMOUNT, PAYFLAG, SEQ, NORMALPACKAGENUM, UNNORMALPACKAGENUM,
UNIONTASKPACKAGENUM, SORTNUM, CIGNUM, SYNSEQ)");
System.Data.EntityClient.EntityConnection entityConnection = null;
System.Data.Common.DbConnection storeConnection = null;
System.Data.Common.DbCommand cmd = null;
//DataTable dt = new DataTable();
//dt.Columns.Add("BILLCODE", typeof(string));
//dt.Columns.Add("ROUTEPACKAGENUM", typeof(int));
//dt.Columns.Add("ORDERPACKAGENUM",typeof(int));
//dt.Columns.Add("PACKAGESEQ", typeof(int));
//dt.Columns.Add("CIGARETTEQTY", typeof(int));
//dt.Columns.Add("SHAPEDNUM", typeof(int));
//dt.Columns.Add("CIGARETTECODE", typeof(string));
//dt.Columns.Add("CIGARETTENAME", typeof(string));
//dt.Columns.Add("CIGARETTETYPE", typeof(string));
//dt.Columns.Add("ROUTECODE", typeof(string));
//dt.Columns.Add("PACKAGEQTY", typeof(int));
//dt.Columns.Add("ORDERDATE",typeof(DateTime));
//dt.Columns.Add("LINECODE", typeof(string));
//dt.Columns.Add("ORDERCOUNT", typeof(int));
//dt.Columns.Add("ORDERSEQ", typeof(int));
//dt.Columns.Add("CIGSEQ", typeof(int));
//dt.Columns.Add("EXPORT", typeof(int));
//dt.Columns.Add("PACKAGENUM", typeof(int));
//dt.Columns.Add("ORDERQUANTITY", typeof(int));
//dt.Columns.Add("ADDRESS", typeof(string));
//dt.Columns.Add("CUSTOMERNAME", typeof(string));
//dt.Columns.Add("CUSTOMERNO",typeof(string));
//dt.Columns.Add("ORDERURL", typeof(string));
//dt.Columns.Add("ORDERAMOUNT",typeof(decimal));
//dt.Columns.Add("PAYFLAG", typeof(string));
//dt.Columns.Add("SEQ", typeof(int));
//dt.Columns.Add("NORMALPACKAGENUM", typeof(int));
//dt.Columns.Add("UNNORMALPACKAGENUM", typeof(int));
//dt.Columns.Add("UNIONTASKPACKAGENUM", typeof(int));
//dt.Columns.Add("SORTNUM", typeof(int));
//dt.Columns.Add("CIGNUM", typeof(int));
//dt.Columns.Add("SYNSEQ", typeof(int));
//OracleBulkCopy copy = new OracleBulkCopy("DATA SOURCE=czt-test;PASSWORD=hnzt123;PERSIST SECURITY INFO=True;USER ID=zoomtel", OracleBulkCopyOptions.Default);
//copy.BatchSize = 10000;
//copy.BulkCopyTimeout = 2000;
//copy.DestinationTableName = "t_package_callback";
TBJModel items = null;
for (var j = 0; j < list.Count; j++)
{
items = list[j];
if (tempbillcode == "")
{
tempbillcode = items.billcode;
}
else if (tempbillcode != items.billcode)
{
needInfo = needInfo.Where(x => x.BILLCODE != tempbillcode).ToList();
tempbillcode = items.billcode;
}
firstTask = needInfo.Where(a => a.BILLCODE == items.billcode).FirstOrDefault();
for (int i = 1; i <= items.normalqty; i++)//
{
if (temppackagenum == 0 || temppackagenum != items.PACKTASKNUM)
{
temppackagenum = items.PACKTASKNUM; cigseq = 0;
}
cigseq++;
mCopunt++;
//DataRow dr = dt.NewRow();
//dr[0] = items.billcode;
//dr[1] = items.pCount;
//dr[2] = items.var_orderPagNum;
//dr[3] = items.PACKAGESEQ;
//dr[4] = 1;
//dr[5] = items.var_shaednum;
//dr[6] = items.CIGARETTECODE;
//dr[7] = items.CIGARETTENAME;
//dr[8] = items.CIGTYPE;
//dr[9] = items.regioncode;
//dr[10] = items.PACKAGEQTY;
//dr[11] = items.ORDERDATE;
//dr[12] = items.MIANBELT.ToString();
//dr[13] = items.var_ordercount;
//dr[14] = firstTask.SORTSEQ;
//dr[15] = cigseq;
//dr[16] = packageno;
//dr[17] = packageno;
//dr[18] = items.ORDERQTY;
//dr[19] = firstTask.CONTACTADDRESS;
//dr[20] = firstTask.CUSTOMERNAME;
//dr[21] = firstTask.CUSTOMERCODE;
//dr[22] = firstTask.URL;
//dr[23] = firstTask.TOTALAMOUNT;
//dr[24] = firstTask.CUSTTYPE;
//dr[25] = items.ALLPACKAGESEQ;
//dr[26] = items.var_UnionTPagNum;
//dr[27] = items.var_NormalTPagNum;
//dr[28] = items.var_UnnormalTPagNum;
//dr[29] = items.SORTNUM;
//dr[30] = maxCigNum++;
//dr[31] = items.synseq;
//dt.Rows.Add(dr);
//if (dt.Rows.Count >= 10000)
//{
// // entityConnection = (System.Data.EntityClient.EntityConnection)en.Connection;
// //entityConnection.Open();
// copy.WriteToServer(dt);
// // entityConnection.Close();
// dt.Rows.Clear();
// GC.Collect();
//}
//T_PACKAGE_CALLBACK tb = new T_PACKAGE_CALLBACK();
//tb.BILLCODE = item.billcode;//订单
//tb.ROUTEPACKAGENUM = item.pCount;//车组总包数
//tb.ORDERPACKAGENUM = item.var_orderPagNum;//订单总包数
//tb.PACKAGESEQ = item.PACKAGESEQ;//订单内包序
//tb.CIGARETTEQTY = 1;//品牌条烟数
//tb.SHAPEDNUM = item.var_shaednum;//订单异型烟数量
//tb.CIGARETTECODE = item.CIGARETTECODE;//卷烟编码
//tb.CIGARETTENAME = item.CIGARETTENAME;//卷烟名称
//tb.CIGARETTETYPE = item.CIGTYPE;//卷烟类型
//tb.ROUTECODE = item.regioncode;//车组编号
//tb.PACKAGEQTY = item.PACKAGEQTY;//包内条烟数量
//tb.ORDERDATE = item.ORDERDATE;//订单日期
//tb.LINECODE = item.MIANBELT.ToString();//线路编号
//tb.ORDERCOUNT = item.var_ordercount; //车组内订单数
//tb.ORDERSEQ = firstTask.SORTSEQ;//订单户序 firstTask.SORTSEQ
////tb.CIGSEQ = cigseq++;//条烟顺序
//tb.EXPORT = packageno;//出口号（包装机号）
//tb.PACKAGENUM = packageno;// 包装机号
//tb.ORDERQUANTITY = item.ORDERQTY;//订单总数
//tb.ADDRESS = firstTask.CONTACTADDRESS;//订单地址
//tb.CUSTOMERNAME = firstTask.CUSTOMERNAME;//客户名称
//tb.CUSTOMERNO = firstTask.CUSTOMERCODE;//客户编码
//tb.ORDERURL = firstTask.URL;//客户URL
//tb.ORDERAMOUNT = firstTask.TOTALAMOUNT;//订单总金额；
//tb.PAYFLAG = firstTask.CUSTTYPE;//结算状态
//tb.SEQ = item.ALLPACKAGESEQ;//整齐包序
//tb.UNIONTASKPACKAGENUM = item.var_UnionTPagNum;//合包总包数
//tb.NORMALPACKAGENUM = item.var_NormalTPagNum;//常规烟总包数
//tb.UNNORMALPACKAGENUM = item.var_UnnormalTPagNum;//异型烟总包数
//tb.SORTNUM = item.SORTNUM;//流水号
//tb.CIGNUM = maxCigNum++;// 每台包装机从1 增长
//tb.SYNSEQ = item.synseq;//批次号
//en.T_PACKAGE_CALLBACK.AddObject(tb);
// sql_text=sql_text +" select '" + item.billcode +"',"+ item.pCount+","+ item.var_orderPagNum+"," +item.PACKAGESEQ+ ","+
// 1+"," + item.var_shaednum+",'" + item.CIGARETTECODE+"','"+ item.CIGARETTENAME+"','"+ item.CIGTYPE+"','"+ item.regioncode+"',"+
// item.PACKAGEQTY + ",to_date('" + item.ORDERDATE.ToString("yyyy-MM-dd") + "','yyyy-mm-dd'),'" + item.MIANBELT.ToString() + "'," + item.var_ordercount + "," + firstTask.SORTSEQ + "," + 1 + "," + packageno + ","
// + packageno+","+ item.ORDERQTY+",'"+
//firstTask.CONTACTADDRESS+"','" +firstTask.CUSTOMERNAME+"','"+ firstTask.CUSTOMERCODE+"','" +firstTask.URL+"',"+ firstTask.TOTALAMOUNT+",'"+ firstTask.CUSTTYPE+"'," +item.ALLPACKAGESEQ+"," +item.var_NormalTPagNum+","
//+item.var_UnnormalTPagNum+","+ item.var_UnionTPagNum+","+
// item.SORTNUM+","+ (maxCigNum++)+","+ item.synseq +" from dual union";
sql_text = sql_text.Append(" select '").Append(items.billcode).Append("',").Append(items.pCount)
.Append(",").Append(items.var_orderPagNum).Append(",").Append(items.PACKAGESEQ).Append(",")
.Append(1).Append(",").Append(items.var_shaednum).Append(",'").Append(items.CIGARETTECODE).Append("','")
.Append(items.CIGARETTENAME).Append("','").Append(items.CIGTYPE).Append("','").Append(items.regioncode)
.Append("',").Append(items.PACKAGEQTY).Append(",to_date('").Append(items.ORDERDATE.ToString("yyyy-MM-dd"))
.Append("','yyyy-mm-dd'),'").Append(items.MIANBELT.ToString()).Append("',").Append(items.var_ordercount)
.Append(",").Append(firstTask.SORTSEQ).Append(",")
.Append(cigseq)
.Append(",").Append(packageno).Append(",").Append(packageno).Append(",").Append(items.ORDERQTY).Append(",'")
.Append(firstTask.CONTACTADDRESS).Append("','").Append(firstTask.CUSTOMERNAME).Append("','")
.Append(firstTask.CUSTOMERCODE).Append("','").Append(firstTask.URL).Append("',").Append(firstTask.TOTALAMOUNT)
.Append(",'").Append(firstTask.CUSTTYPE).Append("',").Append(items.ALLPACKAGESEQ).Append(",")
.Append(items.var_NormalTPagNum).Append(",").Append(items.var_UnnormalTPagNum).Append(",")
.Append(items.var_UnionTPagNum).Append(",").Append(items.SORTNUM).Append(",").Append(maxCigNum++)
.Append(",").Append(items.synseq).Append(" from dual union");
if (mCopunt >= 200)
{
entityConnection = (System.Data.EntityClient.EntityConnection)en.Connection;
entityConnection.Open();
storeConnection = entityConnection.StoreConnection;
cmd = storeConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = sql_text.ToString().Substring(0, sql_text.Length - 5);
cmd.ExecuteNonQuery();
cmd.Dispose();
cmd = null;
entityConnection.Close();
entityConnection = null;
storeConnection = null;
sql_text.Clear();
GC.Collect();
mCopunt = 0;
sql_text.Append(@" insert into T_PACKAGE_CALLBACK(BILLCODE, ROUTEPACKAGENUM, ORDERPACKAGENUM, PACKAGESEQ, CIGARETTEQTY, SHAPEDNUM, CIGARETTECODE,
CIGARETTENAME, CIGARETTETYPE, ROUTECODE, PACKAGEQTY, ORDERDATE, LINECODE, ORDERCOUNT, ORDERSEQ, CIGSEQ, EXPORT, PACKAGENUM,
ORDERQUANTITY, ADDRESS, CUSTOMERNAME, CUSTOMERNO, ORDERURL, ORDERAMOUNT, PAYFLAG, SEQ, NORMALPACKAGENUM, UNNORMALPACKAGENUM,
UNIONTASKPACKAGENUM, SORTNUM, CIGNUM, SYNSEQ)");
}
}
firstTask = null;
}
if (sql_text.ToString().Contains("union"))
{
entityConnection = (System.Data.EntityClient.EntityConnection)en.Connection;
entityConnection.Open();
storeConnection = entityConnection.StoreConnection;
cmd = storeConnection.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = sql_text.ToString().Substring(0, sql_text.Length - 5);
cmd.ExecuteNonQuery();
cmd.Dispose();
cmd = null;
entityConnection.Close();
entityConnection = null;
storeConnection = null;
}
sql_text.Clear();
}
}
private bool AddBuiltInRoles(out string errorMsg)
{
System.Data.Common.DbConnection dbConnection = null;
System.Data.Common.DbTransaction dbTransaction = null;
bool result;
try
{
System.Data.Common.DbConnection dbConnection2;
dbConnection = (dbConnection2 = new System.Data.SqlClient.SqlConnection(this.GetConnectionString()));
try
{
dbConnection.Open();
System.Data.Common.DbCommand dbCommand = dbConnection.CreateCommand();
dbTransaction = dbConnection.BeginTransaction();
dbCommand.Connection = dbConnection;
dbCommand.Transaction = dbTransaction;
dbCommand.CommandType = System.Data.CommandType.Text;
dbCommand.CommandText = "INSERT INTO aspnet_Roles(RoleName, LoweredRoleName) VALUES(@RoleName, LOWER(@RoleName))";
System.Data.Common.DbParameter value = new System.Data.SqlClient.SqlParameter("@RoleName", System.Data.SqlDbType.NVarChar, 256);
dbCommand.Parameters.Add(value);
RolesConfiguration rolesConfiguration = HiConfiguration.GetConfig().RolesConfiguration;
dbCommand.Parameters["@RoleName"].Value = rolesConfiguration.Manager;
dbCommand.ExecuteNonQuery();
dbCommand.Parameters["@RoleName"].Value = rolesConfiguration.Member;
dbCommand.ExecuteNonQuery();
dbCommand.Parameters["@RoleName"].Value = rolesConfiguration.SystemAdministrator;
dbCommand.ExecuteNonQuery();
dbTransaction.Commit();
dbConnection.Close();
}
finally
{
if (dbConnection2 != null)
{
((System.IDisposable)dbConnection2).Dispose();
}
}
errorMsg = null;
result = true;
}
catch (System.Data.SqlClient.SqlException ex)
{
errorMsg = ex.Message;
if (dbTransaction != null)
{
try
{
dbTransaction.Rollback();
}
catch (System.Exception ex2)
{
errorMsg = ex2.Message;
}
}
if (dbConnection != null && dbConnection.State != System.Data.ConnectionState.Closed)
{
dbConnection.Close();
dbConnection.Dispose();
}
result = false;
}
return(result);
}
private bool CreateAdministrator(out int newUserId, out string errorMsg)
{
System.Data.Common.DbConnection dbConnection = null;
System.Data.Common.DbTransaction dbTransaction = null;
bool result;
try
{
System.Data.Common.DbConnection dbConnection2;
dbConnection = (dbConnection2 = new System.Data.SqlClient.SqlConnection(this.GetConnectionString()));
try
{
dbConnection.Open();
RolesConfiguration rolesConfiguration = HiConfiguration.GetConfig().RolesConfiguration;
System.Data.Common.DbCommand dbCommand = dbConnection.CreateCommand();
dbTransaction = dbConnection.BeginTransaction();
dbCommand.Connection = dbConnection;
dbCommand.Transaction = dbTransaction;
dbCommand.CommandType = System.Data.CommandType.Text;
dbCommand.CommandText = "SELECT RoleId FROM aspnet_Roles WHERE [LoweredRoleName] = LOWER(@RoleName)";
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RoleName", rolesConfiguration.SystemAdministrator));
System.Guid guid = (System.Guid)dbCommand.ExecuteScalar();
dbCommand.Parameters["@RoleName"].Value = rolesConfiguration.Manager;
System.Guid guid2 = (System.Guid)dbCommand.ExecuteScalar();
dbCommand.Parameters.Clear();
dbCommand.CommandText = "INSERT INTO aspnet_Users (UserName, LoweredUserName, IsAnonymous, UserRole, LastActivityDate, Password, PasswordFormat, PasswordSalt, IsApproved, IsLockedOut, CreateDate, LastLoginDate, LastPasswordChangedDate, LastLockoutDate, FailedPasswordAttemptCount, FailedPasswordAttemptWindowStart, FailedPasswordAnswerAttemptCount, FailedPasswordAnswerAttemptWindowStart, Email, LoweredEmail) VALUES (@Username, LOWER(@Username), 0, @UserRole, @CreateDate, @Password, @PasswordFormat, @PasswordSalt, 1, 0, @CreateDate, @CreateDate, @CreateDate, CONVERT( datetime, '17540101', 112 ), 0, CONVERT( datetime, '17540101', 112 ), 0, CONVERT( datetime, '17540101', 112 ), @Email, LOWER(@Email));SELECT @@IDENTITY";
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Username", this.username));
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@UserRole", UserRole.SiteManager));
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CreateDate", System.DateTime.Now));
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Password", this.password));
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PasswordFormat", System.Web.Security.MembershipPasswordFormat.Clear));
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PasswordSalt", ""));
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Email", this.email));
newUserId = System.Convert.ToInt32(dbCommand.ExecuteScalar());
dbCommand.Parameters.Clear();
dbCommand.CommandText = "INSERT INTO aspnet_Managers(UserId) VALUES(@UserId)";
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@UserId", newUserId));
dbCommand.ExecuteNonQuery();
dbCommand.CommandText = "INSERT INTO aspnet_UsersInRoles(UserId, RoleId) VALUES(@UserId, @RoleId)";
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RoleId", guid2));
dbCommand.ExecuteNonQuery();
dbCommand.Parameters["@RoleId"].Value = guid;
dbCommand.ExecuteNonQuery();
dbTransaction.Commit();
dbConnection.Close();
}
finally
{
if (dbConnection2 != null)
{
((System.IDisposable)dbConnection2).Dispose();
}
}
errorMsg = null;
result = true;
}
catch (System.Data.SqlClient.SqlException ex)
{
errorMsg = ex.Message;
newUserId = 0;
if (dbTransaction != null)
{
try
{
dbTransaction.Rollback();
}
catch (System.Exception ex2)
{
errorMsg = ex2.Message;
}
}
if (dbConnection != null && dbConnection.State != System.Data.ConnectionState.Closed)
{
dbConnection.Close();
dbConnection.Dispose();
}
result = false;
}
return(result);
}
