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