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