/// <summary>
/// Disposes any resources that have been created.
/// </summary>
public void Dispose()
{
if (_dataAdapter != null)
{
_dataAdapter.Dispose();
}
if (_commandBuilder != null)
{
_commandBuilder.Dispose();
}
if (_selectCommand != null)
{
_selectCommand.Dispose();
}
if (_insertCommand != null)
{
_insertCommand.Dispose();
}
if (_updateCommand != null)
{
_updateCommand.Dispose();
}
if (_deleteCommand != null)
{
_deleteCommand.Dispose();
}
}
public T[] List <T>(string Sql, System.Data.Common.DbTransaction transaction = null)
{
DbBase.LastSQL = Sql;
System.Data.Common.DbCommand cmd = null;
System.Data.Common.DbDataReader dataReader = null;
T[] result = null;
try
{
if (transaction == null && DbConnection.State != ConnectionState.Open)
{
DbConnection.Open();
}
cmd = DbBase.DbConnection.CreateCommand();
cmd.CommandText = Sql;
if (transaction != null)
{
cmd.Transaction = transaction;
}
dataReader = cmd.ExecuteReader();
List <string> columns = GetDataReaderColumns(dataReader);
result = List <T>(dataReader, columns);
}
finally
{
if (dataReader != null)
{
dataReader.Close();
dataReader.Dispose();
dataReader = null;
}
if (cmd != null)
{
cmd.Dispose();
cmd = null;
}
if (transaction == null)
{
DbConnection.Close();
}
}
if (result == null)
{
return(new T[] { });
}
return(result);
}
public void DealWithDataReaderInMutipleThread()
{
i = 0;
Database db = MB.Orm.Persistence.DatabaseHelper.CreateDatabase();
System.Data.Common.DbCommand dbCmd = db.GetSqlStringCommand("SELECT * FROM MTSBW.BF_USER");
using (IDataReader reader = db.ExecuteReader(dbCmd)) {
try {
//System.Threading.Thread t1 = new System.Threading.Thread(new System.Threading.ThreadStart(() => {
// while (reader.Read()) {
// System.Threading.Interlocked.Add(ref i, 1);
// }
// }));
//System.Threading.Thread t2 = new System.Threading.Thread(new System.Threading.ThreadStart(() => {
// try {
// while (reader.Read()) {
// System.Threading.Interlocked.Add(ref i, 1);
// }
// }
// catch (Exception ex) {
// string msg = ex.ToString();
// }
//}));
double timeElapsed = 0;
using (MB.Util.MethodTraceWithTime trace = new Util.MethodTraceWithTime("test")) {
Task[] tasks = new Task[3] {
Task.Factory.StartNew(() => consumeDataReader(reader, ref i)),
Task.Factory.StartNew(() => consumeDataReader(reader, ref i)),
Task.Factory.StartNew(() => consumeDataReader(reader, ref i))
};
Task.WaitAll(tasks);
timeElapsed = trace.GetExecutedTimes();
}
int result = i;
}
catch (Exception ex) {
}
finally {
dbCmd.Dispose();
}
}
}
/// <summary>
/// 获得一个字段的值
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public object GetVar(DbCommand cmd)
{
object o;
try
{
cmd.Connection = dbHelper.CreateConnection();
o = dbHelper.ExecuteScalar(cmd);
}
catch (Exception e)
{
throw e;
}
finally
{
cmd.Dispose();
}
return o;
}
public override void CreateOrModify_TablesAndFields(string dataTable, DatasetConfig datasetConfig)
{
this.datasetConfig = datasetConfig;
if (!string.IsNullOrEmpty(datasetConfig.DatasetTableName)) //Add the posibility to use a specific table_name (for using the table more then ones)
this.dataTable = datasetConfig.DatasetTableName;
else
this.dataTable = dataTable;
this.fieldList = datasetConfig.DatasetConfigRows;
List<DatasetConfigRow> createFieldList;
lock (FileNameLockObjects[myConfig.DatabaseFile])
{
//Look if Table exists, when not, create it!
try
{
string sql = "SELECT * FROM " + dataTable + ";";
myCmd.Connection = myDBConn;
myCmd.CommandText = sql;
myReader = myCmd.ExecuteReader();
}
catch (SQLiteException ex)
{
if (ex.ErrorCode == SQLiteErrorCode.Error)
{
try
{
string sql = "CREATE TABLE " + dataTable + " (id INTEGER PRIMARY KEY ASC AUTOINCREMENT); ";
myCmd.CommandText = sql;
myCmd.ExecuteNonQuery();
sql = "SELECT * FROM " + dataTable + ";";
myCmd.CommandText = sql;
myReader = myCmd.ExecuteReader();
}
catch (SQLiteException ex_ex)
{
throw ex_ex;
}
}
else
{
throw ex;
}
}
//Look for the Fields, create or alter them!
List<String> existDBFelderliste = new List<string>();
for (int n = 0; n < myReader.FieldCount; n++)
{
existDBFelderliste.Add(myReader.GetName(n));
}
myReader.Close();
myReader.Dispose();
myCmd.Dispose();
//Wenn Date Time Feld gesetzt...
dateFieldName = datasetConfig.DateTimeDatabaseField;
createFieldList = new List<DatasetConfigRow>(fieldList);
if (!string.IsNullOrEmpty(datasetConfig.DateTimeDatabaseField))
createFieldList.Add(new DatasetConfigRow() {DatabaseField = dateFieldName, DatabaseFieldType = "TEXT"});
foreach (DatasetConfigRow myFeld in createFieldList)
{
foreach (string existMyFeld in existDBFelderliste)
{
if (myFeld.DatabaseField.ToLower() == existMyFeld.ToLower())
{
goto nextFeld;
}
}
//Feld existiert nicht -> erzeugen
string sql = "ALTER TABLE " + dataTable + " ADD COLUMN " + myFeld.DatabaseField + " " + myFeld.DatabaseFieldType;
try
{
myCmd = new SQLiteCommand();
myCmd.Connection = myDBConn;
myCmd.CommandText = sql;
myCmd.ExecuteNonQuery();
myCmd.Dispose();
}
catch (SQLiteException ex)
{
throw ex;
}
nextFeld:
//Irgendeine anweisung, da sonst der Sprung nicht geht...
{
}
}
}
//Create Insert Command
string wertliste = "", felderliste = "";
foreach (DatasetConfigRow myFeld in createFieldList)
{
if (wertliste != "")
{
wertliste += ",";
felderliste += ",";
}
felderliste += myFeld.DatabaseField;
wertliste += "@" + myFeld.DatabaseField;
}
insertCommand = "INSERT INTO " + this.dataTable + "(" + felderliste + ") values(" + wertliste + ")";
}
protected override bool _internal_Write()
{
lock (FileNameLockObjects[myConfig.DatabaseFile])
{
myCmd = new SQLiteCommand();
//Look if the Connection is still open..
try
{
string sql = "SELECT id FROM " + dataTable + " WHERE id = 0";
myCmd.Connection = myDBConn;
myCmd.CommandText = sql;
myCmd.ExecuteNonQuery();
}
catch (Exception)
{
myDBConn.Close(); //Verbindung schließen!
myDBConn.Open();
if (myDBConn.State != System.Data.ConnectionState.Open)
{
Logging.LogText("Error ReConnecting to Database! Dataset:" + datasetConfig.Name, Logging.LogLevel.Error);
return false;
}
}
//Add the Fields to the Database
myCmd.Connection = myDBConn;
myCmd.CommandText = insertCommand;
myCmd.Dispose();
int tryCounter = 0;
nomol:
try
{
using (DbTransaction dbTrans = myDBConn.BeginTransaction())
{
using (DbCommand cmd = myDBConn.CreateCommand())
{
cmd.CommandText = insertCommand;
for (int n = 0; n < _maxAdd; n++)
//foreach (IEnumerable<object> values in _intValueList)
{
cmd.Parameters.Clear();
IEnumerable<object> values = _intValueList[n];
var addDateTime = _intDateTimesList[n];
if (!string.IsNullOrEmpty(dateFieldName))
cmd.Parameters.Add(new SQLiteParameter("@" + dateFieldName, System.Data.DbType.String) { Value = addDateTime.ToString("yyyy.MM.dd - HH:mm:ss.fff") });
using (IEnumerator<DatasetConfigRow> e1 = fieldList.GetEnumerator())
using (IEnumerator<object> e2 = values.GetEnumerator())
{
while (e1.MoveNext() && e2.MoveNext())
{
//foreach (DatasetConfigRow field in fieldList)
//{
DatasetConfigRow field = e1.Current;
Object value = e2.Current; //values[fnr++];
switch (field.PLCTag.TagDataType)
{
case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.LInt:
case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.LWord:
case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.Int:
case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.Dint:
case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.Word:
case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.Dword:
case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.Byte:
case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.BCDByte:
case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.BCDWord:
case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.BCDDWord:
cmd.Parameters.Add(new SQLiteParameter("@" + field.DatabaseField, System.Data.DbType.String) {Value = value.ToString()});
break;
case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.Float:
case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.LReal:
cmd.Parameters.Add(new SQLiteParameter("@" + field.DatabaseField, System.Data.DbType.String) {Value = value.ToString().Replace(',', '.')});
break;
case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.DateTime:
cmd.Parameters.Add(new SQLiteParameter("@" + field.DatabaseField, System.Data.DbType.String) {Value = ((DateTime) value).ToString("yyyy-MM-dd HH:mm:ss.fffffff")});
break;
case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.String:
case DotNetSiemensPLCToolBoxLibrary.DataTypes.TagDataType.CharArray:
cmd.Parameters.Add(new SQLiteParameter("@" + field.DatabaseField, System.Data.DbType.String) {Value = (String) value});
break;
}
}
}
cmd.ExecuteNonQuery();
}
//Ringpufferarchiv...
if (datasetConfig.MaxDatasets > 0)
{
string delstr = "DELETE FROM " + dataTable + " WHERE id <= (SELECT max(id) FROM " + dataTable + ") - (" + datasetConfig.MaxDatasets.ToString() + ")";
cmd.CommandText = delstr;
cmd.ExecuteNonQuery();
}
}
dbTrans.Commit();
}
}
catch (SQLiteException ex)
{
if (ex.ErrorCode == SQLiteErrorCode.Busy || ex.ErrorCode == SQLiteErrorCode.Locked) //Locked || Busy
{
tryCounter++;
if (tryCounter > 20)
throw new Exception("SQLLite-Datenbank nach 20 Versuchen immer noch locked oder busy!!");
goto nomol;
}
else
{
throw ex;
}
}
catch (Exception ex)
{
throw ex;
}
if (_newDataCallback != null)
_newDataCallback(datasetConfig.Name);
return true;
}
}
public DbDataReader ExecuteSPForDataReader(string spName, DbParameter[] spParams)
{
try
{
_genricCommand = _genricConnection.CreateCommand();
OpenConnection();
_genricCommand.CommandType = CommandType.StoredProcedure;
_genricCommand.CommandText = spName;
if (spParams != null)
{
foreach (var spParam in spParams)
{
if (spParam.Value == null)
{
spParam.Value = DBNull.Value;
}
_genricCommand.Parameters.Add(spParam);
}
}
return _genricCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
finally
{
_genricCommand.Dispose();
_genricCommand = null;
}
}
/// <summary>
/// This method provide Data Reader (Base Object of type "DbDataReader") which can be
/// type cast to any DataReader class.
/// </summary>
/// <param name="selectSQL"> valid SQL select query</param>
/// <returns>Base Object of type "DbDataReader"</returns>
/// <example>
/// System.Data.SqlClient.SqlDataReader sqlRd =
/// (System.Data.SqlClient.SqlDataReader)dtl.ExecuteQueryForReader("select * from authors");
///</example>
public Object ExecuteQueryForReader(string selectSQL)
{
DbDataReader dbDataRdr;
try
{
_genricCommand = _genricConnection.CreateCommand();
OpenConnection();
_genricCommand.CommandType = CommandType.Text;
_genricCommand.CommandText = selectSQL;
dbDataRdr = _genricCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
finally
{
_genricCommand.Dispose();
_genricCommand = null;
}
return (Object)dbDataRdr;
}
/// <summary>
/// 執行SQL 語法
/// </summary>
public object ExecuteSql(DbCommand cmd)
{
OleDbConnection connection = Connection;
cmd.Connection = connection;
try
{
connection.Open();
return cmd.ExecuteNonQuery();
}
catch (OleDbException exception)
{
throw exception;
}
finally
{
connection.Close();
cmd.Dispose();
}
}
public bool CheckLogDb(string file)
{
bool ret = true;
try
{
string dir = Path.GetDirectoryName(file);
if(!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
cn = new SQLiteConnection();
{
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=False;", file);
cn.Open();
{
using(DbTransaction tr = cn.BeginTransaction())
{
try
{
using(DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = @"CREATE TABLE IF NOT EXISTS GPS (id INTEGER NOT NULL PRIMARY KEY,
TimeUTC DATETIME NOT NULL,
SessionCounter INTEGER NOT NULL,
Delta DOUBLE,
Speed DOUBLE,
SeaLevelAltitude DOUBLE,
EllipsoidAltitude DOUBLE,
SatellitesInView TINYINT,
SatelliteCount TINYINT,
Lat DOUBLE NOT NULL,
Lng DOUBLE NOT NULL,
PositionDilutionOfPrecision DOUBLE,
HorizontalDilutionOfPrecision DOUBLE,
VerticalDilutionOfPrecision DOUBLE,
FixQuality TINYINT NOT NULL,
FixType TINYINT NOT NULL,
FixSelection TINYINT NOT NULL);
CREATE INDEX IF NOT EXISTS IndexOfGPS ON GPS (TimeUTC, PositionDilutionOfPrecision);";
cmd.ExecuteNonQuery();
}
this.cmd = cn.CreateCommand();
{
cmd.CommandText = @"INSERT INTO GPS
(TimeUTC,
SessionCounter,
Delta,
Speed,
SeaLevelAltitude,
EllipsoidAltitude,
SatellitesInView,
SatelliteCount,
Lat,
Lng,
PositionDilutionOfPrecision,
HorizontalDilutionOfPrecision,
VerticalDilutionOfPrecision,
FixQuality,
FixType,
FixSelection) VALUES(@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16);";
cmd.Parameters.Add(new SQLiteParameter("@p1"));
cmd.Parameters.Add(new SQLiteParameter("@p2"));
cmd.Parameters.Add(new SQLiteParameter("@p3"));
cmd.Parameters.Add(new SQLiteParameter("@p4"));
cmd.Parameters.Add(new SQLiteParameter("@p5"));
cmd.Parameters.Add(new SQLiteParameter("@p6"));
cmd.Parameters.Add(new SQLiteParameter("@p7"));
cmd.Parameters.Add(new SQLiteParameter("@p8"));
cmd.Parameters.Add(new SQLiteParameter("@p9"));
cmd.Parameters.Add(new SQLiteParameter("@p10"));
cmd.Parameters.Add(new SQLiteParameter("@p11"));
cmd.Parameters.Add(new SQLiteParameter("@p12"));
cmd.Parameters.Add(new SQLiteParameter("@p13"));
cmd.Parameters.Add(new SQLiteParameter("@p14"));
cmd.Parameters.Add(new SQLiteParameter("@p15"));
cmd.Parameters.Add(new SQLiteParameter("@p16"));
cmd.Prepare();
}
tr.Commit();
}
catch
{
tr.Rollback();
ret = false;
}
}
}
}
}
catch(Exception ex)
{
if(cn != null)
{
cn.Dispose();
cn = null;
}
if(cmd != null)
{
cmd.Dispose();
cmd = null;
}
Debug.WriteLine("CreateEmptyDB: " + ex.ToString());
ret = false;
}
if(ret)
{
LogDb = file;
}
else
{
LogDb = null;
}
return ret;
}
private void button2_Click(object sender, EventArgs e)
{
try {
//////Microsoft.Office.Tools.Excel.Workbook theWorkbook = Microsoft.Office.Tools.Excel.ex .ExcelObj.Workbooks.Open(
////// this.txtXmlPath.Text, 0, true, 5,
////// "", "", true, Microsoft.Office.Tools.Excel.xl.xlWindows, "\t", false, false,
////// 0, true);
//////Excel.Sheets sheets = theWorkbook.Worksheets;
//////Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);
//////int i=1;
//////string[] strArray;
//////System.Array myvalues;
//////do {
////// Excel.Range range = worksheet.get_Range("A" + i.ToString(), "O" + i.ToString());
////// myvalues = (System.Array)range.Cells.Value;
////// strArray = ConvertToStringArray(myvalues);
//////} while (strArray[0].Length > 0);
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + this.txtXlsPath.Text + @";Extended Properties=""Excel 8.0;HDR=YES;""";
DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = connectionString;
////using (DbCommand command = connection.CreateCommand())
////{
//// command.CommandText = "INSERT INTO [Cities$]
//// (ID, City, State) VALUES(4,\"Tampa\",\"Florida\")";
connection.Open();
//// command.ExecuteNonQuery();
////}
System.Data.Common.DbCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT * FROM [volumes$]";
System.Data.Common.DbDataReader drdr = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(drdr);
drdr.Dispose();
cmd.Dispose();
XmlDocument docXml = new XmlDocument();
XmlNode nodeRoot = docXml.AppendChild(docXml.CreateNode(XmlNodeType.Element, "root", docXml.NamespaceURI));
XmlNode nodeVolumes = nodeRoot.AppendChild(docXml.CreateNode(XmlNodeType.Element, "volumes", docXml.NamespaceURI));
XmlNode nodeVolume;
XmlNode nodeColumns = nodeRoot.AppendChild(docXml.CreateNode(XmlNodeType.Element, "columns", docXml.NamespaceURI));
foreach (DataColumn dc in dt.Columns)
{
XmlNode nodeColumn = nodeColumns.AppendChild(docXml.CreateNode(XmlNodeType.Element, "column", docXml.NamespaceURI));
XmlAttribute attFldName = nodeColumn.Attributes.Append((XmlAttribute)docXml.CreateNode(XmlNodeType.Attribute, "FldName", docXml.NamespaceURI));
attFldName.InnerText = dc.ColumnName;
}
foreach (DataRow dr in dt.Rows)
{
nodeVolume = nodeVolumes.SelectSingleNode("./volume[@volume_code = '" + dr["Volume"].ToString() + "']");
if (nodeVolume == null)
{
nodeVolume = nodeVolumes.AppendChild(docXml.CreateNode(XmlNodeType.Element, "volume", docXml.NamespaceURI));
XmlAttribute attVolumeCode = nodeVolume.Attributes.Append((XmlAttribute)docXml.CreateNode(XmlNodeType.Attribute, "volume_code", docXml.NamespaceURI));
attVolumeCode.InnerText = dr["volume"].ToString();
}
XmlNode nodeIssue = nodeVolume.AppendChild(docXml.CreateNode(XmlNodeType.Element, "issue", docXml.NamespaceURI));
foreach (DataColumn dc in dt.Columns)
{
if (string.Compare(dc.ColumnName, "volume", true) != 0)
{
XmlNode nodeFld;
nodeFld = nodeIssue.AppendChild(docXml.CreateNode(XmlNodeType.Element, "Fld", docXml.NamespaceURI));
XmlAttribute attFldName = nodeFld.Attributes.Append((XmlAttribute)(docXml.CreateNode(XmlNodeType.Attribute, "Name", docXml.NamespaceURI)));
attFldName.InnerText = dc.ColumnName;//.Replace(" ", "_");
nodeFld.InnerText = dr[dc].ToString();
}
}
}
string strXmlFilePath = this.txtXlsPath.Text.Substring(0, this.txtXlsPath.Text.LastIndexOf(".xls")) + ".xml";
docXml.Save(strXmlFilePath);
this.txtXmlPath.Text = strXmlFilePath;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// 返回第一行第一列的值。
/// </summary>
/// <param name="dbCmd">使用的DbCommand。</param>
/// <returns>包含数据的object对象。</returns>
public object ExecuteScalar(DbCommand dbCmd)
{
if (dbCmd == null)
{
return null;
}
object value = null;
try
{
using (DbConnection dbConn = CreateDbConnection())
{
if (dbConn == null)
{
return null;
}
dbCmd.Connection = dbConn;
dbConn.Open();
value = dbCmd.ExecuteScalar();
dbCmd.Dispose();
}
}
catch (Exception ex)
{
throw ex;
}
return value;
}
/// <summary>
/// 执行没有返回集合的 Transact-SQL 语句或存储过程。
/// </summary>
/// <param name="dbCmd">使用的DbCommand。</param>
/// <returns>所影响的行数。</returns>
public int ExecuteNonQuery(DbCommand dbCmd)
{
try
{
if (dbCmd == null)
{
return 0;
}
int rowNum = 0;
using (DbConnection dbConn = CreateDbConnection())
{
if (dbConn == null)
{
return 0;
}
dbCmd.Connection = dbConn;
dbConn.Open();
rowNum = dbCmd.ExecuteNonQuery();
dbCmd.Dispose();
}
return rowNum;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 返回一个数据集
/// </summary>
/// <param name="dbCmd">使用的DbCommand。</param>
/// <returns>System.Data 类的提供程序的类的新实例</returns>
public DataSet ExecuteDataSet(DbCommand dbCmd)
{
try
{
if (dbCmd == null)
{
return null;
}
var set = new DataSet();
using (DbConnection dbConn = CreateDbConnection())
{
if (dbConn == null)
{
return null;
}
dbCmd.Connection = dbConn;
dbConn.Open();
using (DbDataAdapter adapter = CreateDbDataAdapter(dbCmd))
{
adapter.Fill(set);
}
dbCmd.Dispose();
}
return set;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 执行select 操作
/// </summary>
/// <param name="sql"></param>
/// <param name="ps"></param>
/// <returns></returns>
public DataTable Select(DbCommand cmd)
{
DataTable Dt = new DataTable();
try
{
cmd.Connection = dbHelper.CreateConnection();
Dt = dbHelper.ExecuteDataTable(cmd);
}
catch (Exception e)
{
throw e;
}
finally
{
cmd.Dispose();
}
return Dt;
}
/**
* This method releases statements after the thread is
* done with them. These statements are not closed, but
* rather cached until another thread has need of them.
*
* @param stmt
* The statement that is to be released.
*/
private void ReleaseStatement(DbCommand stmt)
{
stmt.Dispose();
this.statementCache.Add(stmt);
}
/// <summary>
/// Executes a command, and returns 1 row at a time until all rows are returned
/// </summary>
/// <param name="Command">The database command to execute the reader on</param>
/// <returns></returns>
public IEnumerable<Dictionary<string, object>> QueryReader(DbCommand Command)
{
// Execute the query
DbDataReader Reader = Command.ExecuteReader();
// If we have rows, add them to the list
if (Reader.HasRows)
{
// Add each row to the rows list
while (Reader.Read())
{
Dictionary<string, object> Row = new Dictionary<string, object>(Reader.FieldCount);
for (int i = 0; i < Reader.FieldCount; ++i)
Row.Add(Reader.GetName(i), Reader.GetValue(i));
yield return Row;
}
}
// Cleanup
Reader.Close();
Reader.Dispose();
Command.Dispose();
}
/// <summary>
/// Closes the connection.
/// </summary>
/// <param name="command">The command.</param>
public void CloseConnection(DbCommand command)
{
if (command != null && command.Connection.State != ConnectionState.Closed && batchConnection == null)
{
if (command.Transaction == null)
{
CloseConnection(command.Connection);
command.Dispose();
}
}
}
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();
}
}
