private bool AssuredConnected()
{
switch (connection.State)
{
case (System.Data.ConnectionState.Closed):
connection.Open();
return(false);
case (System.Data.ConnectionState.Broken):
connection.Close();
connection.Open();
return(false);
default: return(true);
}
}
/**/
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlDataReader</returns>
public SqlDataReader RunProcedure(string storedProcName, params IDataParameter[] parameters)
{
System.Data.IDbConnection iConn = this.GetConnection();
{
iConn.Open();
using (SqlCommand sqlCmd = BuildQueryCommand(iConn, storedProcName, parameters))
{
return(sqlCmd.ExecuteReader(CommandBehavior.CloseConnection));
}
}
}
/**/
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="tableName">DataSet结果中的表名</param>
/// <returns>DataSet</returns>
public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
{
using (System.Data.IDbConnection iConn = this.GetConnection())
{
DataSet dataSet = new DataSet();
iConn.Open();
System.Data.IDataAdapter iDA = this.GetAdapater();
iDA = this.GetAdapater(BuildQueryCommand(iConn, storedProcName, parameters));
((SqlDataAdapter)iDA).Fill(dataSet, tableName);
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
return(dataSet);
}
}
public string NewPurchaseReq(List <Core.Domain.Purchase_Requisition.PurchaseRequisitionDetail> PurchaseItem, Core.Domain.Purchase_Requisition.PurchaseRequisition PurchaseHeader)
{
db = new SqlConnection(ConfigurationManager.ConnectionStrings["cnConsumption"].ConnectionString);
string sql;
//First Add Header
int ID = 0;
string NewPrNO = "PR-" + DateTime.Now.ToLongTimeString();
sql = " INSERT INTO TH_PurchaseRequisition(PRNo,PRDate,KitchenID,Remarks)VALUES ('" + NewPrNO + "',@PRDate,1,@Remarks) " +
"Select Cast(SCOPE_IDENTITY() AS int)";
try
{
ID = db.Query <int>(sql, new
{
PurchaseHeader.PRDate,
PurchaseHeader.Remarks
}).SingleOrDefault();
}
catch (Exception e)
{
if (e.Message.IndexOf("") > 0)
{
db.Close();
return("-1");
}
}
foreach (PurchaseRequisitionDetail obj in PurchaseItem)
{
sql = " Insert into TD_PurchaseRequisition (PRID,ItemID,Qty,Unit,Specs) " +
" Values (" + ID + ",@ItemID,@Qty,@Unit,@Specs)";
db.Execute(sql, new
{
obj.ItemId,
obj.Qty,
obj.Unit,
obj.Specs
});
}
return(NewPrNO);
}
/**/
/// <summary>
/// 执行SQL语句，返回影响的记录数 、用于增删改
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public int ExecuteSql(string SqlString)
{
using (System.Data.IDbConnection iConn = this.GetConnection())
{
using (System.Data.IDbCommand iCmd = GetCommand(SqlString, iConn))
{
iConn.Open();
try
{
int rows = iCmd.ExecuteNonQuery();
return(rows);
}
catch (System.Exception E)
{
throw new Exception(E.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
/**/
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public int ExecuteSqlTran(ArrayList SQLStringList)
{
int i = 1;
using (System.Data.IDbConnection iConn = this.GetConnection())
{
iConn.Open();
using (System.Data.IDbCommand iCmd = GetCommand())
{
iCmd.Connection = iConn;
using (System.Data.IDbTransaction iDbTran = iConn.BeginTransaction())
{
iCmd.Transaction = iDbTran;
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > 1)
{
iCmd.CommandText = strsql;
iCmd.ExecuteNonQuery();
}
}
iDbTran.Commit();
}
catch (System.Exception E)
{
iDbTran.Rollback();
i = -1;
return(i);
throw new Exception(E.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
return(i);
}
}
}
}
/**/
/// <summary>
/// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlCommand</returns>
private SqlCommand BuildQueryCommand(IDbConnection iConn, string storedProcName, IDataParameter[] parameters)
{
IDbCommand iCmd = GetCommand(storedProcName, iConn);
iCmd.CommandType = CommandType.StoredProcedure;
if (parameters == null)
{
return((SqlCommand)iCmd);
}
foreach (IDataParameter parameter in parameters)
{
iCmd.Parameters.Add(parameter);
}
return((SqlCommand)iCmd);
}
/**/
/// <summary>
/// 执行存储过程，返回影响的行数
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="rowsAffected">影响的行数</param>
/// <returns></returns>
public int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
{
using (System.Data.IDbConnection iConn = this.GetConnection())
{
int result;
iConn.Open();
using (SqlCommand sqlCmd = BuildIntCommand(iConn, storedProcName, parameters))
{
rowsAffected = sqlCmd.ExecuteNonQuery();
result = (int)sqlCmd.Parameters["ReturnValue"].Value;
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
return(result);
}
}
}
/// <summary>
/// 커넥션을 닫는다.
/// </summary>
/// <param name="con"></param>
public virtual void CloseConnection(System.Data.IDbConnection con)
{
if (this.Transaction != null)
{
return;
}
if (con == null)
{
return;
}
if (con.State == System.Data.ConnectionState.Open)
{
con.Close();
}
con = null;
}
/**/
/// <summary>
/// 执行存储过程 填充已经存在的DataSet数据集
/// </summary>
/// <param name="storeProcName">存储过程名称</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="dataSet">要填充的数据集</param>
/// <param name="tablename">要填充的表名</param>
/// <returns></returns>
public DataSet RunProcedure(string storeProcName, IDataParameter[] parameters, DataSet dataSet, string tableName)
{
using (System.Data.IDbConnection iConn = this.GetConnection())
{
iConn.Open();
System.Data.IDataAdapter iDA = this.GetAdapater();
iDA = this.GetAdapater(BuildQueryCommand(iConn, storeProcName, parameters));
((SqlDataAdapter)iDA).Fill(dataSet, tableName);
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
return(dataSet);
}
}
/**/
/// <summary>
/// 执行存储过程并返回受影响的行数
/// </summary>
/// <param name="storedProcName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public int RunProcedureNoQuery(string storedProcName, IDataParameter[] parameters)
{
int result = 0;
using (System.Data.IDbConnection iConn = this.GetConnection())
{
iConn.Open();
using (SqlCommand scmd = BuildQueryCommand(iConn, storedProcName, parameters))
{
result = scmd.ExecuteNonQuery();
}
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
return(result);
}
/**/
/// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public int ExecuteSql(string SQLString, params IDataParameter[] iParms)
{
using (System.Data.IDbConnection iConn = this.GetConnection())
{
System.Data.IDbCommand iCmd = GetCommand();
{
try
{
PrepareCommand(out iCmd, iConn, null, SQLString, iParms);
int rows = iCmd.ExecuteNonQuery();
iCmd.Parameters.Clear();
return(rows);
}
catch (System.Exception E)
{
throw new Exception(E.Message);
}
finally
{
iCmd.Dispose();
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
/**/
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）</param>
public int ExecuteSqlTran(Hashtable SQLStringList)
{
int i = 1;
using (System.Data.IDbConnection iConn = this.GetConnection())
{
iConn.Open();
using (IDbTransaction iTrans = iConn.BeginTransaction())
{
System.Data.IDbCommand iCmd = GetCommand();
try
{
//循环
foreach (DictionaryEntry myDE in SQLStringList)
{
string cmdText = myDE.Key.ToString();
IDataParameter[] iParms = (IDataParameter[])myDE.Value;
PrepareCommand(out iCmd, iConn, iTrans, cmdText, iParms);
int val = iCmd.ExecuteNonQuery();
iCmd.Parameters.Clear();
}
iTrans.Commit();
}
catch
{
iTrans.Rollback();
i = -1;
throw;
}
finally
{
iCmd.Dispose();
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
return(i);
}
}
}
private bool InsertFraudMsgs(ILogger logger)
{
bool bRet = false;
try
{
Database d = OPS.Components.Data.DatabaseFactory.GetDatabase();
System.Data.IDbConnection DBCon = d.GetNewConnection();
DBCon.Open();
try
{
String strSQL = String.Format("insert into MSGS_XML_FRAUD_OPERATIONS (MXF_UNI_ID,MXF_MOVDATE,MXF_NUMBER,MXF_NAME,MXF_XPRTN_DATE,MXF_FIN_ID,MXF_XML) values " +
"({0},to_date('{1}','hh24missddmmyy'),'{2}','{3}',to_date('{4}','hh24missddmmyy'),{5},'{6}')",
_unitId,
OPS.Comm.Dtx.DtxToString(_date),
_szCCNumber,
_szCCName,
OPS.Comm.Dtx.DtxToString(_dtExpirDate),
_fineNumber,
_root.OuterXml);
logger.AddLog("[Msg04:Process]: " + strSQL, LoggerSeverities.Debug);
OracleCommand cmd = new OracleCommand(strSQL, (OracleConnection)DBCon);
if (cmd.ExecuteNonQuery() == 1)
{
bRet = true;
}
cmd.Dispose();
}
catch
{
}
DBCon.Close();
}
catch
{
}
return(bRet);
}
/// <summary>
/// 执行查询语句，返回一条记录对应的对象
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="strSQL">查询语句</param>
/// <returns></returns>
public T query <T>(string strSQL, params IDataParameter[] iParms) where T : class, new()
{
System.Data.IDbConnection iConn = this.GetConnection();
{
System.Data.IDbCommand iCmd = GetCommand();
{
try
{
PrepareCommand(out iCmd, iConn, null, strSQL, iParms);
System.Data.IDataReader iReader = iCmd.ExecuteReader();
iCmd.Parameters.Clear();
if (iReader.Read())
{
PropertyInfo[] pis = typeof(T).GetProperties();
T model = new T();
foreach (PropertyInfo pi in pis)
{
pi.SetValue(model, iReader[pi.Name], null);
}
return(model);
}
}
catch (System.Exception e)
{
throw new Exception(e.Message);
}
finally
{
iCmd.Dispose();
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
return(null);
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public void ExecuteSqlTran(List <string> list)
{
using (System.Data.IDbConnection iConn = this.GetConnection())
{
iConn.Open();
using (System.Data.IDbCommand iCmd = GetCommand())
{
iCmd.Connection = iConn;
using (System.Data.IDbTransaction iDbTran = iConn.BeginTransaction())
{
iCmd.Transaction = iDbTran;
try
{
for (int n = 0; n < list.Count; n++)
{
string strsql = list[n].ToString();
iCmd.CommandText = strsql;
iCmd.ExecuteNonQuery();
}
iDbTran.Commit();
}
catch (System.Exception E)
{
iDbTran.Rollback();
throw new Exception(E.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
}
}
/// <summary>
/// 执行查询语句，返回IDataAdapter
/// </summary>
/// <param name="strSQL">查询语句</param>
/// <returns>IDataAdapter</returns>
public IDataAdapter ExecuteReader(string strSQL)
{
using (System.Data.IDbConnection iConn = this.GetConnection())
{
iConn.Open();
try
{
System.Data.IDataAdapter iAdapter = this.GetAdapater(strSQL, iConn);
return(iAdapter);
}
catch (System.Exception e)
{
throw new Exception(e.Message);
}
finally
{
if (iConn.State != ConnectionState.Closed)
{
iConn.Close();
}
}
}
}
/// <summary>
/// Establishes a connection to the database, must hold the lock before this method is called.
/// </summary>
protected virtual void EnsureConnected()
{
if (m_connection != null && m_connection.State == System.Data.ConnectionState.Open)
{
return;
}
if (m_connection != null)
{
try
{
m_connection.Close();
}
catch
{
}
finally
{
m_connection = null;
}
}
m_connection = DatabaseHelper.CreateConnection(ConnectionString, ConnectionClass);
}
public int ExecuteNonQuery(System.Data.IDbConnection connection, System.Data.CommandType commandType, string commandText)
{
int affectRows;
if (connection.State != System.Data.ConnectionState.Open)
{
connection.Open();
}
SqlCommand Command = new SqlCommand();
if (!this.needTransaction)
{
tran = null;
}
PrepareCommand(Command, connection, tran, CommandType.Text, commandText);
try
{
affectRows = Command.ExecuteNonQuery();
}
catch (System.Data.SqlClient.SqlException ex)
{
needTransaction = false;
throw ex;
}
finally
{
if (mustCloseConnection)
{
if (!needTransaction)
{
connection.Close();
}
}
}
return(affectRows);
}
//---------------------------------------------------------------------------------------------------------------------------------------------------------
public virtual void Disconnect()
{
// Disconnect can be called from Dispose and should guarantee no errors
// stops a proper reconnection after a timeout
// if(!m_bConnected)
// return;
try {
if (dbTransaction != null)
{
RollbackTransaction(false);
}
if (dbCommand != null)
{
dbCommand.Dispose();
dbCommand = null;
}
if (dbConnection != null)
{
try {
dbConnection.Close();
} catch {
} finally {
dbConnection.Dispose();
dbConnection = null;
}
}
dbConnected = false;
} catch (Exception ex) {
Logger.LogError(6, "Failed to disconnect: " + ex.StackTrace);
}
}
// private object ExecuteIdentity(System.Data.IDbConnection connection, System.Data.CommandType commandType, string CommandText)
// {
// object identity;
//// string[] commands = CommandText.Split(';');(?<command>*)
// string[] commands = SystemFramework.Utils.UtilMethods.SplitEx(CommandText,"$$$");//2005-10-19 用比较少用的"$$$"分割CommandText,防止用户输入带有 ; 的文本时导致Split出错
//
// if(commands.Length < 1)
// return null;
//
// if (connection.State != System.Data.ConnectionState.Open)
// connection.Open();
//
//// tran = connection.BeginTransaction();
// SqlCommand Command = (SqlCommand)connection.CreateCommand();
//// Command.Transaction = tran as SqlCeTransaction;
// Command.CommandType = commandType;
// Command.CommandText = commands[0];
//
// try
// {
// identity = Command.ExecuteScalar();
// if(commands.Length >= 2)
// {
// Command.CommandText = commands[1];
// identity = Command.ExecuteScalar();
// }
//
//// tran.Commit();
// return identity;
// }
// catch(SqlException ex)
// {
//// tran.Rollback();
// needTransaction = false;
// throw ex;
// }
// finally
// {
// if (mustCloseConnection)
// {
// if(!needTransaction)
// {
// connection.Close();
// }
// }
// }
// }
public object ExecuteScalar(System.Data.IDbConnection connection, System.Data.CommandType commandType, string CommandText)
{
object affectRows;
if (connection.State != System.Data.ConnectionState.Open)
{
connection.Open();
}
OracleCommand Command = new OracleCommand();
if (!this.needTransaction)
{
tran = null;
}
PrepareCommand(Command, connection, tran, CommandType.Text, CommandText);
try
{
affectRows = Command.ExecuteScalar();
}
catch (OracleException ex)
{
needTransaction = false;
throw ex;
}
finally
{
if (mustCloseConnection)
{
if (!needTransaction)
{
connection.Close();
}
}
}
return(affectRows);
}
public void Run(string EPRTRcmsSourceConnectionString, Altova.IO.Output xliff_core_1_2_transitionalTarget)
{
mAutoNumberStates = new AutoNumberStateMap();
// Open the source(s)
WriteTrace("Connecting to EPRTRcms database...\n");
try
{
m_EPRTRcmsInstance = new System.Data.OleDb.OleDbConnection(EPRTRcmsSourceConnectionString);
m_EPRTRcmsInstance.Open();
}
catch (Exception e)
{
throw new DataSourceUnavailableException("Error connecting to database.", e);
}
m_Queryloopdbo_xliff_file = Altova.Db.DbTreeOperations.CreateCommand(
m_EPRTRcmsInstance,
"SELECT [SourceLanguage], [DataType], [TargetLanguage], [FileID] FROM [dbo].[xliff_file]"
);
m_Queryloopdbo_xliff_file.CommandTimeout = 0;
m_Queryloopdbo_xliff_header_FileID = Altova.Db.DbTreeOperations.CreateCommand(
m_EPRTRcmsInstance,
"SELECT [uid] FROM [dbo].[xliff_header] WHERE [FileID] = ?"
, System.Data.DbType.Int32);
m_Queryloopdbo_xliff_header_FileID.CommandTimeout = 0;
m_Queryloopdbo_xliff_Group_FileID = Altova.Db.DbTreeOperations.CreateCommand(
m_EPRTRcmsInstance,
"SELECT [GroupText], [GroupID] FROM [dbo].[xliff_Group] WHERE [FileID] = ?"
, System.Data.DbType.Int32);
m_Queryloopdbo_xliff_Group_FileID.CommandTimeout = 0;
m_Queryloopdbo_xliff_TransID_GroupID = Altova.Db.DbTreeOperations.CreateCommand(
m_EPRTRcmsInstance,
"SELECT [TransIDText], [TransID] FROM [dbo].[xliff_TransID] WHERE [GroupID] = ?"
, System.Data.DbType.Int32);
m_Queryloopdbo_xliff_TransID_GroupID.CommandTimeout = 0;
m_Queryloopdbo_xliff_Value_TransID = Altova.Db.DbTreeOperations.CreateCommand(
m_EPRTRcmsInstance,
"SELECT [Source] FROM [dbo].[xliff_Value] WHERE [TransID] = ?"
, System.Data.DbType.Int32);
m_Queryloopdbo_xliff_Value_TransID.CommandTimeout = 0;
m_Queryloopdbo_xliff_Value_TransID2 = Altova.Db.DbTreeOperations.CreateCommand(
m_EPRTRcmsInstance,
"SELECT [Target] FROM [dbo].[xliff_Value] WHERE [TransID] = ?"
, System.Data.DbType.Int32);
m_Queryloopdbo_xliff_Value_TransID2.CommandTimeout = 0;
// Create the target
XmlDocument xliff_core_1_2_transitionalDoc = (xliff_core_1_2_transitionalTarget.Type == Altova.IO.Output.OutputType.XmlDocument) ? xliff_core_1_2_transitionalTarget.Document : new XmlDocument();
// create processing instruction etc...
XmlNode xliffTargetObject = xliff_core_1_2_transitionalDoc.CreateElement("xliff", "urn:oasis:names:tc:xliff:document:1.2");
xliff_core_1_2_transitionalDoc.AppendChild(xliffTargetObject);
XmlElement xliffTargetEl = (XmlElement)xliffTargetObject;
xliffTargetEl.SetAttribute("xmlns", "urn:oasis:names:tc:xliff:document:1.2");
xliffTargetEl.SetAttribute("xmlns:xsi", "http://www.w3.org/2001/XMLSchema-instance");
xliffTargetEl.SetAttribute("schemaLocation", "http://www.w3.org/2001/XMLSchema-instance", "urn:oasis:names:tc:xliff:document:1.2 C:/EIONET/CMS_XLIFF/xliff_out/xliff-core-1.2-transitional.xsd");
xliffTargetEl.SetAttribute("xmlns:xs", "http://www.w3.org/2001/XMLSchema");
// Execute mapping
loopdbo_xliff_file(m_EPRTRcmsInstance, xliffTargetObject);
// Close the target
XmlTreeOperations.SaveDocument(
xliff_core_1_2_transitionalDoc,
xliff_core_1_2_transitionalTarget,
"UTF-8",
false,
false,
true
);
// Close the Source Library
m_EPRTRcmsInstance.Close();
if (runDoesCloseAll)
{
xliff_core_1_2_transitionalTarget.Close();
}
}
private static IEnumerable QueryInternal(Type type, Mapping.Table table, System.Data.IDbConnection connection, String sql, Object param, IDbTransaction transaction, CommandType?commandType, Int32?commandTimeout)
{
var command = new CommandDefinition(sql, param, transaction, commandTimeout, commandType, CommandFlags.Buffered);
var identity = new SqlMapper.Identity(sql, commandType, connection, type, param == null ? null : param.GetType(), null);
var info = SqlMapper.GetCacheInfo(identity, param, command.AddToCache);
IDbCommand cmd = null;
IDataReader reader = null;
Boolean wasClosed = connection.State == ConnectionState.Closed;
try
{
cmd = command.SetupCommand(connection, info.ParamReader);
if (wasClosed)
{
connection.Open();
}
reader = cmd.ExecuteReader(wasClosed ? CommandBehavior.CloseConnection | CommandBehavior.SequentialAccess : CommandBehavior.SequentialAccess);
wasClosed = false; // *if* the connection was closed and we got this far, then we now have a reader
// with the CloseConnection flag, so the reader will deal with the connection; we
// still need something in the "finally" to ensure that broken SQL still results
// in the connection closing itself
var tuple = info.Deserializer;
int hash = SqlMapper.GetColumnHash(reader);
if (tuple.Func == null || tuple.Hash != hash)
{
if (reader.FieldCount == 0) //https://code.google.com/p/dapper-dot-net/issues/detail?id=57
{
yield break;
}
tuple = info.Deserializer = new SqlMapper.DeserializerState(hash, GetDeserializer(type, reader, 0, -1, false, table));
if (command.AddToCache)
{
SqlMapper.SetQueryCache(identity, info);
}
}
var func = tuple.Func;
while (reader.Read())
{
yield return(func(reader));
}
while (reader.NextResult())
{
}
// happy path; close the reader cleanly - no
// need for "Cancel" etc
reader.Dispose();
reader = null;
command.OnCompleted();
}
finally
{
if (reader != null)
{
if (!reader.IsClosed)
{
try { cmd.Cancel(); }
catch { /* don't spoil the existing exception */ }
}
reader.Dispose();
}
if (wasClosed)
{
connection.Close();
}
if (cmd != null)
{
cmd.Dispose();
}
}
}
public void Convert()
{
if (ValidateDestination() == false)
{
return;
}
Server SourceServer = null;
if (Configuration.SqlServerIntegratedSecurity)
{
SourceServer = new Server(Configuration.SqlServerName);
}
else
{
ServerConnection svrConn = new ServerConnection(Configuration.SqlServerName);
svrConn.LoginSecure = false;
svrConn.Login = Configuration.SqlServerUserName;
svrConn.Password = Configuration.SqlServerPassword;
SourceServer = new Server(svrConn);
}
Database sourceDb = SourceServer.Databases[Configuration.SqlServerDatabaseName];
if (sourceDb == null)
{
Console.WriteLine("Source db '{0}' not found in '{1}'",
Configuration.SqlServerDatabaseName,
Configuration.SqlServerName);
return;
}
List <string> schemaNames = new List <string>();
foreach (Schema schema in sourceDb.Schemas)
{
if (schema.Name.Substring(0, 3) != "db_")
{
schemaNames.Add(schema.Name);
}
}
List <string> tableNames = new List <string>();
foreach (Table tbl in sourceDb.Tables)
{
if (!tbl.IsSystemObject)
{
tableNames.Add(tbl.Name);
}
}
string sqlCeConnectionString = string.Empty;
if (string.IsNullOrEmpty(Configuration.SqlCePassword))
{
sqlCeConnectionString = string.Format("Data Source='{0}';Encrypt={1};SSCE:Max Database Size=4091;",
Configuration.SqlCeFileName, Configuration.SqlCeIsEncrypted.ToString().ToUpper());
}
{
sqlCeConnectionString = string.Format("Data Source='{0}';Password={1};Encrypt={2};SSCE:Max Database Size=4091;",
Configuration.SqlCeFileName, Configuration.SqlCePassword, Configuration.SqlCeIsEncrypted.ToString().ToUpper());
}
bool copiedFailed = false;
sqlCeConnectionString = sqlCeConnectionString.Replace("LCID=idpe;", "");
System.Data.SqlServerCe.SqlCeEngine eng = new System.Data.SqlServerCe.SqlCeEngine(sqlCeConnectionString);
object engine = eng;
Type type = engine.GetType();
Assembly asm = Assembly.GetAssembly(typeof(System.Data.SqlServerCe.SqlCeEngine));
Console.WriteLine("Sql Ce Version:" + asm.GetName().Version.ToString());
//Create the database.
MethodInfo mi = type.GetMethod("CreateDatabase");
Console.WriteLine("Creating the SQL Server Compact Edition Database...");
try
{
mi.Invoke(engine, null);
}
catch (TargetInvocationException ex)
{
Console.WriteLine("You do not have permissions to save the file to " + Configuration.SqlCeFileName + ". Please select a different destination path and try again.");
return;
}
Console.WriteLine("Connecting to the SQL Server Compact Edition Database...");
Type connType = asm.GetType("System.Data.SqlServerCe.SqlCeConnection");
System.Data.IDbConnection conn = (System.Data.IDbConnection)Activator.CreateInstance(connType);
conn.ConnectionString = sqlCeConnectionString;
conn.Open();
//create all the tables
int tblCount = 0;
Type cmdType = asm.GetType("System.Data.SqlServerCe.SqlCeCommand");
System.Data.IDbCommand cmd = (System.Data.IDbCommand)Activator.CreateInstance(cmdType);
foreach (string tblName in tableNames)
{
Table tbl = sourceDb.Tables[tblName, Configuration.SqlServerSchemaName];
if (tbl == null)
{
Console.WriteLine("Table '" + tblName + "' was not found in the selected schema.");
copiedFailed = true;
break;
}
if (tbl.IsSystemObject)
{
continue;
}
//if (tbl.Name == "IdpeVersion")
// Debugger.Break();
Console.WriteLine("Scripting table: " + tbl.Name);
StringBuilder sb = new StringBuilder();
sb.Append("CREATE TABLE [").Append(tbl.Name).Append("](");
int colIdx = 0;
List <string> pKeys = new List <string>();
foreach (Column col in tbl.Columns)
{
if (colIdx > 0)
{
sb.Append(", ");
}
//if (col.Name == "Data")
// Debugger.Break();
sb.Append("[").Append(col.Name).Append("]").Append(" ");
int max = 0;
switch (col.DataType.SqlDataType)
{
case SqlDataType.VarChar:
max = col.DataType.MaximumLength;
col.DataType = new DataType(SqlDataType.NVarChar);
col.DataType.MaximumLength = max;
break;
case SqlDataType.Char:
max = col.DataType.MaximumLength;
col.DataType = new DataType(SqlDataType.NChar);
col.DataType.MaximumLength = max;
break;
case SqlDataType.Text:
case SqlDataType.VarCharMax:
col.DataType = new DataType(SqlDataType.NText);
break;
case SqlDataType.VarBinaryMax:
col.DataType = new DataType(SqlDataType.Image);
break;
case SqlDataType.Decimal:
int scale = col.DataType.NumericScale;
int precision = col.DataType.NumericPrecision;
col.DataType = new DataType(SqlDataType.Numeric);
col.DataType.NumericPrecision = precision;
col.DataType.NumericScale = scale;
break;
}
sb.Append(col.DataType.SqlDataType.ToString());
SqlDataType datatype = col.DataType.SqlDataType;
if (datatype == SqlDataType.NVarChar || datatype == SqlDataType.NChar)
{
sb.Append(" (").Append(col.DataType.MaximumLength.ToString()).Append(") ");
}
else if (datatype == SqlDataType.Numeric)
{
sb.Append(" (").Append(col.DataType.NumericPrecision).Append(",").Append(col.DataType.NumericScale).Append(")");
}
if (col.InPrimaryKey)
{
pKeys.Add(col.Name);
}
//if (col.InPrimaryKey)
// sb.Append(" CONSTRAINT PK").Append(col.Name);
if (!col.Nullable)
{
sb.Append(" NOT NULL");
}
if (col.DefaultConstraint != null && !String.IsNullOrEmpty(col.DefaultConstraint.Text))
{
string def = col.DefaultConstraint.Text.Replace("((", "(").Replace("))", ")");
sb.Append(" DEFAULT ").Append(col.DefaultConstraint.Text);
//sb.Append(" DEFAULT (1) ");
}
if (col.Identity)
{
sb.Append(" IDENTITY (").Append(col.IdentitySeed.ToString()).Append(",").Append(col.IdentityIncrement.ToString()).Append(")");
}
//if (col.InPrimaryKey)
// sb.Append(" PRIMARY KEY");
colIdx++;
}
sb.Append(")");
cmd.CommandText = sb.ToString();
cmd.CommandText = cmd.CommandText.Replace("suser_sname()", "'Manual User'");
cmd.Connection = conn;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Create table failed! " + ex.Message);
copiedFailed = true;
break;
}
//add the PK constraints
if (pKeys.Count > 0)
{
sb = new StringBuilder();
sb.Append("ALTER TABLE [").Append(tbl.Name).Append("] ADD CONSTRAINT PK_");
//create the constraint name
for (int k = 0; k < pKeys.Count; k++)
{
if (k > 0)
{
sb.Append("_");
}
sb.Append(pKeys[k]);
}
sb.Append(" PRIMARY KEY(");
//add the constraint fields
for (int k = 0; k < pKeys.Count; k++)
{
if (k > 0)
{
sb.Append(", ");
}
sb.Append(pKeys[k]);
}
sb.Append(")");
cmd.CommandText = sb.ToString();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Create table failed! Failed creating the Primary Key(s).");
copiedFailed = true;
break;
}
}
//copy the indexes
Console.WriteLine("Scripting the indexes for table: " + tbl.Name);
foreach (Index idx in tbl.Indexes)
{
if (idx.IndexKeyType == IndexKeyType.DriPrimaryKey)
{
continue;
}
sb = new StringBuilder();
sb.Append("CREATE");
if (idx.IsUnique)
{
sb.Append(" UNIQUE");
}
//if (!idx.IsClustered)
// sb.Append(" CLUSTERED");
//else
// sb.Append(" NONCLUSTERED");
sb.Append(" INDEX ").Append(idx.Name).Append(" ON [").Append(tbl.Name).Append("](");
for (int i = 0; i < idx.IndexedColumns.Count; i++)
{
if (i > 0)
{
sb.Append(", ");
}
sb.Append("[" + idx.IndexedColumns[i].Name + "]");
}
sb.Append(")");
cmd.CommandText = sb.ToString();
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Create table failed! Failed creating the indexes." + ex.Message);
copiedFailed = true;
break;
}
}
tblCount++;
}
if (!copiedFailed)
{
//Now copy the data
bool copyData = true;
if (copyData)
{
Console.WriteLine("Copying database data.");
foreach (string tblName in tableNames)
{
Table tbl = sourceDb.Tables[tblName];
if (tbl.IsSystemObject)
{
continue;
}
Console.WriteLine("Copying " + tbl.RowCount.ToString() + " rows from " + tbl.Name);
bool hasIdentity = false;
string alterSql = "ALTER TABLE [{0}] ALTER COLUMN [{1}] IDENTITY({2},{3})";
string IDColName = "";
long increment = 1;
//If the table has an Identity column then we need to re-set the seed and increment
//This is a hack since SQL Server Compact Edition does not support SET IDENTITY_INSERT <columnname> ON
foreach (Column col in tbl.Columns)
{
if (col.Identity)
{
hasIdentity = true;
IDColName = col.Name;
alterSql = String.Format(alterSql, tbl.Name, col.Name, "{0}", "{1}");
}
}
//Select SQL
string sql = "SELECT * FROM [{0}]";
//Insert Sql
string insertSql = "INSERT INTO [{0}] ({1}) VALUES ({2})";
StringBuilder sbColums = new StringBuilder();
StringBuilder sbValues = new StringBuilder();
int idx1 = 0;
foreach (Column col in tbl.Columns)
{
if (col.Name != IDColName)
{
if (idx1 > 0)
{
sbColums.Append(",");
sbValues.Append(",");
}
sbColums.Append("[").Append(col.Name).Append("]");
sbValues.Append("?");
idx1++;
}
}
//if (tbl.Name.Contains("IdpeVersion"))
// Debugger.Break();
insertSql = String.Format(insertSql, tbl.Name, sbColums.ToString(), sbValues.ToString());
sql = String.Format(sql, tbl.Name);
DataSet ds = sourceDb.ExecuteWithResults(sql);
if (ds.Tables.Count > 0)
{
if (ds.Tables[0].Rows.Count > 0)
{
int rowCnt = 0;
foreach (DataRow row in ds.Tables[0].Rows)
{
rowCnt++;
if (hasIdentity)
{
long seed = long.Parse(row[IDColName].ToString());
//seed--;
string alterTableForIDColumn = String.Format(alterSql, seed.ToString(), increment.ToString());
cmd.CommandText = alterTableForIDColumn;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Failed altering the Table for IDENTITY insert.");
copiedFailed = true;
break;
}
}
sbValues = new StringBuilder();
cmd.Parameters.Clear();
cmd.CommandText = insertSql;
for (int i = 0; i < tbl.Columns.Count; i++)
{
if (tbl.Columns[i].Name != IDColName)
{
//if (tbl.Columns[i].Name == "Data")
// Debugger.Break();
Type type1 = asm.GetType("System.Data.SqlServerCe.SqlCeParameter");
object[] objArray1 = new object[2];
objArray1[0] = tbl.Columns[i].Name;
objArray1[1] = row[tbl.Columns[i].Name];
object p = Activator.CreateInstance(type1, objArray1);
cmd.Parameters.Add(p);
}
}
cmd.CommandText = String.Format(insertSql, sbValues.ToString());
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Copy table data failed!");
copiedFailed = true;
break;
}
}
}
}
}
//Now add the FK relationships
if (!copiedFailed)
{
Console.Write("Adding ForeignKeys.");
string fkSql = "ALTER TABLE [{0}] ADD CONSTRAINT [{1}] FOREIGN KEY([{2}]) REFERENCES [{3}] ([{4}])";
foreach (string tblName in tableNames)
{
Table tbl = sourceDb.Tables[tblName];
if (tbl.IsSystemObject)
{
continue;
}
int fkCnt = tbl.ForeignKeys.Count;
int fxIdx = 0;
foreach (ForeignKey fk in tbl.ForeignKeys)
{
if (!tableNames.Contains(fk.ReferencedTable))
{
continue;
}
fxIdx++;
Console.WriteLine(tbl.Name + ": " + fk.Name);
string createFKSql = String.Format(fkSql, tbl.Name, fk.Name, "{0}", fk.ReferencedTable, sourceDb.Tables[fk.ReferencedTable].Indexes[fk.ReferencedKey].IndexedColumns[0].Name);
StringBuilder sbFk = new StringBuilder();
foreach (ForeignKeyColumn col in fk.Columns)
{
if (sbFk.Length > 0)
{
sbFk.Append(",");
}
sbFk.Append(col.Name);
}
createFKSql = String.Format(createFKSql, sbFk.ToString());
cmd.CommandText = createFKSql;
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Creating ForeignKeys failed!");
//copiedFailed = true;
//break;
}
}
}
}
Console.WriteLine("Closing the connection to the SQL Server Compact Edition Database...");
conn.Close();
conn.Dispose();
if (!copiedFailed)
{
Console.WriteLine("Completed!");
}
else
{
Console.WriteLine("Copy failed!");
}
}
else
{
Console.WriteLine("Finished!");
}
}
else
{
Console.WriteLine("Copy failed!");
}
}
private void pmQueryRefTo()
{
WS.Data.Agents.cDBMSAgent objSQLHelper = new WS.Data.Agents.cDBMSAgent(App.ERPConnectionString, App.DatabaseReside);
string strFldList = " GLRef.fcRfType, GLRef.fcRefType , GLRef.fcCoor , year(GLREF.FDDATE) as Yr , month(GLREF.FDDATE) as Mth";
strFldList = strFldList + ", sum(GLRef.fnAmt) as Amt";
strFldList = strFldList + ", sum(GLRef.fnVatAmt) as VatAmt";
strFldList = strFldList + ", sum(GLRef.fnPayAmt) as PaidAmt";
strFldList = strFldList + ", sum(GLRef.fnBPayInv) as PayAmt";
//strFldList = strFldList + " , (select Book.fcCode from Book where Book.fcSkid = GLRef.fcBook) as QcBook";
strFldList = strFldList + " , (select Coor.fcCode from Coor where Coor.fcSkid = GLRef.fcCoor) as QcCoor";
strFldList = strFldList + " , (select Coor.fcName from Coor where Coor.fcSkid = GLRef.fcCoor) as QnCoor";
strFldList = strFldList + " , (select Branch.fcCode from Branch where Branch.fcSkid = GLRef.fcBranch) as QcBranch";
string strSQLStr = "select " + strFldList + " from GLRef ";
strSQLStr = strSQLStr + " left join Branch on Branch.fcSkid = GLRef.fcBranch ";
strSQLStr = strSQLStr + " left join Coor on Coor.fcSkid = GLRef.fcCoor ";
strSQLStr = strSQLStr + " where GLRef.fcCorp = ? ";
//strSQLStr = strSQLStr + " and GLRef.fcCoor in (select fcSkid from Coor where Coor.fcCorp = ? and Coor.fcIsCust = 'Y' and Coor.fcCode between ? and ?) ";
strSQLStr = strSQLStr + " and GLRef.fcStat <> 'C' ";
strSQLStr = strSQLStr + " group by GLRef.fcBranch, GLRef.fcCoor, GLRef.fcRFType, GLRef.fcREFType , year(GLREF.FDDATE) , month(GLREF.FDDATE) ";
strSQLStr = strSQLStr + " order by year(GLREF.FDDATE) , month(GLREF.FDDATE) , QcBranch , QcCoor , GLRef.fcRFType, GLRef.fcREFType ";
System.Data.IDbConnection conn = objSQLHelper.GetDBConnection();
System.Data.IDbCommand cmd = objSQLHelper.GetDBCommand(strSQLStr.ToUpper(), conn);
try
{
conn.Open();
cmd.CommandTimeout = 2000;
cmd.Parameters.Add(AppUtil.ReportHelper.AddParameter(App.ActiveCorp.RowID, DbType.String));
bool bllIsPYokma = false;
decimal decAmt = 0;
decimal decVatAmt = 0;
decimal decPayAmt = 0;
string strLastQcCoor = "";
string strDRRefType = "SB,SD,SI,SQ,SR,SU,SV,SW,SX,SY,SZ,";
string strCRRefType = "SA,SC,SM,SN";
string strPayRefType = "RI,RV";
string strCashRefType = "SR,SU,";
string strRefType = "";
string strRfType = "";
string strAddr11 = "";
string strAddr21 = "";
string strAddr31 = "";
string strCoorTel = "";
string strCoorFax = "";
string strMemdata = "";
System.Data.OleDb.OleDbDataReader dtrGLRef = (System.Data.OleDb.OleDbDataReader)cmd.ExecuteReader();
if (dtrGLRef.HasRows)
{
//DataRow dtrBFRow = dtsPrintPreview.XRAR01.NewRow();
DataRow dtrPreview = null;
DataRow dtrBFRow = null;
string strCurrPrefix = "";
while (dtrGLRef.Read())
{
strRefType = dtrGLRef["fcRefType"].ToString();
strRfType = dtrGLRef["fcRfType"].ToString();
dtrPreview["cYrMth"] = Convert.ToInt32(dtrGLRef["Yr"]).ToString("0000") + "/" + Convert.ToInt32(dtrGLRef["Mth"]).ToString("00");
dtrPreview["CQCCOOR"] = dtrGLRef["QcCoor"].ToString();
dtrPreview["CQNCOOR"] = dtrGLRef["QnCoor"].ToString();
decAmt = Convert.ToDecimal(dtrGLRef["Amt"]) + Convert.ToDecimal(dtrGLRef["VatAmt"]);
decPayAmt = Convert.ToDecimal(dtrGLRef["PayAmt"]);
if ((SysDef.gc_RFTYPE_RECEIVE + SysDef.gc_RFTYPE_RCV_VAT + SysDef.gc_RFTYPE_PAYMENT + SysDef.gc_RFTYPE_PAY_VAT).IndexOf(strRfType) > -1)
{
dtrPreview["NPAYAMT"] = Convert.ToDecimal(dtrPreview["NPAYAMT"]) + decPayAmt;
}
else if ((SysDef.gc_RFTYPE_INV_SELL + SysDef.gc_RFTYPE_INV_BUY).IndexOf(strRfType) > -1)
{
dtrPreview["NBUYSELL"] = Convert.ToDecimal(dtrPreview["NBUYSELL"]) + decAmt + decVatAmt;
if (strCashRefType.IndexOf(strRefType) > -1)
{
dtrPreview["NPAYAMT"] = Convert.ToDecimal(dtrPreview["NPAYAMT"]) + decAmt + decVatAmt;
}
}
else if ((SysDef.gc_RFTYPE_CR_SELL + SysDef.gc_RFTYPE_CR_BUY).IndexOf(strRfType) > -1)
{
dtrPreview["NCRNOTE"] = Convert.ToDecimal(dtrPreview["NCRNOTE"]) + decAmt + decVatAmt;
}
else if ((SysDef.gc_RFTYPE_DR_SELL + SysDef.gc_RFTYPE_DR_BUY).IndexOf(strRfType) > -1)
{
dtrPreview["NDRNOTE"] = Convert.ToDecimal(dtrPreview["NDRNOTE"]) + decAmt + decVatAmt;
}
}
}
dtrGLRef.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
finally
{
conn.Close();
}
}
public virtual Durados.Web.Mvc.UI.Helpers.tblObject GetChartData(string chartId, string queryString)
{
string errorMessage;
string createMessage;
string userId;
string userRole;
SetErrorMessageValues(out errorMessage, out createMessage, out userId, out userRole);
if (string.IsNullOrEmpty(chartId))
{
throw new ChartIdIsMissingException();
}
int id = Convert.ToInt32(chartId.Replace("Chart", ""));
int?dashboardId = Database.GetDashboardPK(id);
if (!dashboardId.HasValue)
{
throw new DashboardIdIsMissingException();
}
if (!Map.Database.Dashboards[dashboardId.Value].Charts.ContainsKey(id))
{
throw new ChartIdNotFoundException(chartId);
}
Chart chart = Map.Database.Dashboards[dashboardId.Value].Charts[id];
if (string.IsNullOrEmpty(chart.SQL))
{
throw new SqlStatementIsMissingException();
}
System.Data.IDbConnection connection = null;
System.Data.IDataReader reader = null;
try
{
Durados.Web.Mvc.UI.Helpers.tblObject content = GetChartJsonObject(chart);
// Connect
System.Data.IDbCommand cmd = BuildConnectionAndCommand(userId, userRole, chart, queryString, ref connection);
// Read
try
{
reader = cmd.ExecuteReader();
}
catch (Exception exception)
{
throw new SqlExecutionFailureException(exception.Message);
}
string xFieldName = chart.XField;
Dictionary <int, string> yFieldNames = new Dictionary <int, string>();
//if(!string.IsNullOrEmpty(chart.YField) && chart.ChartType!=ChartType.Gauge)
// yFieldNames.AddRange(chart.YField.Split(','));
content.Neg = false;
List <Durados.Web.Mvc.UI.Helpers.ChartSeries> series = new List <Durados.Web.Mvc.UI.Helpers.ChartSeries>();
if (reader.FieldCount >= 2 || chart.ChartType == ChartType.Gauge && reader.FieldCount == 1)
{
if (string.IsNullOrEmpty(xFieldName) || chart.ChartType == ChartType.Gauge)
{
xFieldName = reader.GetName(0);
}
FillChartData(content, reader, xFieldName, yFieldNames, series, chart);
}
else
{
string message = chart.ChartType != ChartType.Gauge ? string.Format(errorMessage, "SQL", "The select statement must contain at least two columns.", chartId) : string.Format(errorMessage, "SQL", "The select statement must contain one column.", chartId);
throw new SqlReturnsLessThanTwoColumnsException(chartId);
}
SetChartProperties(chart, content, xFieldName, yFieldNames, series);
//System.Web.Script.Serialization.JavaScriptSerializer jss = new System.Web.Script.Serialization.JavaScriptSerializer();
//return jss.Serialize(content);
return(content);
}
catch (Exception exception)
{
throw new ChartException(exception.Message);
}
finally
{
try
{
reader.Close();
}
catch { }
try
{
connection.Close();
}
catch { }
}
}
