/// <summary>
/// 上传文件
/// </summary>
public void FileUp(string FullName, string DBFullName, byte[] bFile)
{
string FileName = System.IO.Path.GetFileName(FullName);
string CFolder = System.IO.Path.GetDirectoryName(FullName);
string DBFolder = System.IO.Path.GetDirectoryName(DBFullName);
// 1.上传到数据库
Apq.Data.Common.DbConnectionHelper.Open(Connection);
System.Data.Common.DbCommand sqlCmd = Connection.CreateCommand();
sqlCmd.CommandText = "Apq_FileTrans_Insert";
sqlCmd.CommandType = CommandType.StoredProcedure;
Apq.Data.Common.DbCommandHelper cmdHelper = new Apq.Data.Common.DbCommandHelper(sqlCmd);
cmdHelper.AddParameter("@FileName", FileName);
cmdHelper.AddParameter("@DBFolder", DBFolder);
cmdHelper.AddParameter("@CFolder", CFolder);
cmdHelper.AddParameter("@FileStream", bFile);
cmdHelper.AddParameter("@ID", 0);
sqlCmd.Parameters["@ID"].Direction = ParameterDirection.InputOutput;
sqlCmd.ExecuteNonQuery();
int ID = Apq.Convert.ChangeType <int>(sqlCmd.Parameters["@ID"].Value);
// 2.转到磁盘
sqlCmd.Parameters.Clear();
sqlCmd.CommandText = "Apq_FileTrans_WriteToHD_ADO";
sqlCmd.CommandType = CommandType.StoredProcedure;
cmdHelper.AddParameter("@ID", ID);
cmdHelper.AddParameter("@KeepInDB", DBNull.Value);
sqlCmd.ExecuteNonQuery();
}
public datacontrol(String path)
{
bool flag;
if(File. Exists(path)){
flag=false;
}else{
flag=true;
}
SQLiteConnection con = new SQLiteConnection ("Data Source="+path);
str = path;
con.Open();
connection=con;
cmd=new SQLiteCommand(con);
if(flag){
cmd.CommandText="create table driver(driverid varchar PRIMARY KEY,personid varchar not null,sex bool not null,name varchar not null,birthday char(8) not null,password varchar not null)";
cmd.ExecuteNonQuery();
cmd.CommandText="create table adminstrator (id varchar PRIMARY KEY,password varchar not null)";
cmd.ExecuteNonQuery();
cmd.CommandText="insert into adminstrator values(\'adminstrator\',\'1234\')";
cmd.ExecuteNonQuery();
cmd.CommandText="create table car(carid varchar primary key,caryear int,avaliable bool,insurancen bool,yearcheck bool)";
cmd.ExecuteNonQuery();
cmd.CommandText="create table contract(carid varchar,driverid varchar,startdate int,enddate int,isagree bool,isvalid bool,PRIMARY KEY(carid,driverid,startdate))";
cmd.ExecuteNonQuery();
//TODO create the table
}
}
/// <summary>
/// Drops the DemoTable.
/// </summary>
/// <param name="command">A command attached to a connection</param>
private void ExecuteDropDemoTable(DbCommand command)
{
Console.WriteLine("Removing DemoTable..");
command.CommandText = "DROP TABLE DemoTable";
command.ExecuteNonQuery();
}
// execute an update, delete, or insert command
// and return the number of affected rows
public static int ExecuteNonQuery(DbCommand command)
{
// The number of affected rows
int affectedRows = -1;
// Execute the command making sure the connection gets closed in the end
try
{
// Open the connection of the command
command.Connection.Open();
// Execute the command and get the number of affected rows
affectedRows = command.ExecuteNonQuery();
}
catch (Exception ex)
{
// Log eventual errors and rethrow them
//Utilities.LogError(ex);
throw ex;
}
finally
{
// Close the connection
command.Connection.Close();
}
// return the number of affected rows
return affectedRows;
}
/// <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)
{
}
}
/// <summary>
/// Executes a command
/// </summary>
public void ExecuteNonQuery()
{
using (db.DbCommand cmd = ToDbCommand())
{
cmd.ExecuteNonQuery();
}
}
public int Update(Entities.OAMQMessage message)
{
//string sql = @"update OAMQMessages set Status = :Status, ErrorCount = :ErrorCount WHERE MessageId = :MessageId ";
//System.Data.Common.DbCommand cmd = this.DataSource.GetSqlStringCommand(sql);
//string prefix = ":";
//this.AddParameter(this.DataSource, cmd, System.Data.DbType.Int32, prefix + "Status", message.Status);
//this.AddParameter(this.DataSource, cmd, System.Data.DbType.Int32, prefix + "ErrorCount", message.ErrorCount);
//this.AddParameter(this.DataSource, cmd, System.Data.DbType.String, prefix + "MessageId", message.MessageId.ToString());
string sql = @"update OAMQMessages set
Sendertime = :Sendertime
,Status = :Status
,ErrorCount = :ErrorCount
WHERE MessageId = :MessageId ;";
System.Data.Common.DbCommand cmd = this.DbConnection.CreateCommand();
cmd.CommandText = sql;
string prefix = string.Empty;
this.AddParameter(cmd, System.Data.DbType.String, prefix + "Sendertime", message.Sendertime.ToString("yyyy-MM-dd HH:mm:ss"));
this.AddParameter(cmd, System.Data.DbType.Int32, prefix + "Status", message.Status);
this.AddParameter(cmd, System.Data.DbType.Int32, prefix + "ErrorCount", message.ErrorCount);
this.AddParameter(cmd, System.Data.DbType.String, prefix + "MessageId", message.MessageId.ToString());
return(cmd.ExecuteNonQuery());
}
public static int ExecuteNonQuery(DbCommand cmd)
{
using (DbConnection connection = GetConnection())
{
cmd.Connection = connection;
return cmd.ExecuteNonQuery();
}
}
public int ExecNonQuery(DbCommand cm)
{
//OnExecuting(ExecTypes.NonQuery, cm);
int r = cm.ExecuteNonQuery();
//OnExecuted(ExecTypes.NonQuery, cm, r);
return r;
}
public static int ExecuteNonQuery(DbCommand command)
{
int result = -1;
command.Connection.Open();
result = command.ExecuteNonQuery();
return result;
}
/// <summary>
/// Will execute the given command on the database
/// </summary>
/// <param name="command">The command to execute</param>
protected void ExecuteCommand(DbCommand command)
{
bool doClose = InitializeConnection(ref command);
command.ExecuteNonQuery();
if (doClose)
Connection.Close();
}
// insert update delete command
// return no of affected rows
public static int ExecuteNonQuery(DbCommand command)
{
int affectedRows = -1;
command.Connection.Open();
affectedRows = command.ExecuteNonQuery();
return affectedRows;
}
public bool Fun_ExecSQL(string sSQL, bool bDBClose = false)
{
try
{
sSQL = sSQL.Replace("''", "' '");
if (this.DBConn == null)
{
if (this.Fun_OpenDataBase() == false)
{
return(false);
}
}
else
{
if (this.DBConn.State != System.Data.ConnectionState.Open)
{
if (this.Fun_OpenDataBase() == false)
{
return(false);
}
}
}
if (DBConn.State != System.Data.ConnectionState.Open)
{
return(false);
}
this.DBCommand.Connection = DBConn;
this.DBCommand.CommandType = System.Data.CommandType.Text;
this.DBCommand.CommandText = sSQL;
this.DBCommand.Transaction = DBTran;
if (DBCommand.ExecuteNonQuery() <= 0)
{
if (bDBClose == true)
{
Fun_CloseDB();
}
return(false);
}
else
{
if (bDBClose == true)
{
Fun_CloseDB();
}
return(true);
}
}
catch (Exception)
{
return(false);
}
}
public void ExecuteNonQuery(DbCommand command)
{
using (var connection = new SQLiteConnection(_connectionString))
{
command.Connection = connection;
connection.Open();
command.ExecuteNonQuery();
}
}
public static int ExecuteNonQuery(DbCommand cmd)
{
using(SqlCeConnection conn = new SqlCeConnection(DBHelper.GetConnectionString()))
{
PrepareCommand(cmd, conn, null);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
public static bool ExecuteNonQuery(DbCommand comm)
{
int i = -1;
comm.Connection.Open();
i = comm.ExecuteNonQuery();
comm.Connection.Close();
return i > 0;
}
public virtual int ExecuteNonQuery(System.Data.Common.DbCommand command, System.Data.Common.DbTransaction dbTransaction)
{
if (command == null)
{
return(0);
}
PrepareCommand(command, dbTransaction);
return(command.ExecuteNonQuery());
}
protected override object ExecuteResultInternal(IDbExecuteContext ctx, DbCommand command, Type resultType, object resultInstance)
{
var affectedRows = command.ExecuteNonQuery();
//判断是否是存储过程，是则需要处理输出参数和返回参数
if(command.CommandType == CommandType.StoredProcedure) {
var result = this.HandleOutputOrReturnValue(ctx, resultType, resultInstance, command, affectedRows);
return result;
}
return affectedRows;
}
protected int ExecuteNonQuery(DbCommand command)
{
this.AbrirConexao();
command.Connection = this.Conexao;
var resultado = command.ExecuteNonQuery();
this.FecharConexao();
return resultado;
}
public virtual int ExecuteNonQuery(System.Data.Common.DbCommand command)
{
if (command == null)
{
return(0);
}
using (var conn = GetWrapperedConnection())
{
PrepareCommand(command, conn);
return(command.ExecuteNonQuery());
}
}
/// <summary>
/// 下载文件
/// </summary>
public void FileDow(string DBFullName, string FullName)
{
string FileName = System.IO.Path.GetFileName(FullName);
string CFolder = System.IO.Path.GetDirectoryName(FullName);
string DBFolder = System.IO.Path.GetDirectoryName(DBFullName);
// 准备本地目录
if (!System.IO.Directory.Exists(CFolder))
{
System.IO.Directory.CreateDirectory(CFolder);
}
// 1.写入数据库
Apq.Data.Common.DbConnectionHelper.Open(Connection);
System.Data.Common.DbCommand sqlCmd = Connection.CreateCommand();
sqlCmd.CommandText = "Apq_FileTrans_Insert_ADO";
sqlCmd.CommandType = CommandType.StoredProcedure;
Apq.Data.Common.DbCommandHelper cmdHelper = new Apq.Data.Common.DbCommandHelper(sqlCmd);
cmdHelper.AddParameter("@FullName", DBFullName);
cmdHelper.AddParameter("@CFolder", CFolder);
cmdHelper.AddParameter("@ID", 0);
sqlCmd.Parameters["@ID"].Direction = ParameterDirection.InputOutput;
sqlCmd.ExecuteNonQuery();
int ID = Apq.Convert.ChangeType <int>(sqlCmd.Parameters["@ID"].Value);
// 2.读取到本地
DataSet ds = new DataSet();
Apq.Data.Common.DbConnectionHelper connHelper = new Apq.Data.Common.DbConnectionHelper(Connection);
DbDataAdapter da = connHelper.CreateAdapter();
da.SelectCommand.CommandText = "Apq_FileTrans_List";
da.SelectCommand.CommandType = CommandType.StoredProcedure;
Apq.Data.Common.DbCommandHelper daHelper = new Apq.Data.Common.DbCommandHelper(da.SelectCommand);
daHelper.AddParameter("@ID", ID);
da.Fill(ds);
// 3.保存文件
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
byte[] bFile = Apq.Convert.ChangeType <byte[]>(ds.Tables[0].Rows[0]["FileStream"]);
System.IO.FileStream fs = new System.IO.FileStream(FullName, System.IO.FileMode.Create);
fs.Write(bFile, 0, bFile.Length);
fs.Flush();
fs.Close();
}
// 4.删除数据库行
da.SelectCommand.CommandText = "Apq_FileTrans_Delete";
da.SelectCommand.ExecuteNonQuery();
}
public int ExecuteNonQuery(DbCommand command)
{
DbConnection connection = null;
try
{
connection = GetOpenConnection();
command.Connection = connection;
return command.ExecuteNonQuery(); // return rows affected
}
finally
{
ReleaseConnection(connection);
}
}
public int ExecuteNonQuery(DbCommand command)
{
DbConnection connection = null;
try
{
connection = CreateOpenConnection();
command.Connection = connection;
return command.ExecuteNonQuery();
}
finally // is executed in case of exceptions as well
{
ReleaseConnection(connection);
}
}
public static int ExecuteNonQuery(DbCommand cmd)
{
int affectedRows = -1;
try
{
cmd.Connection.Open();
affectedRows = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Utilities.LogError(ex);
throw;
}
finally
{ cmd.Connection.Close(); }
return affectedRows;
}
public int ExecuteNonQueryCommand(DbCommand command)
{
int res = 0;
try
{
OpenConnection();
res = command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
CloseConnection();
}
return res;
}
/// <summary>
/// Inserts 5 new rows into your demo table
/// </summary>
/// <param name="command">A command attached to a connection</param>
private void ExecuteInsertTestDataStatement(DbCommand command)
{
Console.WriteLine("Adding some test data..");
for (int data = 0; data < 5; data++)
{
string commandText = string.Format("INSERT INTO DemoTable (DemoId, DemoName) values ({0}, 'Demo {0}')", data);
Console.WriteLine(commandText);
command.CommandText = commandText;
command.ExecuteNonQuery();
}
Console.WriteLine("Done..");
Console.WriteLine("Press any key to read back your data from the cloud..");
Console.ReadKey();
}
public static int ExecuteNonQuery(DbCommand comm)
{
int retrievedRows = -1;
try
{
comm.Connection.Open();
retrievedRows = comm.ExecuteNonQuery();
}
catch (Exception ex)
{
throw;
}
finally
{
comm.Connection.Close();
}
return retrievedRows;
}
public int ExecuteInsertAndReturnId(DbCommand command)
{
DbConnection connection = null;
try
{
connection = CreateOpenConnection();
command.Connection = connection;
command.ExecuteNonQuery();
command.Parameters.Clear();
command.CommandText = "SELECT @@IDENTITY";
return Convert.ToInt32(command.ExecuteScalar());
//return
}
finally // is executed in case of exceptions as well
{
ReleaseConnection(connection);
}
}
private string CreateAdministrator(string connectionstr)
{
System.Data.Common.DbConnection dbConnection = new System.Data.SqlClient.SqlConnection(connectionstr);
dbConnection.Open();
System.Data.Common.DbCommand dbCommand = dbConnection.CreateCommand();
dbCommand.Connection = dbConnection;
dbCommand.CommandType = System.Data.CommandType.Text;
dbCommand.CommandText = "INSERT INTO aspnet_Roles(RoleName,IsDefault) VALUES('超级管理员',1); SELECT @@IDENTITY";
int num = System.Convert.ToInt32(dbCommand.ExecuteScalar());
dbCommand.CommandText = "INSERT INTO aspnet_Managers(RoleId, UserName, Password, Email, CreateDate) VALUES (@RoleId, @UserName, @Password, @Email, getdate())";
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RoleId", num));
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Username", "admin"));
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Password", HiCryptographer.Md5Encrypt("123456")));
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Email", "*****@*****.**"));
dbCommand.ExecuteNonQuery();
dbConnection.Close();
return("");
}
// execute an update, delete, or insert command and return the number of affected rows
public static int ExecuteNonQuery(DbCommand command)
{
int affectedRows = 0;
try
{
command.Connection.Open();
affectedRows = command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
command.Connection.Close();
}
return affectedRows;
}
public int Update(Entities.OAMQMessage message)
{
string sql = @"update OAMQMessages set
Sendertime = @Sendertime
,Status = @Status
,ErrorCount = @ErrorCount
,MessageRemark=@MessageRemark
WHERE MessageId = @MessageId ;";
System.Data.Common.DbCommand cmd = this.DbConnection.CreateCommand();
cmd.CommandText = sql;
string prefix = string.Empty;
this.AddParameter(cmd, System.Data.DbType.DateTime, prefix + "Sendertime", message.Sendertime);
this.AddParameter(cmd, System.Data.DbType.Int32, prefix + "Status", message.Status);
this.AddParameter(cmd, System.Data.DbType.Int32, prefix + "ErrorCount", message.ErrorCount);
this.AddParameter(cmd, System.Data.DbType.String, prefix + "MessageId", message.MessageId);
this.AddParameter(cmd, System.Data.DbType.String, prefix + "MessageRemark", message.MessageRemark);
return(cmd.ExecuteNonQuery());
}
public static int ExecuteNonQuery(DbCommand command)
{
int findRows = -1;
try
{
command.Connection.Open();
findRows = command.ExecuteNonQuery();
}
catch
{
throw;
}
finally
{
command.Connection.Close();
}
return findRows;
}
private bool CreateAdministrator(out string errorMsg)
{
System.Data.Common.DbConnection dbConnection = new System.Data.SqlClient.SqlConnection(this.GetConnectionString());
dbConnection.Open();
System.Data.Common.DbCommand dbCommand = dbConnection.CreateCommand();
dbCommand.Connection = dbConnection;
dbCommand.CommandType = System.Data.CommandType.Text;
dbCommand.CommandText = "INSERT INTO aspnet_Roles(RoleName,IsDefault) VALUES('超级管理员',1); SELECT @@IDENTITY";
int num = System.Convert.ToInt32(dbCommand.ExecuteScalar());
dbCommand.CommandText = "INSERT INTO aspnet_Managers(RoleId, UserName, Password, Email, CreateDate) VALUES (@RoleId, @UserName, @Password, @Email, getdate())";
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RoleId", num));
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Username", this.username));
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Password", HiCryptographer.Md5Encrypt(this.password)));
dbCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Email", this.email));
dbCommand.ExecuteNonQuery();
dbConnection.Close();
errorMsg = null;
return(true);
}
/// <summary>
/// 执行update insert del操作
/// </summary>
/// <param name="command"></param>
/// <returns>返回影响行数</returns>
public static int ExecuteNonQuery(DbCommand command)
{
int affectRows = -1;
try
{
if (command.Connection.State != ConnectionState.Open)
{
command.Connection.Open();
}
affectRows = command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
command.Connection.Close();
}
return affectRows;
}
internal static void OpenConnection(DbConnection connection, GeneralDialect dialect)
{
connection.Open();
if (!string.IsNullOrEmpty(dialect.OnConnectCommand))
{
try
{
using (System.Data.Common.DbCommand command = connection.CreateCommand())
{
command.CommandText = dialect.OnConnectCommand;
command.CommandTimeout = Helper.DefaultTimeout;
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw new Exception("Unable to execute on connect command.", ex);
}
}
}
/// <summary>
/// Executes a SQL statement against a <see cref="DataContext"/> connection.
/// </summary>
/// <param name="context">The <see cref="DataContext"/> to execute the batch select against.</param>
/// <param name="command">The DbCommand to execute.</param>
/// <returns>The number of rows affected.</returns>
/// <remarks>The DbCommand is not disposed by this call, the caller must dispose the DbCommand.</remarks>
public static int ExecuteCommand(this DataContext context, DbCommand command)
{
if (context == null)
throw new ArgumentNullException("context");
if (command == null)
throw new ArgumentNullException("command");
LogCommand(context, command);
command.Connection = context.Connection;
if (command.Connection == null)
throw new InvalidOperationException("The DataContext must contain a valid SqlConnection.");
if (context.Transaction != null)
command.Transaction = context.Transaction;
if (command.Connection.State == ConnectionState.Closed)
command.Connection.Open();
return command.ExecuteNonQuery();
}
// Execute an update, delete, or insert command
// and return the number of affect rows
public static int ExecuteNonQuery(DbCommand command)
{
// the number of affected rows
int affectedRows = -1;
// Execute the command making sure the connection gets closed in the end
try {
command.Connection.Open();
// Execute the command and get the number of affected rows
affectedRows = command.ExecuteNonQuery();
} catch (Exception ex) {
// Log eventual errors and rethrow them
ErrorLogger error = new ErrorLogger();
error.ErrorLog(HttpContext.Current.Server.MapPath("Logs/ExecuteNonQueryErrorLog"), ex.Message);
throw;
} finally {
// Close the connection
command.Connection.Close();
}
return affectedRows;
}
public static void init(string path)
{
bool flag = true;
if (File. Exists(path))
{
flag = false;
}
SQLiteConnection con = new SQLiteConnection ("Data Source="+path);
connection=con;
con.Open();
cmd=new SQLiteCommand(con);
if (flag)
{
cmd.CommandText ="create table person (id char(10),name char(10),password char(20),type int);"+
"create table course (id char(10),name char(20),t_id char(10));" +
"create table OnCourse (s_id char(10),c_id char(10),int grade);" +
"create table Acourse (name char(20),t_id char(10))"+
"insert into person values('test','test','test',0);";
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Executes a query with no return value.
/// </summary>
/// <param name="command">The raw command object.</param>
public void ExecuteNonQuery(System.Data.Common.DbCommand command)
{
command.Connection = Conn;
command.CommandTimeout = 0;
try
{
if (ConnectionState.Closed == Conn.State)
{
Conn.Open();
}
command.ExecuteNonQuery();
}
finally
{
if (ConnectionState.Open == Conn.State)
{
Conn.Close();
}
}
}
/// <summary>
/// Exclui um objeto Modelo na Base de Dados
/// </summary>
private void Delete(Dictionary <string, object> DadosOriginal, GerenciadorDB mngBD, ref string sDetalhesLog)
{
sDetalhesLog += string.Format("{0} apagado(a). Detalhes:\r\n", NomeEntidade);
using (System.Data.Common.DbCommand cmd = mngBD.getCommand())
{
string sSQL = string.Format("DELETE FROM {0}.{1} WHERE ", GerenciadorDB.NameSpaceDB, NomeTabela);
// inclui todos os parametros no where para verificar concorrencia
foreach (KeyValuePair <string, object> itemOriginal in DadosOriginal)
{
// Verifica se valor é NULL
string VerifNull = itemOriginal.Value == null || (itemOriginal.Value is string && string.IsNullOrEmpty((string)itemOriginal.Value)) ||
(itemOriginal.Value is bool && (bool)itemOriginal.Value == false) || (itemOriginal.Value is int && (int)itemOriginal.Value == 0) ? "1" : "0";
// SQL do parâmetro
sSQL += "((1=" + VerifNull + " AND " + itemOriginal.Key + " is NULL) OR (" + itemOriginal.Key + "=?" + (VerifNull == "1" && itemOriginal.Value is string? " OR " + itemOriginal.Key + "=\'\'" : "") + ")) AND ";
// Adiciona parâmetro
cmd.Parameters.Add(Parametro(itemOriginal, mngBD));
// descrição LOG
sDetalhesLog += itemOriginal.Key + ": [" + (itemOriginal.Value == null ? "NULL" : itemOriginal.Value.ToString()) + "]\r\n";
}
sSQL = sSQL.Substring(0, sSQL.Length - 5);
cmd.CommandText = sSQL;
cmd.Prepare();
// Executa
int iAffectedRows = cmd.ExecuteNonQuery();
if (!iAffectedRows.Equals(1))
{
throw new Exception(string.Format("Erro ao executar delete. Não foi afetado o número esperado de linhas.\r\nLinhas afetadas: {0}.\r\nDetalhes: {1}.", iAffectedRows, sDetalhesLog));
}
}
}
public static int SendData(string sproc, List <ParamStruct> paramList)
{
SDC.DbProviderFactory factory = SDC.DbProviderFactories.GetFactory(Properties.Settings.Default.provider);
SDC.DbCommand comm = factory.CreateCommand();
comm = BuildCommand(sproc, paramList);
SDC.DbConnection conn = Connection(factory);
comm.Connection = conn;
conn.Open();
int count = comm.ExecuteNonQuery();
for (int i = 0; i < comm.Parameters.Count - 1; i++)
{
ParamStruct x = paramList[i];
x.paramValue = comm.Parameters[i].Value;
paramList[i] = x;
}
return(count);
}
private bool ExecuteScriptFile(string pathToScriptFile, out string errorMsg)
{
System.IO.StreamReader reader = null;
System.Data.SqlClient.SqlConnection connection = null;
bool result;
try
{
string applicationPath = Globals.ApplicationPath;
System.IO.StreamReader streamReader;
reader = (streamReader = new System.IO.StreamReader(pathToScriptFile));
try
{
System.Data.SqlClient.SqlConnection sqlConnection;
connection = (sqlConnection = new System.Data.SqlClient.SqlConnection(this.GetConnectionString()));
try
{
System.Data.SqlClient.SqlCommand command2 = new System.Data.SqlClient.SqlCommand
{
Connection = connection,
CommandType = System.Data.CommandType.Text,
CommandTimeout = 60
};
System.Data.Common.DbCommand command3 = command2;
connection.Open();
while (!reader.EndOfStream)
{
string str = Install.NextSqlFromStream(reader);
if (!string.IsNullOrEmpty(str))
{
command3.CommandText = str.Replace("$VirsualPath$", applicationPath);
command3.ExecuteNonQuery();
}
}
connection.Close();
}
finally
{
if (sqlConnection != null)
{
((System.IDisposable)sqlConnection).Dispose();
}
}
reader.Close();
}
finally
{
if (streamReader != null)
{
((System.IDisposable)streamReader).Dispose();
}
}
errorMsg = null;
result = true;
}
catch (System.Data.SqlClient.SqlException exception)
{
errorMsg = exception.Message;
if (connection != null && connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
connection.Dispose();
}
if (reader != null)
{
reader.Close();
reader.Dispose();
}
result = false;
}
return(result);
}
public static int ExecuteNonQuery(DbCommand dbCommand)
{
if (dbConnection.State != ConnectionState.Open)
dbConnection.Open();
int iExecuteNonQuery = dbCommand.ExecuteNonQuery();
dbConnection.Close();
return iExecuteNonQuery;
}
/// <summary>
/// Execute an update, delete, or insert command
/// and return the number of affected rows
/// </summary>
/// <param name="command">database command</param>
public static int ExecuteNonQuery(DbCommand command)
{
var affectedRows = -1;
using (command)
{
try
{
command.Connection.Open();
affectedRows = command.ExecuteNonQuery();
}
catch (Exception ex)
{
Utilities.LogError(ex);
}
finally
{
if (command != null)
{
command.Parameters.Clear();
command.Connection.Close();
}
}
return affectedRows;
}
}
private int DoExecuteNonQuery(DbCommand command)
{
if (IsBatchConnection && batchCommander.batchSize > 1)
{
batchCommander.Process(command);
return 0;
}
try
{
WriteLog(command);
int rowsAffected = command.ExecuteNonQuery();
return rowsAffected;
}
catch
{
throw;
}
}
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 int ExecuteSQLCommand(System.Data.Common.DbCommand com, System.Data.Common.DbTransaction trans)
{
Connect();
//com.Transaction = trans;
return(com.ExecuteNonQuery());
}
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();
}
}
public static int Execute(this System.Data.Common.DbCommand cmd, string command)
{
cmd.CommandText = command;
return(cmd.ExecuteNonQuery());
}
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);
}
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);
}
public int ExecuteSQLCommand(System.Data.Common.DbCommand com)
{
Connect();
return(com.ExecuteNonQuery());
}
