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 { } } }