///<summary>
/// Execute a stocked procedure.
/// <param name="schema">
/// <see cref="SharpQuery.SchemaClass">SchemaClass</see> object.
/// </param>
/// <param name="rows">
/// Maximum number of row to extract. If is "0" then all rows are extracted.
/// </param>
/// <returns> return a <see cref="System.Data.DataTable">DataTable</see>
///or a <see cref="System.Data.DataSet">DataSet</see> object.
/// </returns>
/// </summary>
public override object ExecuteProcedure(ISchemaClass schema, int rows, SharpQuerySchemaClassCollection parameters)
{
DataTable table = null;
if (schema == null)
{
throw new System.ArgumentNullException("schema");
}
ADODB.Recordset record = null;
ADODB.Command command = new ADODB.Command();
command.ActiveConnection = this.pADOConnection;
ADODB.Parameter para = null;
command.CommandText = schema.Name;
command.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc;
if (parameters != null)
{
foreach (SharpQueryParameter classParam in parameters)
{
para = new ADODB.Parameter();
para.Type = DbTypeToDataType(classParam.DataType);
para.Direction = ParamDirectionToADODirection(classParam.Type);
para.Name = classParam.Name;
if (para.Name.StartsWith("["))
{
para.Name = para.Name.Remove(0, 1);
}
if (para.Name.EndsWith("]"))
{
para.Name = para.Name.Remove(para.Name.Length - 1, 1);
}
para.Value = classParam.Value;
command.Parameters.Append(para);
}
}
this.pADOConnection.BeginTrans();
try
{
record = (ADODB.Recordset)command.GetType().InvokeMember(
"Execute",
System.Reflection.BindingFlags.InvokeMethod,
null,
command,
null);
//record.MaxRecords = rows;
table = RecordSetToDataTable(record);
//Procedure is ReadOnly
table.DefaultView.AllowDelete = false;
table.DefaultView.AllowEdit = false;
table.DefaultView.AllowNew = false;
}
catch (System.Exception e)
{
if (schema != null)
{
this.pADOConnection.RollbackTrans();
string mes = schema.Name + "\n\r";
foreach (ADODB.Error err in this.pADOConnection.Errors)
{
mes += "-----------------\n\r";
mes += err.Description + "\n\r";
mes += err.NativeError + "\n\r";
}
throw new ExecuteProcedureException(mes);
}
else
{
throw new ExecuteProcedureException(e.Message);
}
}
this.pADOConnection.CommitTrans();
return table;
}
public void UpdateN4Unit()
{
string Storage = PaidThruDate.Value.Year > 2000 ? $@"'{PaidThruDate.ToString()}'" : "null";
string Electricity = PlugOut.Value.Year > 2000 ? $@"'{PlugOut.ToString()}'" : "null";
string arrastrePayment = IsArrastrePaid ? $@"'{DateTime.Now.ToString()}'" : "null";
//Storage = PaidThruDate.Value.Year == LastFreeDay.Value.Year &&
// PaidThruDate.Value.Month == LastFreeDay.Value.Month &&
// PaidThruDate.Value.Day == LastFreeDay.Value.Day ? "null" : Storage;
//PaidThruDate = Storage == "null" ? Convert.ToDateTime("1970-01-01 00:00:00") : DateTime.ParseExact(Storage.Replace("'",""), "M/d/yyyy h:mm:ss tt", null); //update values
//PlugOut = Electricity == "null" ? Convert.ToDateTime("1970-01-01 00:00:00") : DateTime.ParseExact(Electricity.Replace("'", ""), "M/d/yyyy h:mm:ss tt", null); //update values
//LastFreeDay = arrastrePayment == "null" ? Convert.ToDateTime("1970-01-01 00:00:00") : DateTime.ParseExact(arrastrePayment.Replace("'", ""), "M/d/yyyy h:mm:ss tt", null); //update values
ADODB.Connection DEVN4Connection = new Connections().DEVN4Connection;
//Connect
DEVN4Connection.Open();
//Update
ADODB.Command updateCommand = new ADODB.Command();
updateCommand.ActiveConnection = DEVN4Connection;
updateCommand.CommandText = $@"
UPDATE [apex].[dbo].[inv_unit_fcy_visit]
SET [flex_date01] = {arrastrePayment}
,[flex_date02] = {Electricity}
,[flex_date03] = {Storage}
WHERE unit_gkey = {Gkey}
";
updateCommand.Execute(out object dsad, 0, 0);
DEVN4Connection.Close();
}
public string GetViewText(string connectionString, ViewSchema view)
{
ADOX.Catalog catalog = GetCatalog(connectionString);
ADOX.View v = catalog.Views[view.Name];
ADODB.Command cmd = v.Command as ADODB.Command;
return(cmd != null ? cmd.CommandText : String.Empty);
}
public string GetCommandText(string connectionString, CommandSchema command)
{
ADOX.Catalog catalog = GetCatalog(connectionString);
ADOX.Procedure procedure = catalog.Procedures[command.Name];
ADODB.Command cmd = procedure.Command as ADODB.Command;
return(cmd != null ? cmd.CommandText : String.Empty);
}
/// <summary>
/// Call DB Stored Procedure to generate Polygon Geometries for Detail Foorprint component
/// </summary>
/// <param name="g3eFid"></param>
/// <param name="detailID"></param>
/// <param name="xCoord"></param>
/// <param name="yCoord"></param>
/// <param name="mhType"></param>
private void PlaceManholeFootprint(int g3eFid, int detailID, double xCoord, double yCoord, string mhType)
{
ADODB.Command cmd = null;
int outRecords = 0;
string sqlString = string.Format("Begin FootPrintDetailPlacement.AddManholeDetailFootprint({0},{1},'{2}',{3},{4}); End;", g3eFid, detailID, mhType, xCoord, yCoord);
try
{
cmd = new ADODB.Command();
cmd.CommandText = sqlString;
ADODB.Recordset results = _dataContext.ExecuteCommand(cmd, out outRecords);
}
catch (Exception ex)
{
throw ex;
}
}
public CommandSchema[] GetCommands(string connectionString, DatabaseSchema database)
{
ADOX.Catalog catalog = GetCatalog(connectionString);
ArrayList commands = new ArrayList();
for (int i = 0; i < catalog.Procedures.Count; i++)
{
ADOX.Procedure proc = catalog.Procedures[i];
ADODB.Command cmd = (ADODB.Command)proc.Command;
commands.Add(new CommandSchema(database, catalog.Procedures[i].Name, "", DateTime.MinValue, ConvertToExtendedProperties(cmd.Properties))); //catalog.GetObjectOwner(catalog.Procedures[i].Name, ADOX.ObjectTypeEnum.adPermObjProcedure, null));
}
Cleanup();
return((CommandSchema[])commands.ToArray(typeof(CommandSchema)));
}
public ParameterSchema[] GetCommandParameters(string connectionString, CommandSchema command)
{
ADOX.Catalog catalog = GetCatalog(connectionString);
ADOX.Procedure adoxprocedure = catalog.Procedures[command.Name];
ADODB.Command cmd = (ADODB.Command)adoxprocedure.Command;
cmd.Parameters.Refresh();
ParameterSchema[] parameters = new ParameterSchema[cmd.Parameters.Count];
for (int i = 0; i < cmd.Parameters.Count; i++)
{
var properties = new ExtendedPropertyCollection(ConvertToExtendedProperties(cmd.Parameters[i].Properties));
bool allowDBNull = false;
try
{
allowDBNull = cmd.Parameters[i].Properties["Nullable"] != null && (bool)cmd.Parameters[i].Properties["Nullable"].Value;
if (cmd.Parameters[i].Properties["Default"] != null)
{
properties.Add(new ExtendedProperty(ExtendedPropertyNames.DefaultValue, cmd.Parameters[i].Properties["Default"].Value, DbType.String, PropertyStateEnum.ReadOnly));
}
}
catch {}
parameters[i] = new ParameterSchema(
command,
cmd.Parameters[i].Name,
GetParameterDirection(cmd.Parameters[i].Direction),
GetDbType(cmd.Parameters[i].Type),
cmd.Parameters[i].Type.ToString(),
cmd.Parameters[i].Size,
cmd.Parameters[i].Precision,
cmd.Parameters[i].NumericScale,
allowDBNull,
properties.ToArray());
}
Cleanup();
return(parameters);
}
//------------------------------------------------------------------------------------
//subroutine to create TLs or EVs based on the user's selection
public void createOBJs(ref ADODB.Connection adoConn, string sqlstring)
{
ADODB.Recordset adoRS = new ADODB.Recordset();
ADODB.Command adocmd = new ADODB.Command();
object objRecAff;
//---------------------------------------------------------------------------------------------------------------------
//open the ODBC connection
adocmd.ActiveConnection = adoConn;
// adoRS.ActiveConnection = adoConn
//---------------------------------------------------------------------------------------------------------------------
//send the SQL query to the ODBC connection and create the objects
//adoRS.Open(sqlstring)
adocmd.CommandText = sqlstring;
adocmd.Execute(out objRecAff);
// adoRS.Close()
// adoRS = Nothing
adocmd = null;
}
public static ObservableCollection <Yard_Container> GetYardContainer()
{
ObservableCollection <Yard_Container> _yardContainers;
ADODB.Connection N4Connection = new Connections().N4Connection;
//Connect
N4Connection.Open();
//Retrieve
ADODB.Command retrieveCommand = new ADODB.Command();
retrieveCommand.ActiveConnection = N4Connection;
retrieveCommand.CommandText = $@"
SELECT iu.id 'Container Number'
,category 'Category'
,freight_kind 'Freight Kind'
,transit_state 'Transit State'
,time_in
,time_discharge_complete
,iu.gkey
,last_free_day
,requires_power
,ata
FROM [apex].[dbo].inv_unit iu
inner join inv_unit_fcy_visit iufv on iufv.unit_gkey = iu.gkey
left join argo_carrier_visit acv on iufv.actual_ib_cv = acv.gkey
left join argo_visit_details avd on acv.cvcvd_gkey = avd.gkey
where transit_state like '%YARD%'
";
System.Data.DataTable _yardContainersTable = new System.Data.DataTable();
System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter();
adapter.Fill(_yardContainersTable, retrieveCommand.Execute(out object dsadsad, 0, 0));
//Convert datatable to observable collection
_yardContainers = Generate(_yardContainersTable);
//return
return(_yardContainers);
}
//TODO: Дописать экранирование спецсимволов
public static bool ExecuteQuery(string sql)
{
ADODB.Command cmd = new ADODB.Command
{
ActiveConnection = CnMain,
CommandText = sql,
CommandType = ADODB.CommandTypeEnum.adCmdText
};
try
{
object nRecordsAffected = Type.Missing;
object oParams = Type.Missing;
cmd.Execute(out nRecordsAffected, ref oParams, (int)ADODB.ExecuteOptionEnum.adExecuteNoRecords);
return(true);
}
catch
{
WriteLog.writeStringInLog("Ошибка выполнения запроса: " + sql);
return(false);
}
}
//SS:01/03/2018:2018-R1:ABSEXCH-19796: When Running the ExchDVT.exe, SQL Admin Passwords are visible in dump file.
//Generic routine
private void ExecuteQuery(string connStr, string query, string connPassword)
{
ADODB.Connection conn = new ADODB.Connection();
ADODB.Command cmd = new ADODB.Command();
cmd.CommandText = query;
cmd.CommandTimeout = 10000;
if (conn.State == 0)
{
if (connPassword.Trim() == "")
{
conn.Open();
}
else
{
conn.Open(connStr, "", connPassword.Trim(),
(int)ADODB.ConnectModeEnum.adModeUnknown);
}
}
conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
try
{
Object recAff;
cmd.ActiveConnection = conn;
cmd.CommandType = ADODB.CommandTypeEnum.adCmdText;
cmd.Execute(out recAff, Type.Missing, (int)ADODB.CommandTypeEnum.adCmdText);
if (conn.State == 1)
{
conn.Close();
}
}
catch
{
throw;
}
}
static void Main(string[] args)
{
ADODB.Connection conn = null;
ADODB.Recordset rs = null;
try
{
////////////////////////////////////////////////////////////////////////////////
// Connect to the data source.
//
Console.WriteLine("Connecting to the database ...");
// Get the connection string from App.config. (The data source is created in the
// sample SQLServer2005DB)
string connStr = ConfigurationManager.ConnectionStrings["SQLServer2005DB"].
ConnectionString;
// Open the connection
conn = new ADODB.Connection();
conn.Open(connStr, null, null, 0);
////////////////////////////////////////////////////////////////////////////////
// Build and Execute an ADO Command.
// It can be a SQL statement (SELECT/UPDATE/INSERT/DELETE), or a stored
// procedure call. Here is the sample of an INSERT command.
//
Console.WriteLine("Inserting a record to the Person table...");
// 1. Create a command object
ADODB.Command cmdInsert = new ADODB.Command();
// 2. Assign the connection to the command
cmdInsert.ActiveConnection = conn;
// 3. Set the command text
// SQL statement or the name of the stored procedure
cmdInsert.CommandText = "INSERT INTO Person(LastName, FirstName, EnrollmentDate, Picture)"
+ " VALUES (?, ?, ?, ?)";
// 4. Set the command type
// ADODB.CommandTypeEnum.adCmdText for oridinary SQL statements;
// ADODB.CommandTypeEnum.adCmdStoredProc for stored procedures.
cmdInsert.CommandType = ADODB.CommandTypeEnum.adCmdText;
// 5. Append the parameters
// Append the parameter for LastName (nvarchar(50)
ADODB.Parameter paramLN = cmdInsert.CreateParameter(
"LastName", // Parameter name
ADODB.DataTypeEnum.adVarChar, // Parameter type (nvarchar(50))
ADODB.ParameterDirectionEnum.adParamInput, // Parameter direction
50, // Max size of value in bytes
"Zhang"); // Parameter value
cmdInsert.Parameters.Append(paramLN);
// Append the parameter for FirstName (nvarchar(50))
ADODB.Parameter paramFN = cmdInsert.CreateParameter(
"FirstName", // Parameter name
ADODB.DataTypeEnum.adVarChar, // Parameter type (nvarchar(50))
ADODB.ParameterDirectionEnum.adParamInput, // Parameter direction
50, // Max size of value in bytes
"Rongchun"); // Parameter value
cmdInsert.Parameters.Append(paramFN);
// Append the parameter for EnrollmentDate (datetime)
ADODB.Parameter paramED = cmdInsert.CreateParameter(
"EnrollmentDate", // Parameter name
ADODB.DataTypeEnum.adDate, // Parameter type (datetime)
ADODB.ParameterDirectionEnum.adParamInput, // Parameter direction
-1, // Max size (ignored for datetime)
DateTime.Now); // Parameter value
cmdInsert.Parameters.Append(paramED);
// Append the parameter for Picture (image)
// Read the image file into a safe array of bytes
Byte[] bImage = ReadImage(@"MSDN.jpg");
ADODB.Parameter paramImage = cmdInsert.CreateParameter(
"Picture", // Parameter name
ADODB.DataTypeEnum.adLongVarBinary, // Parameter type (Image)
ADODB.ParameterDirectionEnum.adParamInput, // Parameter direction
bImage != null ? bImage.Length : 1, // Max size of value in bytes
bImage); // Parameter value
cmdInsert.Parameters.Append(paramImage);
// 6. Execute the command
object nRecordsAffected = Type.Missing;
object oParams = Type.Missing;
cmdInsert.Execute(out nRecordsAffected, ref oParams,
(int)ADODB.ExecuteOptionEnum.adExecuteNoRecords);
////////////////////////////////////////////////////////////////////////////////
// Use the Recordset Object.
// http://msdn.microsoft.com/en-us/library/ms681510.aspx
// Recordset represents the entire set of records from a base table or the
// results of an executed command. At any time, the Recordset object refers to
// only a single record within the set as the current record.
//
Console.WriteLine("Enumerating the records in the Person table");
// 1. Create a Recordset object
rs = new ADODB.Recordset();
// 2. Open the Recordset object
string strSelectCmd = "SELECT * FROM Person"; // WHERE ...
rs.Open(strSelectCmd, // SQL statement / table,view name /
// stored procedure call / file name
conn, // Connection / connection string
ADODB.CursorTypeEnum.adOpenForwardOnly, // Cursor type. (forward-only cursor)
ADODB.LockTypeEnum.adLockOptimistic, // Lock type. (locking records only
// when you call the Update method.
(int)ADODB.CommandTypeEnum.adCmdText); // Evaluate the first parameter as
// a SQL command or stored procedure.
// 3. Enumerate the records by moving the cursor forward
rs.MoveFirst(); // Move to the first record in the Recordset
while (!rs.EOF)
{
int nPersonId = (int)rs.Fields["PersonID"].Value;
// When dumping a SQL-Nullable field in the table, need to test it for
// DBNull.Value.
string strFirstName = (rs.Fields["FirstName"].Value == DBNull.Value) ?
"(DBNull)" : rs.Fields["FirstName"].Value.ToString();
string strLastName = (rs.Fields["LastName"].Value == DBNull.Value) ?
"(DBNull)" : rs.Fields["LastName"].Value.ToString();
Console.WriteLine("{0}\t{1} {2}", nPersonId, strFirstName, strLastName);
// Update the current record while enumerating the Recordset.
//rs.Fields["XXXX"].Value = XXXX
//rs.Update(); [-or-] rs.UpdateBatch(); outside the loop.
rs.MoveNext(); // Move to the next record
}
}
catch (Exception ex)
{
Console.WriteLine("The application throws the error: {0}", ex.Message);
if (ex.InnerException != null)
{
Console.WriteLine("Description: {0}", ex.InnerException.Message);
}
}
finally
{
////////////////////////////////////////////////////////////////////////////////
// Clean up objects before exit.
//
Console.WriteLine("Closing the connections ...");
// Close the record set if it is open
if (rs != null && rs.State == (int)ADODB.ObjectStateEnum.adStateOpen)
{
rs.Close();
}
// Close the connection to the database if it is open
if (conn != null && conn.State == (int)ADODB.ObjectStateEnum.adStateOpen)
{
conn.Close();
}
}
}
override internal void LoadAll()
{
DataTable metaData = CreateDataTable();
ADODB.Connection cnn = new ADODB.Connection();
ADOX.Catalog cat = new ADOX.Catalog();
// Open the Connection
cnn.Open(dbRoot.ConnectionString, null, null, 0);
cat.ActiveConnection = cnn;
ADOX.Procedure proc = cat.Procedures[this.Procedure.Name];
ADODB.Command cmd = proc.Command as ADODB.Command;
// Retrieve Parameter information
cmd.Parameters.Refresh();
if (cmd.Parameters.Count > 0)
{
int ordinal = 0;
foreach (ADODB.Parameter param in cmd.Parameters)
{
DataRow row = metaData.NewRow();
string hyperlink = "False";
try
{
hyperlink = param.Properties["Jet OLEDB:Hyperlink"].Value.ToString();
}
catch {}
row["TYPE_NAME"] = hyperlink == "False" ? param.Type.ToString() : "Hyperlink";
row["PROCEDURE_CATALOG"] = this.Procedure.Database;
row["PROCEDURE_SCHEMA"] = null;
row["PROCEDURE_NAME"] = this.Procedure.Name;
row["PARAMETER_NAME"] = param.Name;
row["ORDINAL_POSITION"] = ordinal++;
row["PARAMETER_TYPE"] = param.Type; //.ToString();
row["PARAMETER_HASDEFAULT"] = false;
row["PARAMETER_DEFAULT"] = null;
row["IS_NULLABLE"] = false;
row["DATA_TYPE"] = param.Type; //.ToString();
row["CHARACTER_MAXIMUM_LENGTH"] = 0;
row["CHARACTER_OCTET_LENGTH"] = 0;
row["NUMERIC_PRECISION"] = param.Precision;
row["NUMERIC_SCALE"] = param.NumericScale;
row["DESCRIPTION"] = "";
// row["TYPE_NAME"] = "";
row["LOCAL_TYPE_NAME"] = "";
metaData.Rows.Add(row);
}
}
cnn.Close();
base.PopulateArray(metaData);
}
///<summary>
/// Execute a stocked procedure.
/// <param name="schema">
/// <see cref="SharpQuery.SchemaClass">SchemaClass</see> object.
/// </param>
/// <param name="rows">
/// Maximum number of row to extract. If is "0" then all rows are extracted.
/// </param>
/// <returns> return a <see cref="System.Data.DataTable">DataTable</see>
///or a <see cref="System.Data.DataSet">DataSet</see> object.
/// </returns>
/// </summary>
public override object ExecuteProcedure(ISchemaClass schema, int rows, SharpQuerySchemaClassCollection parameters)
{
DataTable table = null;
if (schema == null)
{
throw new System.ArgumentNullException("schema");
}
ADODB.Recordset record = null;
ADODB.Command command = new ADODB.Command();
command.ActiveConnection = this.pADOConnection;
ADODB.Parameter para = null;
command.CommandText = schema.Name;
command.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc;
if (parameters != null)
{
foreach (SharpQueryParameter classParam in parameters)
{
para = new ADODB.Parameter();
para.Type = DbTypeToDataType(classParam.DataType);
para.Direction = ParamDirectionToADODirection(classParam.Type);
para.Name = classParam.Name;
if (para.Name.StartsWith("["))
{
para.Name = para.Name.Remove(0, 1);
}
if (para.Name.EndsWith("]"))
{
para.Name = para.Name.Remove(para.Name.Length - 1, 1);
}
para.Value = classParam.Value;
command.Parameters.Append(para);
}
}
this.pADOConnection.BeginTrans();
try
{
record = (ADODB.Recordset)command.GetType().InvokeMember(
"Execute",
System.Reflection.BindingFlags.InvokeMethod,
null,
command,
null);
//record.MaxRecords = rows;
table = RecordSetToDataTable(record);
//Procedure is ReadOnly
table.DefaultView.AllowDelete = false;
table.DefaultView.AllowEdit = false;
table.DefaultView.AllowNew = false;
}
catch (System.Exception e)
{
if (schema != null)
{
this.pADOConnection.RollbackTrans();
string mes = schema.Name + "\n\r";
foreach (ADODB.Error err in this.pADOConnection.Errors)
{
mes += "-----------------\n\r";
mes += err.Description + "\n\r";
mes += err.NativeError + "\n\r";
}
throw new ExecuteProcedureException(mes);
}
else
{
throw new ExecuteProcedureException(e.Message);
}
}
this.pADOConnection.CommitTrans();
return(table);
}
public static ObservableCollection <Paid_Container> GetPaidContainer()
{
ObservableCollection <Paid_Container> _paidContainers;
ADODB.Connection BLConnection = new Connections().BLConnection;
ADODB.Connection BLConnection2 = new Connections().BLConnection;
ADODB.Connection BLConnection3 = new Connections().BLConnection;
//Connect
BLConnection.Open();
BLConnection2.Open();
BLConnection3.Open();
//Retrieve
#region GPS
ADODB.Command retrieveCommandGPS = new ADODB.Command();
retrieveCommandGPS.ActiveConnection = BLConnection;
retrieveCommandGPS.CommandText = $@"
SELECT [refnum]
,[seqnum]
,[gpsnum]
,[gpstyp]
,[cntnum]
,[bilnum]
,[regnum]
,[crodte]
,[stoday]
,[freday]
,[stosta]
,[stoamt]
,[plugin]
,[plugou]
,[lstdch]
,[stobeg]
,[freeuntil]
,[stoend]
,[status]
,[sysdte]
,[CompanyCode]
FROM [billing].[dbo].[CYMgps] where status <> 'CAN'
";
#endregion
#region CCR
ADODB.Command retrieveCommandCCR = new ADODB.Command();
retrieveCommandCCR.ActiveConnection = BLConnection2;
retrieveCommandCCR.CommandText = $@"
SELECT [refnum]
,[seqnum]
,[itmnum]
,[ccrnum]
,[ccrtyp]
,[chargetyp]
,[descr]
,[docrefno]
,[entnum]
,[regnum]
,[cntnum]
,[cntsze]
,[fulemp]
,[amt]
,[vatamt]
,[wtax]
,[vatcde]
,[stostat]
,[lngth]
,[width]
,[height]
,[ums]
,[quantity]
,[dgrcls]
,[dgramt]
,[revton]
,[ovzamt]
,[enrfrdttm]
,[enstodttm]
,[stordys]
,[rfrhrs]
,[remark]
,[guarntycde]
,[status]
,[shplin]
,[vslcde]
,[pod]
,[userid]
,[sysdttm]
,[updcde]
,[outdttm]
,[IsN4ReeferPaymentUpdated]
,[CompanyCode]
FROM [billing].[dbo].[CCRdtl] where (chargetyp like '%CBIMP%' or chargetyp like '%CBEXP%') and status <> 'CAN'
";
#endregion
#region CYX
ADODB.Command retrieveCommandCYX = new ADODB.Command();
retrieveCommandCYX.ActiveConnection = BLConnection3;
retrieveCommandCYX.CommandText = $@"
SELECT [refnum]
,[seqnum]
,[itmnum]
,[cntnum]
,[ccrnum]
,[cntsze]
,[fulemp]
,[dgrcls]
,[vslcde]
,[whfamt]
,[arramt]
,[ovzamt]
,[dgramt]
,[arrvat]
,[arrtax]
,[vatcde]
,[cntovzl]
,[cntovzw]
,[cntovzh]
,[ovzums]
,[revton]
,[trncde]
,[whfcde]
,[guarntycde]
,[dolrte]
,[exprtr]
,[broker]
,[entnum]
,[commod]
,[remark]
,[trknam]
,[pltnum]
,[trkchs]
,[status]
,[ovrccr]
,[ppanum]
,[userid]
,[sysdttm]
,[updcde]
,[outdttm]
,[supvsr]
,[IsN4BillingPermissionGranted]
,[wghamt]
,[IsN4BillingDGPermissionGranted]
,[IsN4BillingOOGPermissionGranted]
,[CompanyCode]
FROM [billing].[dbo].[CCRcyx] where status <> 'CAN'
";
#endregion
System.Data.DataTable _paidContainersTableGPS = new System.Data.DataTable();
System.Data.DataTable _paidContainersTableCCR = new System.Data.DataTable();
System.Data.DataTable _paidContainersTableCYX = new System.Data.DataTable();
System.Data.OleDb.OleDbDataAdapter adapterGPS = new System.Data.OleDb.OleDbDataAdapter();
System.Data.OleDb.OleDbDataAdapter adapterCCR = new System.Data.OleDb.OleDbDataAdapter();
System.Data.OleDb.OleDbDataAdapter adapterCYX = new System.Data.OleDb.OleDbDataAdapter();
adapterGPS.Fill(_paidContainersTableGPS, retrieveCommandGPS.Execute(out object dsdsad, 0, 0));
adapterGPS.Fill(_paidContainersTableCCR, retrieveCommandCCR.Execute(out object dsdsad2, 0, 0)); //2 implicit initialization
adapterGPS.Fill(_paidContainersTableCYX, retrieveCommandCYX.Execute(out object dsdsad3, 0, 0)); //2 implicit initialization
//Convert datatable to observable collection
_paidContainers = Generate(_paidContainersTableGPS, _paidContainersTableCCR, _paidContainersTableCYX);
//return
return(_paidContainers);
}
static void Main(string[] args)
{
ADODB.Connection conn = null;
ADODB.Recordset rs = null;
try
{
////////////////////////////////////////////////////////////////////////////////
// Connect to the data source.
//
Console.WriteLine("Connecting to the database ...");
// Get the connection string
string connStr = string.Format("Provider=SQLOLEDB;Data Source={0};Initial Catalog={1};Integrated Security=SSPI",
".\\sqlexpress", "SQLServer2005DB");
// Open the connection
conn = new ADODB.Connection();
conn.Open(connStr, null, null, 0);
////////////////////////////////////////////////////////////////////////////////
// Build and Execute an ADO Command.
// It can be a SQL statement (SELECT/UPDATE/INSERT/DELETE), or a stored
// procedure call. Here is the sample of an INSERT command.
//
Console.WriteLine("Inserting a record to the CountryRegion table...");
// 1. Create a command object
ADODB.Command cmdInsert = new ADODB.Command();
// 2. Assign the connection to the command
cmdInsert.ActiveConnection = conn;
// 3. Set the command text
// SQL statement or the name of the stored procedure
cmdInsert.CommandText = "INSERT INTO CountryRegion(CountryRegionCode, Name, ModifiedDate)"
+ " VALUES (?, ?, ?)";
// 4. Set the command type
// ADODB.CommandTypeEnum.adCmdText for oridinary SQL statements;
// ADODB.CommandTypeEnum.adCmdStoredProc for stored procedures.
cmdInsert.CommandType = ADODB.CommandTypeEnum.adCmdText;
// 5. Append the parameters
// Append the parameter for CountryRegionCode (nvarchar(20)
ADODB.Parameter paramCode = cmdInsert.CreateParameter(
"CountryRegionCode", // Parameter name
ADODB.DataTypeEnum.adVarChar, // Parameter type (nvarchar(20))
ADODB.ParameterDirectionEnum.adParamInput, // Parameter direction
20, // Max size of value in bytes
"ZZ" + DateTime.Now.Millisecond); // Parameter value
cmdInsert.Parameters.Append(paramCode);
// Append the parameter for Name (nvarchar(200))
ADODB.Parameter paramName = cmdInsert.CreateParameter(
"Name", // Parameter name
ADODB.DataTypeEnum.adVarChar, // Parameter type (nvarchar(200))
ADODB.ParameterDirectionEnum.adParamInput, // Parameter direction
200, // Max size of value in bytes
"Test Region Name"); // Parameter value
cmdInsert.Parameters.Append(paramName);
// Append the parameter for ModifiedDate (datetime)
ADODB.Parameter paramModifiedDate = cmdInsert.CreateParameter(
"ModifiedDate", // Parameter name
ADODB.DataTypeEnum.adDate, // Parameter type (datetime)
ADODB.ParameterDirectionEnum.adParamInput, // Parameter direction
-1, // Max size (ignored for datetime)
DateTime.Now); // Parameter value
cmdInsert.Parameters.Append(paramModifiedDate);
// 6. Execute the command
object nRecordsAffected = Type.Missing;
object oParams = Type.Missing;
cmdInsert.Execute(out nRecordsAffected, ref oParams,
(int)ADODB.ExecuteOptionEnum.adExecuteNoRecords);
////////////////////////////////////////////////////////////////////////////////
// Use the Recordset Object.
// http://msdn.microsoft.com/en-us/library/ms681510.aspx
// Recordset represents the entire set of records from a base table or the
// results of an executed command. At any time, the Recordset object refers to
// only a single record within the set as the current record.
//
Console.WriteLine("Enumerating the records in the CountryRegion table");
// 1. Create a Recordset object
rs = new ADODB.Recordset();
// 2. Open the Recordset object
string strSelectCmd = "SELECT * FROM CountryRegion"; // WHERE ...
rs.Open(strSelectCmd, // SQL statement / table,view name /
// stored procedure call / file name
conn, // Connection / connection string
ADODB.CursorTypeEnum.adOpenForwardOnly, // Cursor type. (forward-only cursor)
ADODB.LockTypeEnum.adLockOptimistic, // Lock type. (locking records only
// when you call the Update method.
(int)ADODB.CommandTypeEnum.adCmdText); // Evaluate the first parameter as
// a SQL command or stored procedure.
// 3. Enumerate the records by moving the cursor forward
// Move to the first record in the Recordset
rs.MoveFirst();
while (!rs.EOF)
{
// When dumping a SQL-Nullable field in the table, need to test it for DBNull.Value.
string code = (rs.Fields["CountryRegionCode"].Value == DBNull.Value) ?
"(DBNull)" : rs.Fields["CountryRegionCode"].Value.ToString();
string name = (rs.Fields["Name"].Value == DBNull.Value) ?
"(DBNull)" : rs.Fields["Name"].Value.ToString();
DateTime modifiedDate = (rs.Fields["ModifiedDate"].Value == DBNull.Value) ?
DateTime.MinValue : (DateTime)rs.Fields["ModifiedDate"].Value;
Console.WriteLine(" {2} \t{0}\t{1}", code, name, modifiedDate.ToString("yyyy-MM-dd"));
// Move to the next record
rs.MoveNext();
}
}
catch (Exception ex)
{
Console.WriteLine("The application throws the error: {0}", ex.Message);
if (ex.InnerException != null)
{
Console.WriteLine("Description: {0}", ex.InnerException.Message);
}
}
finally
{
////////////////////////////////////////////////////////////////////////////////
// Clean up objects before exit.
//
Console.WriteLine("Closing the connections ...");
// Close the record set if it is open
if (rs != null && rs.State == (int)ADODB.ObjectStateEnum.adStateOpen)
{
rs.Close();
}
// Close the connection to the database if it is open
if (conn != null && conn.State == (int)ADODB.ObjectStateEnum.adStateOpen)
{
conn.Close();
}
}
}
private void cmd_Loc_Search_Click(object sender, EventArgs e)
{
if (txt_slab_no.Text == "")
{
GeneralCommon.Gp_MsgBoxDisplay("必须输入板坯号", "W", "错误提示");
return;
}
string ret_Result_ErrMsg;
int ret_Result_ErrCode = 0;
ADODB.Command adoCmd;
try
{
if (GeneralCommon.M_CN1.State == 0)
{
if (GeneralCommon.GF_DbConnect() == false)
{
return;
}
}
Cursor.Current = Cursors.WaitCursor;
GeneralCommon.M_CN1.CursorLocation = ADODB.CursorLocationEnum.adUseServer;
adoCmd = new ADODB.Command();
adoCmd.ActiveConnection = GeneralCommon.M_CN1;
adoCmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc;
adoCmd.CommandText = "AFL2010P";
GeneralCommon.M_CN1.BeginTrans();
for (int i = 1; i <= 2; i++)
{
adoCmd.Parameters.Append(adoCmd.CreateParameter("", ADODB.DataTypeEnum.adVariant, ADODB.ParameterDirectionEnum.adParamInput, 0, null));
}
object value = null;
adoCmd.Parameters.Append(adoCmd.CreateParameter("arg_e_msg1", ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamOutput, 256, value));
adoCmd.Parameters.Append(adoCmd.CreateParameter("arg_e_msg2", ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamOutput, 256, value));
adoCmd.Parameters.Append(adoCmd.CreateParameter("arg_e_msg3", ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamOutput, 256, value));
adoCmd.Parameters[0].Value = "SL";
adoCmd.Parameters[1].Value = txt_slab_no.Text;
adoCmd.Execute(out value);
if (adoCmd.Parameters["arg_e_msg1"].Value.ToString().StartsWith("NOT"))
{
GeneralCommon.Gp_MsgBoxDisplay("垛位查询失败，请确认", "W", "");
Cursor.Current = Cursors.Default;
adoCmd = null;
GeneralCommon.M_CN1.RollbackTrans();
}
else
{
txt_location1.Text = adoCmd.Parameters["arg_e_msg1"].Value.ToString();
txt_location2.Text = adoCmd.Parameters["arg_e_msg2"].Value.ToString();
txt_location3.Text = adoCmd.Parameters["arg_e_msg3"].Value.ToString();
GeneralCommon.M_CN1.CommitTrans();
GeneralCommon.M_CN1.Close();
adoCmd = null;
Cursor.Current = Cursors.Default;
}
}
catch (Exception ex)
{
adoCmd = null;
GeneralCommon.M_CN1.RollbackTrans();
Cursor.Current = Cursors.Default;
GeneralCommon.Gp_MsgBoxDisplay((string)(ex.Message), "W", "警告");
}
}
static void Main(string[] args)
{
ADODB.Connection conn = null;
ADODB.Recordset rs = null;
try
{
////////////////////////////////////////////////////////////////////////////////
// 连接数据源.
//
Console.WriteLine("正在连接数据库 ...");
// 获取连接字符串
string connStr = string.Format("Provider=SQLOLEDB;Data Source={0};Initial Catalog={1};Integrated Security=SSPI",
".\\sqlexpress", "SQLServer2005DB");
// 打开连接
conn = new ADODB.Connection();
conn.Open(connStr, null, null, 0);
////////////////////////////////////////////////////////////////////////////////
// 编写并执行ADO命令.
// 可以是SQL指令(SELECT/UPDATE/INSERT/DELETE),或是调用存储过程.
// 此处是一个INSERT命令示例.
//
Console.WriteLine("将一条记录插入表CountryRegion中...");
// 1. 生成一个Command对象
ADODB.Command cmdInsert = new ADODB.Command();
// 2. 将连接赋值于命令
cmdInsert.ActiveConnection = conn;
// 3. 设置命令文本
// SQL指令或者存储过程名
cmdInsert.CommandText = "INSERT INTO CountryRegion(CountryRegionCode, Name, ModifiedDate)"
+ " VALUES (?, ?, ?)";
// 4. 设置命令类型
// ADODB.CommandTypeEnum.adCmdText 用于普通的SQL指令;
// ADODB.CommandTypeEnum.adCmdStoredProc 用于存储过程.
cmdInsert.CommandType = ADODB.CommandTypeEnum.adCmdText;
// 5. 添加参数
// CountryRegionCode (nvarchar(20)参数的添加
ADODB.Parameter paramCode = cmdInsert.CreateParameter(
"CountryRegionCode", // 参数名
ADODB.DataTypeEnum.adVarChar, // 参数类型 (nvarchar(20))
ADODB.ParameterDirectionEnum.adParamInput, // 参数类型
20, // 参数的最大长度
"ZZ"+DateTime.Now.Millisecond); // 参数值
cmdInsert.Parameters.Append(paramCode);
// Name (nvarchar(200))参数的添加
ADODB.Parameter paramName = cmdInsert.CreateParameter(
"Name", // 参数名
ADODB.DataTypeEnum.adVarChar, // 参数类型 (nvarchar(200))
ADODB.ParameterDirectionEnum.adParamInput, // 参数传递方向
200, // 参数的最大长度
"Test Region Name"); // 参数值
cmdInsert.Parameters.Append(paramName);
// ModifiedDate (datetime)参数的添加
ADODB.Parameter paramModifiedDate = cmdInsert.CreateParameter(
"ModifiedDate", // 参数名
ADODB.DataTypeEnum.adDate, // 参数类型 (datetime)
ADODB.ParameterDirectionEnum.adParamInput, // 参数传递方向
-1, // 参数的最大长度 (datetime忽视该值)
DateTime.Now); // 参数值
cmdInsert.Parameters.Append(paramModifiedDate);
// 6. 执行命令
object nRecordsAffected = Type.Missing;
object oParams = Type.Missing;
cmdInsert.Execute(out nRecordsAffected, ref oParams,
(int)ADODB.ExecuteOptionEnum.adExecuteNoRecords);
////////////////////////////////////////////////////////////////////////////////
// 使用Recordset对象.
// http://msdn.microsoft.com/en-us/library/ms681510.aspx
// Recordset表示了数据表中记录或执行命令获得的结果的集合。
// 在任何时候， Recordset对象都指向集合中的单条记录，并将
// 该记录作为它的当前记录。
//
Console.WriteLine("列出表CountryRegion中的所有记录");
// 1. 生成Recordset对象
rs = new ADODB.Recordset();
// 2. 打开Recordset对象
string strSelectCmd = "SELECT * FROM CountryRegion"; // WHERE ...
rs.Open(strSelectCmd, // SQL指令/表,视图名 /
// 存储过程调用 /文件名
conn, // 连接对象/连接字符串
ADODB.CursorTypeEnum.adOpenForwardOnly, // 游标类型. (只进游标)
ADODB.LockTypeEnum.adLockOptimistic, // 锁定类型. (仅当需要调用
// 更新方法时，才锁定记录）
(int)ADODB.CommandTypeEnum.adCmdText); // 将第一个参数视为SQL命令
// 或存储过程.
// 3. 通过向前移动游标列举记录
// 移动到Recordset中的第一条记录
rs.MoveFirst();
while (!rs.EOF)
{
// 当在表中定义了一个可空字段，需要检验字段中的值是否为DBNull.Value.
string code = (rs.Fields["CountryRegionCode"].Value == DBNull.Value) ?
"(DBNull)" : rs.Fields["CountryRegionCode"].Value.ToString();
string name = (rs.Fields["Name"].Value == DBNull.Value) ?
"(DBNull)" : rs.Fields["Name"].Value.ToString();
DateTime modifiedDate = (rs.Fields["ModifiedDate"].Value == DBNull.Value) ?
DateTime.MinValue : (DateTime)rs.Fields["ModifiedDate"].Value;
Console.WriteLine(" {2} \t{0}\t{1}", code, name, modifiedDate.ToString("yyyy-MM-dd"));
// 移动到下一条记录
rs.MoveNext();
}
}
catch (Exception ex)
{
Console.WriteLine("应用程序出现错误: {0}", ex.Message);
if (ex.InnerException != null)
Console.WriteLine("描述: {0}", ex.InnerException.Message);
}
finally
{
////////////////////////////////////////////////////////////////////////////////
// 退出前清理对象.
//
Console.WriteLine("正在关闭连接 ...");
// 关闭record set，当它处于打开状态时
if (rs != null && rs.State == (int)ADODB.ObjectStateEnum.adStateOpen)
rs.Close();
// 关闭数据库连接，当它处于打开状态时
if (conn != null && conn.State == (int)ADODB.ObjectStateEnum.adStateOpen)
conn.Close();
}
}
//式样号信息更新或者委托信息发送。
private void PRINT_Click(string plsqlPackageName, ArrayList list, string informationText)
{
GeneralCommon.GStatusBar.Panels[0].Text = "提示信息：操作未能成功...!!";
string ret_Result_ErrMsg;
//int ret_Result_ErrCode = 0;
string ret_Result_ErrCode;
ADODB.Command adoCmd;
try
{
if (GeneralCommon.M_CN1.State == 0)
{
if (GeneralCommon.GF_DbConnect() == false)
{
return;
}
}
Cursor.Current = Cursors.WaitCursor;
GeneralCommon.M_CN1.CursorLocation = ADODB.CursorLocationEnum.adUseServer;
adoCmd = new ADODB.Command();
adoCmd.ActiveConnection = GeneralCommon.M_CN1;
adoCmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc;
adoCmd.CommandText = plsqlPackageName;
GeneralCommon.M_CN1.BeginTrans();
for (int i = 1; i <= list.Count; i++)
{
adoCmd.Parameters.Append(adoCmd.CreateParameter("", ADODB.DataTypeEnum.adVariant, ADODB.ParameterDirectionEnum.adParamInput, 0, null));
}
object value = null;
adoCmd.Parameters.Append(adoCmd.CreateParameter("arg_e_code", ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamOutput, 1, value));
adoCmd.Parameters.Append(adoCmd.CreateParameter("arg_e_msg", ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamOutput, 256, value));
for (int i = 0; i <= list.Count - 1; i++)
{
adoCmd.Parameters[i].Value = list[i].ToString() == "" ? "" : list[i].ToString();
}
adoCmd.Execute(out value);
if (plsqlPackageName == "AQC1061P")
{
if (adoCmd.Parameters["arg_e_code"].Value.ToString() != "YY")
{
//ret_Result_ErrCode = System.Convert.ToInt32(adoCmd.Parameters["arg_e_code"].Value);
ret_Result_ErrCode = (string)(adoCmd.Parameters["arg_e_code"].Value);
ret_Result_ErrMsg = (string)(adoCmd.Parameters["arg_e_msg"].Value);
GeneralCommon.sErrMessg = (string)("Error Code : " + ret_Result_ErrCode + "\r\n" + "Error Mesg : " + ret_Result_ErrMsg);
GeneralCommon.Gp_MsgBoxDisplay(GeneralCommon.sErrMessg, "W", this.Text);
Cursor.Current = Cursors.Default;
adoCmd = null;
GeneralCommon.M_CN1.RollbackTrans();
}
else
{
GeneralCommon.M_CN1.CommitTrans();
GeneralCommon.M_CN1.Close();
GeneralCommon.GStatusBar.Panels[0].Text = informationText;
adoCmd = null;
Cursor.Current = Cursors.Default;
}
}
else
{
if (adoCmd.Parameters["arg_e_code"].Value.ToString() != "0")
{
//ret_Result_ErrCode = System.Convert.ToInt32(adoCmd.Parameters["arg_e_code"].Value);
ret_Result_ErrCode = (string)(adoCmd.Parameters["arg_e_code"].Value);
ret_Result_ErrMsg = (string)(adoCmd.Parameters["arg_e_msg"].Value);
GeneralCommon.sErrMessg = (string)("Error Code : " + ret_Result_ErrCode + "\r\n" + "Error Mesg : " + ret_Result_ErrMsg);
GeneralCommon.Gp_MsgBoxDisplay(GeneralCommon.sErrMessg, "W", this.Text);
Cursor.Current = Cursors.Default;
adoCmd = null;
GeneralCommon.M_CN1.RollbackTrans();
}
else
{
GeneralCommon.M_CN1.CommitTrans();
GeneralCommon.M_CN1.Close();
GeneralCommon.GStatusBar.Panels[0].Text = informationText;
adoCmd = null;
Cursor.Current = Cursors.Default;
}
}
}
catch (Exception ex)
{
adoCmd = null;
GeneralCommon.M_CN1.RollbackTrans();
Cursor.Current = Cursors.Default;
GeneralCommon.Gp_MsgBoxDisplay((string)("Gf_ExecProcedure Error : " + ex.Message), "W", "警告");
}
}
/// <summary>
/// Creates a recordset object, creates a command object and opens the recordset
/// with the command.
/// </summary>
/// <param name="oCmd"></param>
private void OpenRecordset(ADODB.Command oCmd)
{
oRs = new ADODB.RecordsetClass();
oRs.GetType().InvokeMember("Open", BindingFlags.InvokeMethod, null, oRs, new object[] { oCmd });
}
static void Main(string[] args)
{
ADODB.Connection conn = null;
ADODB.Recordset rs = null;
try
{
////////////////////////////////////////////////////////////////////////////////
// 连接数据源.
//
Console.WriteLine("正在连接数据库 ...");
// 获取连接字符串
string connStr = string.Format("Provider=SQLOLEDB;Data Source={0};Initial Catalog={1};Integrated Security=SSPI",
".\\sqlexpress", "SQLServer2005DB");
// 打开连接
conn = new ADODB.Connection();
conn.Open(connStr, null, null, 0);
////////////////////////////////////////////////////////////////////////////////
// 编写并执行ADO命令.
// 可以是SQL指令(SELECT/UPDATE/INSERT/DELETE),或是调用存储过程.
// 此处是一个INSERT命令示例.
//
Console.WriteLine("将一条记录插入表CountryRegion中...");
// 1. 生成一个Command对象
ADODB.Command cmdInsert = new ADODB.Command();
// 2. 将连接赋值于命令
cmdInsert.ActiveConnection = conn;
// 3. 设置命令文本
// SQL指令或者存储过程名
cmdInsert.CommandText = "INSERT INTO CountryRegion(CountryRegionCode, Name, ModifiedDate)"
+ " VALUES (?, ?, ?)";
// 4. 设置命令类型
// ADODB.CommandTypeEnum.adCmdText 用于普通的SQL指令;
// ADODB.CommandTypeEnum.adCmdStoredProc 用于存储过程.
cmdInsert.CommandType = ADODB.CommandTypeEnum.adCmdText;
// 5. 添加参数
// CountryRegionCode (nvarchar(20)参数的添加
ADODB.Parameter paramCode = cmdInsert.CreateParameter(
"CountryRegionCode", // 参数名
ADODB.DataTypeEnum.adVarChar, // 参数类型 (nvarchar(20))
ADODB.ParameterDirectionEnum.adParamInput, // 参数类型
20, // 参数的最大长度
"ZZ" + DateTime.Now.Millisecond); // 参数值
cmdInsert.Parameters.Append(paramCode);
// Name (nvarchar(200))参数的添加
ADODB.Parameter paramName = cmdInsert.CreateParameter(
"Name", // 参数名
ADODB.DataTypeEnum.adVarChar, // 参数类型 (nvarchar(200))
ADODB.ParameterDirectionEnum.adParamInput, // 参数传递方向
200, // 参数的最大长度
"Test Region Name"); // 参数值
cmdInsert.Parameters.Append(paramName);
// ModifiedDate (datetime)参数的添加
ADODB.Parameter paramModifiedDate = cmdInsert.CreateParameter(
"ModifiedDate", // 参数名
ADODB.DataTypeEnum.adDate, // 参数类型 (datetime)
ADODB.ParameterDirectionEnum.adParamInput, // 参数传递方向
-1, // 参数的最大长度 (datetime忽视该值)
DateTime.Now); // 参数值
cmdInsert.Parameters.Append(paramModifiedDate);
// 6. 执行命令
object nRecordsAffected = Type.Missing;
object oParams = Type.Missing;
cmdInsert.Execute(out nRecordsAffected, ref oParams,
(int)ADODB.ExecuteOptionEnum.adExecuteNoRecords);
////////////////////////////////////////////////////////////////////////////////
// 使用Recordset对象.
// http://msdn.microsoft.com/en-us/library/ms681510.aspx
// Recordset表示了数据表中记录或执行命令获得的结果的集合。
// 在任何时候， Recordset对象都指向集合中的单条记录，并将
// 该记录作为它的当前记录。
//
Console.WriteLine("列出表CountryRegion中的所有记录");
// 1. 生成Recordset对象
rs = new ADODB.Recordset();
// 2. 打开Recordset对象
string strSelectCmd = "SELECT * FROM CountryRegion"; // WHERE ...
rs.Open(strSelectCmd, // SQL指令/表,视图名 /
// 存储过程调用 /文件名
conn, // 连接对象/连接字符串
ADODB.CursorTypeEnum.adOpenForwardOnly, // 游标类型. (只进游标)
ADODB.LockTypeEnum.adLockOptimistic, // 锁定类型. (仅当需要调用
// 更新方法时，才锁定记录）
(int)ADODB.CommandTypeEnum.adCmdText); // 将第一个参数视为SQL命令
// 或存储过程.
// 3. 通过向前移动游标列举记录
// 移动到Recordset中的第一条记录
rs.MoveFirst();
while (!rs.EOF)
{
// 当在表中定义了一个可空字段，需要检验字段中的值是否为DBNull.Value.
string code = (rs.Fields["CountryRegionCode"].Value == DBNull.Value) ?
"(DBNull)" : rs.Fields["CountryRegionCode"].Value.ToString();
string name = (rs.Fields["Name"].Value == DBNull.Value) ?
"(DBNull)" : rs.Fields["Name"].Value.ToString();
DateTime modifiedDate = (rs.Fields["ModifiedDate"].Value == DBNull.Value) ?
DateTime.MinValue : (DateTime)rs.Fields["ModifiedDate"].Value;
Console.WriteLine(" {2} \t{0}\t{1}", code, name, modifiedDate.ToString("yyyy-MM-dd"));
// 移动到下一条记录
rs.MoveNext();
}
}
catch (Exception ex)
{
Console.WriteLine("应用程序出现错误: {0}", ex.Message);
if (ex.InnerException != null)
{
Console.WriteLine("描述: {0}", ex.InnerException.Message);
}
}
finally
{
////////////////////////////////////////////////////////////////////////////////
// 退出前清理对象.
//
Console.WriteLine("正在关闭连接 ...");
// 关闭record set，当它处于打开状态时
if (rs != null && rs.State == (int)ADODB.ObjectStateEnum.adStateOpen)
{
rs.Close();
}
// 关闭数据库连接，当它处于打开状态时
if (conn != null && conn.State == (int)ADODB.ObjectStateEnum.adStateOpen)
{
conn.Close();
}
}
}
private string Gp_LOC_Exec(string sAddrText, string cbocurText)
{
// string sQuery = "{call WGA1040C.P_LOC_TUN ('" + cbocurText + "','" + sAddrText + "',?)}";
string ret_Result_ErrMsg;
int ret_Result_ErrCode = 0;
ADODB.Command adoCmd;
try
{
if (GeneralCommon.M_CN1.State == 0)
{
if (GeneralCommon.GF_DbConnect() == false)
{
return("ERROR");
}
}
Cursor.Current = Cursors.WaitCursor;
GeneralCommon.M_CN1.CursorLocation = ADODB.CursorLocationEnum.adUseServer;
adoCmd = new ADODB.Command();
adoCmd.ActiveConnection = GeneralCommon.M_CN1;
adoCmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc;
adoCmd.CommandText = "WGA1010C.P_LOC_TUN";
GeneralCommon.M_CN1.BeginTrans();
for (int i = 1; i <= 2; i++)
{
adoCmd.Parameters.Append(adoCmd.CreateParameter("", ADODB.DataTypeEnum.adVariant, ADODB.ParameterDirectionEnum.adParamInput, 0, null));
}
object value = null;
adoCmd.Parameters.Append(adoCmd.CreateParameter("arg_e_msg", ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamOutput, 256, value));
adoCmd.Parameters[0].Value = cbocurText;
adoCmd.Parameters[1].Value = sAddrText;
adoCmd.Execute(out value);
if (adoCmd.Parameters["arg_e_msg"].Value.ToString() == "")
{
GeneralCommon.M_CN1.CommitTrans();
GeneralCommon.M_CN1.Close();
adoCmd = null;
Cursor.Current = Cursors.Default;
return("");
}
else
{
Cursor.Current = Cursors.Default;
adoCmd = null;
GeneralCommon.M_CN1.RollbackTrans();
// return "ERROR";
}
return("ERROR");
}
catch (Exception ex)
{
adoCmd = null;
GeneralCommon.M_CN1.RollbackTrans();
Cursor.Current = Cursors.Default;
GeneralCommon.Gp_MsgBoxDisplay((string)(ex.Message), "W", "警告");
return(ex.Message + "ERROR");
}
}
public static ObservableCollection <Extended_Container> GetExtended_Containers()
{
ObservableCollection <Extended_Container> _extContainers;
ADODB.Connection BLConnection = new Connections().BLConnection;
//Connect
BLConnection.Open();
//Retrieve
ADODB.Command retrieveCommand = new ADODB.Command();
retrieveCommand.ActiveConnection = BLConnection;
retrieveCommand.CommandText = $@"
SELECT [refnum]
,[seqnum]
,[itmnum]
,[ccrnum]
,[ccrtyp]
,[chargetyp]
,[descr]
,[docrefno]
,[entnum]
,[regnum]
,[cntnum]
,[cntsze]
,[fulemp]
,[amt]
,[vatamt]
,[wtax]
,[vatcde]
,[stostat]
,[lngth]
,[width]
,[height]
,[ums]
,[quantity]
,[dgrcls]
,[dgramt]
,[revton]
,[ovzamt]
,[enrfrdttm]
,[enstodttm]
,[stordys]
,[rfrhrs]
,[remark]
,[guarntycde]
,[status]
,[shplin]
,[vslcde]
,[pod]
,[userid]
,[sysdttm]
,[updcde]
,[outdttm]
,[IsN4ReeferPaymentUpdated]
,[CompanyCode]
FROM [billing].[dbo].[CCRdtl] where status <> 'CAN' and (chargetyp like '%MCRFC%' or chargetyp like '%STOI%' or chargetyp like '%STOE%')
";
System.Data.DataTable _EXTContainersTable = new System.Data.DataTable();
System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter();
adapter.Fill(_EXTContainersTable, retrieveCommand.Execute(out object dsadsad, 0, 0));
//Convert datatable to observable collection
_extContainers = Generate(_EXTContainersTable);
//return
return(_extContainers);
}
private void ExportResults()
{
//SS:01/03/2018:2018-R1:ABSEXCH-19796: When Running the ExchDVT.exe, SQL Admin Passwords are visible in dump file.
ADODB.Connection conn = new ADODB.Connection();
ADODB.Command cmd = new ADODB.Command();
if (CompanyCode == null)
{
cmd.CommandText = "SELECT [IntegrityErrorNo]" +
", [IntegrityErrorCode]" +
", [Severity]" +
", [IntegrityErrorMessage]" +
", [IntegritySummaryDescription]" +
", [SchemaName]" +
", [TableName]" +
", [PositionId]" +
"FROM [common].[SQLDataValidation] ";
}
else
{
cmd.CommandText = "SELECT [IntegrityErrorNo]" +
", [IntegrityErrorCode]" +
", [Severity]" +
", [IntegrityErrorMessage]" +
", [IntegritySummaryDescription]" +
", [SchemaName]" +
", [TableName]" +
", [PositionId] " +
"FROM [common].[SQLDataValidation] " +
"WHERE SchemaName = '" + CompanyCode + "'";
}
cmd.CommandTimeout = 10000;
if (conn.State == 0)
{
if (ConnPassword.Trim() == "")
{
conn.Open();
}
else
{
conn.Open(ExchequerCommonSQLConnection, "", ConnPassword.Trim(),
(int)ADODB.ConnectModeEnum.adModeUnknown);
}
}
conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
System.Data.DataTable dataTable = new System.Data.DataTable {
TableName = "resultSet"
};
try
{
cmd.CommandType = ADODB.CommandTypeEnum.adCmdText;
cmd.ActiveConnection = conn;
ADODB.Recordset recordSet = null;
object objRecAff;
recordSet = (ADODB.Recordset)cmd.Execute(out objRecAff, Type.Missing, (int)ADODB.CommandTypeEnum.adCmdText);
System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter();
adapter.Fill(dataTable, recordSet);
if (conn.State == 1)
{
conn.Close();
}
}
catch
{
throw;
}
Type officeType = Type.GetTypeFromProgID("Excel.Application");
if (officeType == null)
{
//no Excel installed
SaveFileDialog.Filter = "XML File | *.xml";
SaveFileDialog.InitialDirectory = ExchequerPath + "\\Logs";
SaveFileDialog.DefaultExt = "XML";
SaveFileDialog.OverwritePrompt = true;
SaveFileDialog.FileName = "ExchSQLDataValidationResults";
if (CompanyCode != null)
{
SaveFileDialog.FileName = SaveFileDialog.FileName + "-" + CompanyCode;
}
SaveFileDialog.ShowDialog();
this.UseWaitCursor = true;
txtCompanyName.Enabled = false;
txtContactName.Enabled = false;
txtEmailAddress.Enabled = false;
btnSaveResults.Enabled = false;
System.Windows.Forms.Application.DoEvents();
XmlTextWriter writer = new XmlTextWriter(@SaveFileDialog.FileName, null);
writer.WriteStartDocument(true);
writer.Formatting = Formatting.Indented;
writer.WriteStartElement("SQLDataValidationResults");
writer.WriteStartElement("CompanyName");
writer.WriteString(txtCompanyName.Text);
writer.WriteEndElement();
writer.WriteStartElement("ContactName");
writer.WriteString(txtContactName.Text);
writer.WriteEndElement();
writer.WriteStartElement("EmailAddress");
writer.WriteString(txtEmailAddress.Text);
writer.WriteEndElement();
writer.WriteStartElement("VersionInfo");
writer.WriteString(VersionInfo);
writer.WriteEndElement();
dataTable.WriteXml(writer);
writer.WriteEndDocument();
writer.Close();
}
else
{
//Excel installed
SaveFileDialog.Filter = "Excel File | *.xlsx";
SaveFileDialog.InitialDirectory = ExchequerPath + "\\Logs";
SaveFileDialog.DefaultExt = "XLSX";
SaveFileDialog.OverwritePrompt = true;
SaveFileDialog.FileName = "ExchSQLDataValidationResults";
if (CompanyCode != null)
{
SaveFileDialog.FileName = SaveFileDialog.FileName + "-" + CompanyCode;
}
SaveFileDialog.ShowDialog();
this.UseWaitCursor = true;
txtCompanyName.Enabled = false;
txtContactName.Enabled = false;
txtEmailAddress.Enabled = false;
btnSaveResults.Enabled = false;
System.Windows.Forms.Application.DoEvents();
clsExcelUtlity obj = new Data_Integrity_Checker.clsExcelUtlity();
obj.WriteDataTableToExcel(dataTable, "ExchSQL Data Validation Results", SaveFileDialog.FileName, txtCompanyName.Text, txtContactName.Text, txtEmailAddress.Text, VersionInfo);
}
this.UseWaitCursor = false;
dataTable = null;
Close();
}
public void ProfitAndLossBroughtForward(string ExchequerCommonSQLConnection, string CompanyCode, bool Commitment, string connPassword)
{
string CommitmentEnabled = "0";
if (Commitment == true)
{
CommitmentEnabled = "1";
}
ADODB.Connection conn = new ADODB.Connection();
ADODB.Command cmd = new ADODB.Command();
cmd.CommandText = " DECLARE @MinYear INT" +
" , @MaxYear INT" +
" , @iv_IsCommitment INT = " + CommitmentEnabled +
" , @c_CTDPeriod INT = 255" +
" , @c_False BIT = 0" +
" IF OBJECT_ID('tempdb..#ProfitBF') IS NOT NULL" +
" DROP TABLE #ProfitBF" +
" DECLARE @ProfitBF INT" +
" , @NominalType VARCHAR(1)" +
" , @HistoryCode VARBINARY(21)" +
" SELECT @ProfitBF = SS.NomCtrlProfitBF" +
" , @NominalType = N.NominalTypeCode" +
" , @HistoryCode = HC.HistoryCode" +
" FROM " + CompanyCode + ".EXCHQSS SS" +
" JOIN " + CompanyCode + ".evw_Nominal N ON SS.NomCtrlProfitBF = N.NominalCode" +
" CROSS APPLY(SELECT HistoryCode = [common].efn_CreateNominalHistoryCode(SS.NomCtrlProfitBF, NULL, NULL, NULL, @iv_IsCommitment)" +
" ) HC" +
" WHERE common.GetString(IDCode, 1) = 'SYS' " +
// Gather qualifying data
" SELECT HistoryClassificationId = ASCII(@NominalType)" +
" , HistoryCode = @HistoryCode" +
" , NominalCode = @ProfitBF" +
" , HistoryYear = NH.HistoryYear - 1900" +
" , NH.CurrencyId" +
" , SalesAmount = SUM(NH.SalesAmount)" +
" , PurchaseAmount = SUM(NH.PurchaseAmount)" +
" INTO #ProfitBF" +
" FROM " + CompanyCode + ".evw_NominalHistory NH(READUNCOMMITTED)" +
" JOIN " + CompanyCode + ".evw_Nominal N ON NH.NominalCode = N.NominalCode" +
" WHERE NH.HistoryClassificationId = 65" +
" AND NH.HistoryPeriod < 250" +
" AND NH.CostCentreDepartmentFlag IS NULL" +
" AND NH.IsCommitment = @iv_IsCommitment" +
" GROUP BY NH.HistoryYear" +
" , NH.CurrencyId" +
// Add in any direct bookings to the ProfitBF nominal
" MERGE #ProfitBF PBF" +
" USING(SELECT HistoryClassificationId = ASCII(@NominalType)" +
" , HistoryCode = @HistoryCode" +
" , NominalCode = @ProfitBF" +
" , HistoryYear = NH.HistoryYear - 1900" +
" , CurrencyId" +
" , SalesAmount = SUM(SalesAmount)" +
" , PurchaseAmount = SUM(PurchaseAmount)" +
" FROM " + CompanyCode + ".evw_NominalHistory NH(READUNCOMMITTED)" +
" WHERE NH.HistoryClassificationId = 67" +
" AND NH.HistoryCode = @HistoryCode" +
" AND NH.HistoryPeriod < 250" +
" GROUP BY HistoryYear" +
" , CurrencyId" +
" ) NewData ON NewData.HistoryCode = PBF.HistoryCode" +
" AND NewData.HistoryYear = PBF.HistoryYear" +
" AND NewData.CurrencyId = PBF.CurrencyId" +
" WHEN MATCHED THEN" +
" UPDATE" +
" SET SalesAmount = PBF.SalesAmount + NewData.SalesAmount" +
" , PurchaseAmount = PBF.PurchaseAmount + NewData.PurchaseAmount" +
" WHEN NOT MATCHED BY TARGET THEN" +
" INSERT(HistoryClassificationId, HistoryCode, NominalCode, HistoryYear, CurrencyId, SalesAmount, PurchaseAmount)" +
" VALUES(NewData.HistoryClassificationId, NewData.HistoryCode, NewData.NominalCode, NewData.HistoryYear, NewData.CurrencyId, NewData.SalesAmount, NewData.PurchaseAmount)" +
" ;" +
" SELECT @MinYear = MIN(HistoryYear), @MaxYear = MAX(HistoryYear)" +
" FROM #ProfitBF" +
// Fills any gaps in ProfitBF years" +
" INSERT INTO #ProfitBF" +
" SELECT P1.HistoryClassificationId, P1.HistoryCode, P1.NominalCode, EY.ExchequerYear, P1.CurrencyId, 0, 0" +
" FROM #ProfitBF P1" +
" OUTER APPLY(SELECT DISTINCT ExchequerYear" +
" FROM " + CompanyCode + ".evw_Period P" +
" WHERE P.ExchequerYear BETWEEN @MinYear AND @MaxYear" +
" ) EY" +
" WHERE P1.HistoryYear <= EY.ExchequerYear" +
" AND NOT EXISTS(SELECT TOP 1 1" +
" FROM #ProfitBF P2" +
" WHERE P1.HistoryClassificationId = P2.HistoryClassificationId" +
" AND P1.HistoryCode = P2.HistoryCode" +
" AND P1.CurrencyId = P2.CurrencyId" +
" AND EY.ExchequerYear = P2.HistoryYear" +
" )" +
// Inital Result set removed //
// The following finally checks that the ProfitBF hierarchy adds up
" DECLARE @TopLevelNominalCode INT" +
" SELECT @TopLevelNominalCode = N.NominalCode" +
" FROM " + CompanyCode + ".evw_NominalAscendant NA" +
" JOIN " + CompanyCode + ".evw_Nominal N ON NA.AscendantNominalCode = N.NominalCode" +
" JOIN " + CompanyCode + ".evw_NominalHierarchy NHIER ON N.NominalCode = NHIER.NominalCode" +
" WHERE NHIER.NominalLevel = 0" +
" AND NA.NominalCode = @ProfitBF" +
" IF OBJECT_ID('tempdb..#PBFHierarchyChildren') IS NOT NULL" +
" DROP TABLE #PBFHierarchyChildren" +
// Put the children of the Profit BF hierarchy into a temp.table" +
" SELECT NominalCode = N.NominalCode" +
" , HC.HistoryCode" +
" INTO #PBFHierarchyChildren" +
" FROM " + CompanyCode + ".evw_NominalDescendant ND" +
" JOIN " + CompanyCode + ".evw_Nominal N ON ND.DescendantNominalCode = N.NominalCode" +
" CROSS APPLY(SELECT HistoryCode = [common].efn_CreateNominalHistoryCode(N.NominalCode, NULL, NULL, NULL, @iv_IsCommitment)" +
" ) HC" +
" WHERE ND.NominalCode = @TopLevelNominalCode" +
" AND CONVERT(BIT, ISNULL((SELECT TOP 1 1 " +
" FROM " + CompanyCode + ".NOMINAL C " +
" WHERE N.NominalCode = C.glParent), 0)) = @c_False" +
" IF OBJECT_ID('tempdb..#PBFRawData') IS NOT NULL" +
" DROP TABLE #PBFRawData" +
" SELECT PBFHC.NominalCode, H.HistoryPeriodKey, H.ExchequerYear, HistoryPeriod, CurrencyId, SalesAmount, PurchaseAmount" +
" INTO #PBFRawData" +
" FROM #PBFHierarchyChildren PBFHC" +
" JOIN " + CompanyCode + ".evw_NominalHistory H(READUNCOMMITTED) ON PBFHC.HistoryCode = H.HistoryCode" +
" AND H.HistoryPeriod < 250" +
" WHERE PBFHC.NominalCode <> @ProfitBF" +
" AND(H.SalesAmount <> 0 OR H.PurchaseAmount <> 0)" +
" UNION" +
" SELECT NominalCode" +
" , HistoryPeriodKey = ((HistoryYear + 1900) * 1000) + 249" +
" , HistoryYear" +
" , HistoryPeriod = 249" +
" , CurrencyId" +
" , SalesAmount" +
" , PurchaseAmount" +
" FROM #ProfitBF" +
" ORDER BY NominalCode DESC" +
" IF OBJECT_ID('tempdb..#PBFH') IS NOT NULL" +
" DROP TABLE #PBFH" +
" SELECT NA.NominalCode, AscendantNominalCode" +
" INTO #PBFH" +
" FROM " + CompanyCode + ".evw_NominalAscendant NA" +
" JOIN #PBFHierarchyChildren PBFHC ON NA.NominalCode = PBFHC.NominalCode" +
" IF OBJECT_ID('tempdb..#PBFHierarchyData') IS NOT NULL" +
" DROP TABLE #PBFHierarchyData" +
" SELECT HistoryClassificationId = CONVERT(INT, NULL)" +
" , NominalCode = P1.AscendantNominalCode" +
" , HistoryCode = CONVERT(VARBINARY(21), NULL)" +
" , PBF.ExchequerYear" +
" , HistoryPeriodKey" +
" , CurrencyId" +
" , SalesAmount" +
" , PurchaseAmount" +
" INTO #PBFHierarchyData" +
" FROM #PBFRawData PBF" +
" JOIN #PBFH P1 ON PBF.NominalCode = P1.NominalCode" +
" UPDATE PBF" +
" SET HistoryCode = [common].efn_CreateNominalHistoryCode(PBF.NominalCode, NULL, NULL, NULL, @iv_IsCommitment)" +
" , HistoryClassificationId = ASCII(N.NominalTypeCode)" +
" FROM #PBFHierarchyData PBF" +
" JOIN " + CompanyCode + ".evw_Nominal N ON PBF.NominalCode = N.NominalCode" +
" INSERT INTO common.SQLDataValidation " +
" SELECT IntegrityErrorNo = -59000" +
" , IntegrityErrorCode = 'E_HPLBF001'" +
" , Severity = 'High'" +
" , IntegrityErrorMessage = 'GL Code: ' + convert(varchar(100), PBFData.NominalCode) + ' Period: ' + convert(varchar(3), PBFData.HistoryPeriod) + ' Year: '" +
" + convert(varchar(4), (convert(int, PBFData.ExchequerYear) + 1900)) + ' Sales Difference: '" +
" + convert(varchar(100), ISNULL(convert(int, H.hiSales - PBFData.SalesAmount), 0)) + ' Purchase Difference: ' +" +
" +convert(varchar(100), ISNULL(convert(int, H.hiPurchases - PBFData.PurchaseAmount), 0))" +
" , IntegritySummaryDescription = 'Errors found in Profit and Loss Brought Forward'" +
" , SchemaName = '" + CompanyCode + "'" +
" , TableName = 'HISTORY'" +
" , PositionId = 0" +
" FROM (" +
" SELECT NominalCode, HistoryClassificationId, HistoryCode, ExchequerYear, HistoryPeriod = @c_CTDPeriod, CurrencyId, SalesAmount, PurchaseAmount" +
" FROM ( SELECT DISTINCT NominalCode, HistoryClassificationId, HistoryCode, ExchequerYear, CurrencyId" +
" FROM #PBFHierarchyData" +
" ) PBF" +
" CROSS APPLY ( SELECT SalesAmount = SUM(SalesAmount)" +
" , PurchaseAmount = SUM(PurchaseAmount)" +
" FROM #PBFHierarchyData PBFFin" +
" WHERE PBF.HistoryCode = PBFFin.HistoryCode" +
" AND PBF.CurrencyId = PBFFin.CurrencyId" +
" AND (((PBF.ExchequerYear + 1900) * 1000) + 255) >= PBFFin.HistoryPeriodKey" +
" ) Fin" +
" ) PBFData" +
" LEFT JOIN " + CompanyCode + ".HISTORY H ON H.hiCode = PBFData.HistoryCode" +
" AND H.HiExCLass = PBFData.HistoryClassificationId" +
" AND H.hiCurrency = PBFData.CurrencyId" +
" AND H.hiYear = PBFData.ExchequerYear" +
" AND H.hiPeriod = PBFData.HistoryPeriod" +
" WHERE /*H.PositionId IS NULL OR*/ ROUND(H.hiSales, 2) <> ROUND(PBFData.SalesAmount, 2)" +
" OR ROUND(H.hiPurchases, 2) <> ROUND(PBFData.PurchaseAmount, 2)";
cmd.CommandTimeout = 10000;
if (conn.State == 0)
{
if (connPassword.Trim() == "")
{
conn.Open();
}
else
{
conn.Open(ExchequerCommonSQLConnection, "", connPassword.Trim(),
(int)ADODB.ConnectModeEnum.adModeUnknown);
}
}
conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
try
{
Object recAff;
cmd.ActiveConnection = conn;
cmd.CommandType = ADODB.CommandTypeEnum.adCmdText;
cmd.Execute(out recAff, Type.Missing, (int)ADODB.CommandTypeEnum.adCmdText);
if (conn.State == 1)
{
conn.Close();
}
}
catch
{
throw;
}
}