public static DataObject ExecuteReaderCommand(DBConnections objDBConnection, string CommandString)
{
SqlDataReader dataresult = null;
try
{
SqlConnection Connection = new SqlConnection(GetConnectionString(objDBConnection));
SqlCommand Command = new SqlCommand();
Command.Connection = Connection;
Command.CommandType = CommandType.Text;
Command.CommandText = CommandString;
Connection.Open();
dataresult = Command.ExecuteReader(CommandBehavior.CloseConnection);
DataObject dataResult = new DataObject(dataresult);
return(dataResult);
}
catch (Exception e)
{
throw e;
}
}
public static DataObject ExecuteReaderCommand(DBConnections objDBConnection, string StoredProcedure, Hashtable ht)
{
SqlDataReader dataresult = null;
try
{
SqlConnection Connection = new SqlConnection(GetConnectionString(objDBConnection));
SqlCommand Command = new SqlCommand();
Command.Connection = Connection;
Command.CommandType = CommandType.Text;
Command.CommandText = StoredProcedure;
Command = AddParameters(Command, ht, false);
Connection.Open();
dataresult = Command.ExecuteReader(CommandBehavior.CloseConnection);
DataObject dataResult = new DataObject(dataresult);
return(dataResult);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
///
/// </summary>
public static void Clear()
{
if (DBConnections != null)
{
DBConnections.Clear();
}
DBConnections = new Dictionary <string, DBConnection>();
}
public override void DictDeserialize(IDictionary <string, object> docu, Scenario scenario = Scenario.Database)
{
base.DictDeserialize(docu);
var doc = docu as FreeDocument;
if (doc.Children != null)
{
var items = doc.Children;
foreach (var item in items)
{
var proces = new ProcessTask();
proces.Project = this;
proces.DictDeserialize(item);
Tasks.Add(proces);
}
}
if (docu["DBConnections"] != null)
{
var items = docu["DBConnections"] as FreeDocument;
if (items?.Children != null)
{
foreach (var item in items.Children)
{
var type = item["TypeName"].ToString();
var conn = PluginProvider.GetObjectByType <IDataBaseConnector>(type) as DBConnectorBase;
if (conn == null)
{
continue;
}
conn.DictDeserialize(item);
DBConnections.Add(conn);
}
}
}
if (DBConnections.FirstOrDefault(d => d.TypeName == "文件管理") == null)
{
var filemanager = new FileManager()
{
Name = "最近打开的文件"
};
DBConnections.Add(filemanager);
}
if (DBConnections.FirstOrDefault(d => d.TypeName == "MongoDB") == null)
{
var mongo = new MongoDBConnector()
{
Name = "MongoDB连接器"
};
mongo.DBName = "hawk";
DBConnections.Add(mongo);
}
}
public IList <ClaimOrder> Find(string suppilerNo, string claimNo)
{
IList <ClaimOrder> claimOrders = new List <ClaimOrder>();
OleDbConnection conn = null;
try
{
conn = DBConnections.NewConnection();
conn.Open();
string sql =
"SELECT " +
"RTV, claimNo, storeNo, lotNo, supplierNo, supplierName, dept, " +
"qty, pcs, claimAmount, claimReason, decidedDate, arriveRTVDate, " +
"informDate, informDays, withdrawDate, gateOutDate, gateOutType, " +
"destoryInformDate, destoryType, informDateIfOver50Days, creationDate, oid " +
"FROM claim_order " +
"WHERE 1=1";
if (StringUtils.NotEmpty(suppilerNo))
{
sql = sql + " AND supplierNo = '" + suppilerNo + "'";
}
if (StringUtils.NotEmpty(claimNo))
{
sql = sql + " AND claimNo = '" + claimNo + "'";
}
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
claimOrders.Add(ReadClaimOrder(reader));
}
}
catch (Exception e)
{
MessageBox.Show("Message: " + e.Message + "\r\n" + "Stack Trace: " + e.StackTrace, "·ÃÎÊÊý¾Ý¿â´íÎó");
}
finally
{
if (conn != null)
{
conn.Close();
}
}
return(claimOrders);
}
public SCMap AddLayerSql(string connectionName, string query, LayerSqlOptions options = null)
{
options ??= new LayerSqlOptions();
var map = this.Map;
var layer = new VectorItemsLayer()
{
AllowEditItems = false,
EnableHighlighting = false,
EnableSelection = false
};
if (!string.IsNullOrWhiteSpace(options.Name))
{
layer.Name = options.Name;
}
if (!string.IsNullOrWhiteSpace(options.ShapeTitlesPattern))
{
layer.ShapeTitlesPattern = options.ShapeTitlesPattern;
layer.ShapeTitlesVisibility = VisibilityMode.Visible;
}
else
{
layer.ShapeTitlesVisibility = VisibilityMode.Hidden;
}
var connections = DBConnections.LoadConnections();
var connection = connections.FindConnection(connectionName);
if (connection == null)
{
throw new Exception($"Cannot find connection '{connectionName}'.");
}
if (!ConnectionFactory.IsMSSQLServer(connection.Provider))
{
throw new Exception("DBMS must be Microsoft SQL Server.");
}
var adapter = new SqlGeometryDataAdapter
{
ConnectionString = connection.ConnectionString,
SqlText = query,
SpatialDataMember = options.SpatialColumn
};
layer.Data = adapter;
map.Layers.Add(layer);
CurrentLayer = layer;
return(this);
}
// public int DeleteAll()
// {
// int deletedCnt = 0;
// OleDbConnection conn = null;
// try
// {
// conn = DBConnections.NewConnection();
// conn.Open();
//
// string sql =
// "delete from claim_order";
//
// OleDbCommand cmd = conn.CreateCommand();
// cmd.CommandText = sql;
//
// deletedCnt = cmd.ExecuteNonQuery();
// }
// catch (Exception e)
// {
// MessageBox.Show("Message: " + e.Message + "\r\n" + "Stack Trace: " + e.StackTrace, "·ÃÎÊÊý¾Ý¿â´íÎó");
// }
// finally
// {
// if (conn != null)
// {
// conn.Close();
// }
// }
//
// return deletedCnt;
// }
public IList <ClaimOrder> FindYesterdaysClaimOrders()
{
IList <ClaimOrder> claimOrders = new List <ClaimOrder>();
OleDbConnection conn = null;
try
{
conn = DBConnections.NewConnection();
conn.Open();
string sql =
"SELECT " +
"RTV, claimNo, storeNo, lotNo, supplierNo, supplierName, dept, " +
"qty, pcs, claimAmount, claimReason, decidedDate, arriveRTVDate, " +
"informDate, informDays, withdrawDate, gateOutDate, gateOutType, " +
"destoryInformDate, destoryType, informDateIfOver50Days, creationDate, oid " +
"FROM claim_order " +
"WHERE creationDate > @BeginningOfYesterday " +
" AND creationDate < @EndOfYesterday ";
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
OleDbParameter beginningOfYesterdayParam = new OleDbParameter("BeginningOfYesterday", OleDbType.Date);
beginningOfYesterdayParam.Value = DateTime.Today.Subtract(new TimeSpan(TimeSpan.TicksPerDay));
cmd.Parameters.Add(beginningOfYesterdayParam);
OleDbParameter endOfYesterdayParam = new OleDbParameter("EndOfYesterday", OleDbType.Date);
endOfYesterdayParam.Value = DateTime.Today.Subtract(new TimeSpan(TimeSpan.TicksPerSecond));
cmd.Parameters.Add(endOfYesterdayParam);
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
claimOrders.Add(ReadClaimOrder(reader));
}
}
catch (Exception e)
{
MessageBox.Show("Message: " + e.Message + "\r\n" + "Stack Trace: " + e.StackTrace, "·ÃÎÊÊý¾Ý¿â´íÎó");
}
finally
{
if (conn != null)
{
conn.Close();
}
}
return(claimOrders);
}
#pragma warning disable CA1822 // Mark members as static
public void SetDbConnection(string connectionName, DbProvider provider, string connectionString,
#pragma warning restore CA1822 // Mark members as static
string description = null, bool replace = false)
{
if (string.IsNullOrWhiteSpace(connectionName))
{
throw new Exception("ConnectionName cannot be empty.");
}
if (connectionName.Contains(':'))
{
throw new Exception("ConnectionName cannot contain colon (':') character.");
}
var connections = DBConnections.LoadConnections();
var connection = connections.FindConnection(connectionName);
if (connection != null)
{
if (replace)
{
connections.Connections.Remove(connection);
}
else
{
throw new Exception($"Connection '{connectionName}' already exists.");
}
}
var strProvider = provider switch
{
DbProvider.MsSqlServer => ConnectionFactory.SqlClientFactoryLightName,
DbProvider.MsSqlServerFull => ConnectionFactory.SqlClientFactoryName,
DbProvider.MySql => ConnectionFactory.MySqlFactoryName,
DbProvider.SQLite => ConnectionFactory.SQLiteFactoryName,
DbProvider.ODBC => ConnectionFactory.OdbcFactoryName,
_ => ConnectionFactory.SqlClientFactoryLightName
};
connection = new DBConnection()
{
Name = connectionName,
Provider = strProvider,
Description = description
};
connection.ConnectionString = connectionString;
connections.Connections.Add(connection);
DBConnections.SaveConnections(connections);
Messenger.Default.Send <ConnectionListChangedMessage>(new ConnectionListChangedMessage());
}
}
// GET: api/RoomsWithComputers
public IEnumerable <RoomsWithComputersModel> Get()
{
SqlConnection conn = DBConnections.GetConnection();
SqlCommand cmd;
SqlDataReader rdr;
String query;
List <RoomsWithComputersModel> output = new List <RoomsWithComputersModel>();
try
{
conn.Open();
query = "select * from Room r where concat(r.building, r.RoomNo) in (select concat(c.Building, c.RoomNo) from computer c)";
/*query = "select * " +
* "from Room " +
* "inner join Rooms on Room.RoomsComputer = RoomsComputer.roomNo " +
* "where Room.RoomsComputer is not null";*/
cmd = new SqlCommand(query, conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
output.Add(new RoomsWithComputersModel(rdr["Building"].ToString(),
Int32.Parse(rdr["RoomNo"].ToString()),
Int32.Parse(rdr["Capacity"].ToString())));
//Int32.Parse(rdr["number"].ToString()),
//Int32.Parse(rdr["assembledYear"].ToString()),
//rdr["cbuilding"].ToString(),
//Int32.Parse(rdr["cRoomNo"].ToString())));
}
}
catch (Exception e)
{
output.Clear();
throw e;
//output.Add(e.Message);
}
finally
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
}
return(output);
//return new string[] { "value1", "value2" };
}
//string outputForlder = "\\\\192.168.0.254\\captive\\Auto\\IslaBank\\Test";
public void PackingText(List <ChequeModel> _checksModel, frmMain _mainForm)
{
StreamWriter file;
DBConnections db = new DBConnections();
db.GetAllData(_checksModel, _mainForm.batchFile);
var listofcheck = _checksModel.Select(e => e.ChequeType).ToList();
foreach (string Scheck in listofcheck)
{
if (Scheck == "A")
{
string packkingListPath = outputForlder + "\\Regular_Checks\\PackingA.txt";
if (File.Exists(packkingListPath))
{
File.Delete(packkingListPath);
}
var checks = _checksModel.Where(a => a.ChequeType == Scheck).Distinct().ToList();
file = File.CreateText(packkingListPath);
file.Close();
using (file = new StreamWriter(File.Open(packkingListPath, FileMode.Append)))
{
string output = OutPutProcess.ConvertToPackingList(checks, "PERSONAL", _mainForm.batchFile);
file.WriteLine(output);
}
}
}
foreach (string Scheck in listofcheck)
{
if (Scheck == "B")
{
string packkingListPath = outputForlder + "\\Regular_Checks\\PackingB.txt";
if (File.Exists(packkingListPath))
{
File.Delete(packkingListPath);
}
var checks = _checksModel.Where(a => a.ChequeType == Scheck).Distinct().ToList();
file = File.CreateText(packkingListPath);
file.Close();
using (file = new StreamWriter(File.Open(packkingListPath, FileMode.Append)))
{
string output = OutPutProcess.ConvertToPackingList(checks, "COMMERCIAL", _mainForm.batchFile);
file.WriteLine(output);
}
}
}
}
public IList <ClaimOrder> FindOver10000YuanOver14DaysGroupBySupplier()
{
IList <ClaimOrder> claimOrders = new List <ClaimOrder>();
OleDbConnection conn = null;
try
{
conn = DBConnections.NewConnection();
conn.Open();
string sql =
"select t3.RTV, claimNo, storeNo, lotNo, t3.supplierNo, supplierName, dept, qty, pcs, claimAmount, claimReason, decidedDate, arriveRTVDate, informDate, informDays, withdrawDate, gateOutDate, gateOutType, destoryInformDate, destoryType, informDateIfOver50Days, creationDate, oid " +
" from (" +
" select supplierNo, rtv " +
" from claim_order t1 " +
" where t1.gateOutDate is NULL " +
" and t1.destoryInformDate is NULL " +
" and t1.informDays >= 14 " +
" group by t1.supplierNo, rtv" +
" having sum(t1.claimAmount) >= 10000) t2, " +
" claim_order t3 " +
"where t2.rtv = t3.rtv " +
" and t2.supplierNo = t3.supplierNo " +
" and t3.gateOutDate is NULL " +
" and t3.destoryInformDate is NULL " +
" and t3.informDays >= 14";
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
claimOrders.Add(ReadClaimOrder(reader));
}
}
catch (Exception e)
{
MessageBox.Show("Message: " + e.Message + "\r\n" + "Stack Trace: " + e.StackTrace, "·ÃÎÊÊý¾Ý¿â´íÎó");
}
finally
{
if (conn != null)
{
conn.Close();
}
}
return(claimOrders);
}
public override FreeDocument DictSerialize(Scenario scenario = Scenario.Database)
{
var dict = base.DictSerialize();
dict.Children = Tasks.Select(d => d.DictSerialize()).ToList();
var connecots = new FreeDocument
{
Children = DBConnections.Select(d => (d as IDictionarySerializable).DictSerialize()).ToList()
};
;
dict.Add("DBConnections", connecots);
return(dict);
}
protected override void UpdateMap()
{
var map = MapContext.Map;
var layer = new VectorItemsLayer()
{
AllowEditItems = false,
EnableHighlighting = false,
EnableSelection = false
};
if (!string.IsNullOrWhiteSpace(Name))
{
layer.Name = Name;
}
if (!string.IsNullOrWhiteSpace(ShapeTitlesPattern))
{
layer.ShapeTitlesPattern = ShapeTitlesPattern;
layer.ShapeTitlesVisibility = VisibilityMode.Visible;
}
else
{
layer.ShapeTitlesVisibility = VisibilityMode.Hidden;
}
var connections = DBConnections.LoadConnections();
var connection = connections.FindConnection(ConnectionName);
if (connection == null)
{
throw new Exception($"Cannot find connection '{ConnectionName}'.");
}
if (!ConnectionFactory.IsMSSQLServer(connection.Provider))
{
throw new Exception("DBMS must be Microsoft SQL Server.");
}
var adapter = new SqlGeometryDataAdapter
{
ConnectionString = connection.ConnectionString,
SqlText = Query,
SpatialDataMember = SpatialColumn
};
layer.Data = adapter;
map.Layers.Add(layer);
MapContext.CurrentLayer = layer;
}
private static string GetConnectionString(DBConnections ConnectionString)
{
string ConnStr = "";
switch (ConnectionString)
{
case DBConnections.PIS:
ConnStr = "Data Source=cibdbuser.db.11702767.hostedresource.com;Initial Catalog=cibdbuser;Persist Security Info=True;User ID=cibdbuser; pwd=CIBdb123!;";
break;
default:
break;
}
return(ConnStr);
}
public static void ExecuteNonQuery(DBConnections objDBConnection, string StoredProcedure, Hashtable ht, bool IsInjectionSafe)
{
SqlConnection Connection = new SqlConnection(GetConnectionString(objDBConnection));
SqlCommand Command = new SqlCommand();
Command.Connection = Connection;
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = StoredProcedure;
Command = AddParameters(Command, ht, IsInjectionSafe);
Connection.Open();
Command.ExecuteNonQuery();
Connection.Close();
}
// GET: api/RoomsUsed
public IEnumerable <RoomUsedModel> Get()
{
SqlConnection conn = DBConnections.GetConnection();
SqlCommand cmd;
SqlDataReader rdr;
String query;
List <RoomUsedModel> output = new List <RoomUsedModel>();
try
{
conn.Open();
query = "select * from Room r where concat(r.building, r.RoomNo) in (select concat(c.Building, c.RoomNo) from class c)";
cmd = new SqlCommand(query, conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
output.Add(new RoomUsedModel(rdr["Building"].ToString(),
Int32.Parse(rdr["RoomNo"].ToString()),
Int32.Parse(rdr["Capacity"].ToString())));
//rdr["classCode"].ToString(),
//rdr["name"].ToString(),
//rdr["cLbuilding"].ToString(),
//Int32.Parse(rdr["cLRoom"].ToString())));
}
}
catch (Exception e)
{
output.Clear();
throw e;
//output.Add(e.Message);
}
finally
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
}
return(output);
//return new string[] { "value1", "value2" };
}
#pragma warning disable CA1822 // Mark members as static
protected void ExecuteScript(Connection conn, string scriptText, ExportTableToDatabaseOptions options)
#pragma warning restore CA1822 // Mark members as static
{
if (string.IsNullOrWhiteSpace(scriptText))
{
return;
}
using var dataSet = new DataSet("Data");
var sqlScript = new SqlScript.SqlScript()
{
ScriptText = scriptText,
Connection = conn?.DbConnection,
ConnectionName = null,
DataSet = dataSet,
SCDispatcherService = null
};
if (options.CommandTimeout.HasValue)
{
sqlScript.ScriptParameters.CommandTimeout = options.CommandTimeout.Value;
}
var dbConnections = DBConnections.LoadConnections();
sqlScript.RequestConnection += (s, e) =>
{
var requestConnection = dbConnections.FindConnection(e.ConnectionName);
if (requestConnection == null)
{
return;
}
var requestConn = new Connection(requestConnection.Provider, requestConnection.ConnectionString);
if (requestConn.ConnectionType == null)
{
return;
}
requestConn.Open();
e.Connection = requestConn.DbConnection;
};
sqlScript.ExecuteScript();
}
protected override void ProcessRecord()
{
if (string.IsNullOrWhiteSpace(ConnectionName))
{
throw new Exception("ConnectionName cannot be empty.");
}
var connections = DBConnections.LoadConnections();
var connection = connections.FindConnection(ConnectionName) ??
throw new Exception($"Cannot find connection '{ConnectionName}'.");
var conn = new Connection(connection.Provider, connection.ConnectionString);
conn.Open();
var result = conn.DbConnection;
WriteObject(result);
}
public static Object ExecuteScalar(DBConnections objDBConnection, string StoredProcedure, Hashtable ht, bool IsInjectionSafe)
{
SqlConnection Connection = new SqlConnection(GetConnectionString(objDBConnection));
SqlCommand Command = new SqlCommand();
Command.Connection = Connection;
Command.CommandType = CommandType.StoredProcedure;
Command.CommandText = StoredProcedure;
Command = AddParameters(Command, ht, IsInjectionSafe);
Connection.Open();
Object objObject = Command.ExecuteScalar();
Connection.Close();
return(objObject);
}
#pragma warning disable CA1822 // Mark members as static
public DbConnection GetDbConnection(string connectionName)
#pragma warning restore CA1822 // Mark members as static
{
if (string.IsNullOrWhiteSpace(connectionName))
{
throw new Exception("ConnectionName cannot be empty.");
}
var connections = DBConnections.LoadConnections();
var connection = connections.FindConnection(connectionName) ??
throw new Exception($"Cannot find connection '{connectionName}'.");
var conn = new Connection(connection.Provider, connection.ConnectionString);
conn.Open();
return(conn.DbConnection);
}
public void SaveConnections()
{
var prevSelectedConnection = _DBConnections?.SelectedConnection;
_DBConnections = new DBConnections();
foreach (DBConnection connection in bindingConnections)
{
if (connection != _CustomConnection)
{
_DBConnections.Connections.Add(connection);
}
}
if (bindingConnections.Current is DBConnection selectedConnection &&
selectedConnection != _CustomConnection)
{
_DBConnections.SelectedConnection = selectedConnection.Name;
}
// GET: api/Room
public IEnumerable <RoomModel> Get()
{
SqlConnection conn = DBConnections.GetConnection();
SqlCommand cmd;
SqlDataReader rdr;
string query;
List <RoomModel> output = new List <RoomModel>();
try
{
conn.Open();
query = "select * from Room";
cmd = new SqlCommand(query, conn);
//read the data for that command
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
output.Add(new RoomModel(rdr["Building"].ToString(),
Int32.Parse(rdr["RoomNo"].ToString()),
Int32.Parse(rdr["Capacity"].ToString())));
}
}
catch (Exception e)
{
throw e;
//throw e;
}
finally
{
if (conn.State == System.Data.ConnectionState.Open)
{
conn.Close();
}
}
return(output);
//return new string[] { "value1", "value2" };
}
public override FreeDocument DictSerialize(Scenario scenario = Scenario.Database)
{
var dict = base.DictSerialize();
dict.Children = Tasks.Select(d => d.DictSerialize()).ToList();
var connectors = new FreeDocument
{
Children = DBConnections.Select(d => (d as IDictionarySerializable).DictSerialize()).ToList()
};
dict.Add("DBConnections", connectors);
var param = new FreeDocument
{
Children = Parameters.Select(d => d.UnsafeDictSerialize()).ToList()
};
if (ConfigSelector.SelectItem != null)
{
dict.Add("ParameterName", ConfigSelector.SelectItem);
}
dict.Add("Parameters", param);
if (sysProcessManager != null)
{
var runningTasks = new FreeDocument
{
Children =
sysProcessManager.CurrentProcessTasks
.Where(d => d.IsCanceled == false && d.Publisher is SmartETLTool)
.OfType <IDictionarySerializable>().Select(d => d.DictSerialize())
.ToList()
};
dict.Add("RunningTasks", runningTasks);
}
return(dict);
}
public override void DictDeserialize(IDictionary <string, object> docu, Scenario scenario = Scenario.Database)
{
base.DictDeserialize(docu);
var doc = docu as FreeDocument;
if (doc.Children != null)
{
var items = doc.Children;
foreach (var item in items)
{
var proces = new ProcessTask();
proces.Project = this;
proces.DictDeserialize(item);
Tasks.Add(proces);
}
}
if (docu["DBConnections"] != null)
{
var items = docu["DBConnections"] as FreeDocument;
if (items?.Children == null)
{
return;
}
foreach (var item in items.Children)
{
var type = item["TypeName"].ToString();
var conn = PluginProvider.GetObjectByType <IDataBaseConnector>(type) as DBConnectorBase;
if (conn == null)
{
continue;
}
conn.DictDeserialize(item);
DBConnections.Add(conn);
}
}
}
protected override void ProcessRecord()
{
if (string.IsNullOrWhiteSpace(ConnectionName))
throw new Exception("ConnectionName cannot be empty.");
if (ConnectionName.Contains(":"))
throw new Exception("ConnectionName cannot contain colon (':') character.");
var connections = DBConnections.LoadConnections();
var connection = connections.FindConnection(ConnectionName);
if (connection != null)
{
if (Replace)
connections.Connections.Remove(connection);
else
throw new Exception($"Connection '{ConnectionName}' already exists.");
}
var strProvider = Provider switch
{
DbProvider.MsSqlServer => ConnectionFactory.SqlClientFactoryLightName,
DbProvider.MsSqlServerFull => ConnectionFactory.SqlClientFactoryName,
DbProvider.MySql => ConnectionFactory.MySqlFactoryName,
DbProvider.SQLite => ConnectionFactory.SQLiteFactoryName,
DbProvider.ODBC => ConnectionFactory.OdbcFactoryName,
_ => ConnectionFactory.SqlClientFactoryLightName
};
connection = new DBConnection()
{
Name = ConnectionName,
Provider = strProvider,
Description = this.Description
};
connection.ConnectionString = ConnectionString;
connections.Connections.Add(connection);
DBConnections.SaveConnections(connections);
Messenger.Default.Send<ConnectionListChangedMessage>(new ConnectionListChangedMessage());
}
}
public void LoadConnections()
{
bindingConnections.Clear();
_CustomConnection = new DBConnection()
{
Name = "(Custom connection)", Description = "Use connection string in top edit box."
};
bindingConnections.Add(_CustomConnection);
_DBConnections = DBConnections.LoadConnections();
int selectedIndex = 0;
for (int i = 0; i < _DBConnections.Connections.Count; i++)
{
var connection = _DBConnections.Connections[i];
int index = bindingConnections.Add(connection);
if (string.Compare(connection.Name, _DBConnections.SelectedConnection, true) == 0)
{
selectedIndex = index;
}
}
if (bindingConnections.Count > 0)
{
if (selectedIndex >= 0 && selectedIndex < bindingConnections.Count)
{
bindingConnections.Position = selectedIndex;
}
else
{
bindingConnections.Position = 0;
}
}
}
private void txtAccountNumber_TextChanged(object sender, EventArgs e)
{
List <ChequeModel> Acheck = new List <ChequeModel>();
DBConnections dbconnection = new DBConnections();
// var listofchecksAccount = Acheck.Select(a => a.AccountNo).ToList();
if (txtAccountNumber.Text.Length == 12)
{
dbconnection.GetNameifExisting(Acheck);
for (int i = 0; i < Acheck.Count; i++)
{
if (txtAccountNumber.Text == Acheck[i].AccountNo)
{
//CheckBRSTNandChkType();
txtAccountName1.Text = Acheck[i].Name1.ToString();
txtAccountName2.Text = Acheck[i].Name2.ToString();
cmbBranch.Text = Acheck[i].Address1.ToString();
// cmbChkType.Text = Acheck[i].ChkTypeName.ToString();
}
}
}
}
public int DeleteCreationDayBefore(DateTime date)
{
int deletedCnt = 0;
OleDbConnection conn = null;
try
{
conn = DBConnections.NewConnection();
conn.Open();
string sql =
"delete " +
" from claim_order " +
"where creationDate < @date";
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
OleDbParameter dateParam = new OleDbParameter("@data", OleDbType.Date);
dateParam.Value = date;
cmd.Parameters.Add(dateParam);
deletedCnt = cmd.ExecuteNonQuery();
}
catch (Exception e)
{
MessageBox.Show("Message: " + e.Message + "\r\n" + "Stack Trace: " + e.StackTrace, "·ÃÎÊÊý¾Ý¿â´íÎó");
}
finally
{
if (conn != null)
{
conn.Close();
}
}
return(deletedCnt);
}
public override void DictDeserialize(IDictionary <string, object> docu, Scenario scenario = Scenario.Database)
{
base.DictDeserialize(docu);
var doc = docu as FreeDocument;
if (doc.Children != null)
{
var items = doc.Children;
foreach (var item in items)
{
var proces = new ProcessTask();
proces.Project = this;
proces.DictDeserialize(item);
Tasks.Add(proces);
}
}
if (docu["DBConnections"] != null)
{
var items = docu["DBConnections"] as FreeDocument;
if (items?.Children != null)
{
foreach (var item in items.Children)
{
var type = item["TypeName"].ToString();
var conn = PluginProvider.GetObjectByType <IDataBaseConnector>(type) as DBConnectorBase;
if (conn == null)
{
continue;
}
conn.DictDeserialize(item);
DBConnections.Add(conn);
}
}
}
if (docu["RunningTasks"] != null)
{
var tasks = docu["RunningTasks"] as FreeDocument;
SavedRunningTasks = tasks?.Children;
}
if (docu["Parameters"] != null)
{
var tasks = docu["Parameters"] as FreeDocument;
if (tasks != null)
{
Parameters.AddRange(tasks?.Children?.Select(d =>
{
var param = new ParameterItem();
param.UnsafeDictDeserialize(d);
return(param);
}));
}
}
ConfigSelector.SelectItem =
docu["ParameterName"].ToString();
if (DBConnections.FirstOrDefault(d => d.TypeName == GlobalHelper.Get("FileManager")) == null)
{
var filemanager = new FileManager {
Name = GlobalHelper.Get("recent_file")
};
DBConnections.Add(filemanager);
}
if (DBConnections.FirstOrDefault(d => d.TypeName == "MongoDB") == null)
{
var mongo = new MongoDBConnector {
Name = "MongoDB连接器"
};
mongo.DBName = "hawk";
DBConnections.Add(mongo);
}
if (DBConnections.FirstOrDefault(d => d.TypeName == GlobalHelper.Get("SQLiteDatabase")) == null)
{
var sqlite = new SQLiteDatabase {
Name = GlobalHelper.Get("SQLiteDatabase")
};
sqlite.DBName = "hawk-sqlite";
DBConnections.Add(sqlite);
}
}
/*
* Dictionary<ClaimOrder, double> double ÊÇÐèÒªºËËã·ÑÓõÄÌìÊý
*/
public IDictionary <ClaimOrder, double> FindInformDaysOver14(DateTime lastAccountDate)
{
IDictionary <ClaimOrder, double> claimOrders = new Dictionary <ClaimOrder, double>();
OleDbConnection conn = null;
try
{
conn = DBConnections.NewConnection();
conn.Open();
string sql =
"select RTV," +
"claimNo," +
"storeNo," +
"lotNo," +
"supplierNo," +
"supplierName," +
"dept," +
"qty," +
"pcs," +
"claimAmount," +
"claimReason," +
"decidedDate," +
"arriveRTVDate," +
"informDate," +
"informDays," +
"withdrawDate," +
"gateOutDate," +
"gateOutType," +
"destoryInformDate," +
"destoryType," +
"informDateIfOver50Days," +
"creationDate, " +
"oid, " +
"IIF(withdrawDate > @lastAccountDate, " +
"informDays - (withdrawDate - informDate)," +
"informDays - (@lastAccountDate - informDate)) as needAccountDays " +
"from claim_order " +
"where informDays >= 14 ";
;
OleDbCommand cmd = conn.CreateCommand();
cmd.CommandText = sql;
OleDbParameter lastAccountParam = new OleDbParameter("@lastAccountDate", OleDbType.Date);
lastAccountParam.Value = lastAccountDate;
cmd.Parameters.Add(lastAccountParam);
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
claimOrders[ReadClaimOrder(reader)] =
reader.GetDouble(reader.GetOrdinal("needAccountDays"));
}
}
catch (Exception e)
{
MessageBox.Show("Message: " + e.Message + "\r\n" + "Stack Trace: " + e.StackTrace, "·ÃÎÊÊý¾Ý¿â´íÎó");
}
finally
{
if (conn != null)
{
conn.Close();
}
}
return(claimOrders);
}
