public string createTable(List <TableField> fields, Sql oSql) { //var commandStr = "If not exists (select name from sysobjects where name = 'Customer') CREATE TABLE Customer(First_Name char(50),Last_Name char(50),Address char(50),City char(50),Country char(25),Birth_Date datetime)"; //using (SqlCommand command = new SqlCommand(commandStr, con)) // command.ExecuteNonQuery(); string tableName = "table_" + GenerateName(10); var commandStr = "IF NOT EXISTS (select name from sysobjects where name = '" + tableName + "') CREATE TABLE " + tableName + "("; int x = 0; foreach (var field in fields) { commandStr += (x > 0 ? "," : "") + field.ToString(); x++; } commandStr += ")"; //Console.WriteLine(commandStr); SqlResultWithDataSet oResult = oSql.Open(commandStr, CommandType.Text ); return(tableName); }
public string getListEmpresas() { // Declare once, keep using var nTimeout = 30; // timeout var bLog = true; var connectionString = ShiolConfiguration.Instance.Config.SqlServerConnection.ConnectionString; // Create with a timeout of 30 seconds and logging as true var oSql = new Sql(connectionString, nTimeout, bLog); var strSql = "Select * from Establecimientos"; SqlResultWithDataSet oResult = oSql.Open(strSql, CommandType.Text); string json = null; List <PlantillaExcel> fields = new List <PlantillaExcel>(); try { foreach (DataRow row in oResult.Results.Tables[0].Rows) { PlantillaExcel field = new PlantillaExcel(row["EstablecimientoID"].ToString(), ""); field.descripcion = row["NombreEstablecimiento"].ToString(); fields.Add(field); } json = JsonConvert.SerializeObject(fields); } catch (Exception e) { Console.WriteLine(e.Message); } return(json); }
/// <summary> /// Tries to create a DataSet object. /// The resulting boolean indicates whether or not the query execution was successful /// </summary> /// <param name="aStatement">The SQL statement structure to execute and return a DataSet on</param> /// <param name="aResult">The result object that contains the DataSet and potential Exception object</param> /// <returns>A boolean result that indicates whether or not the query was able to be successfully executed</returns> /// <seealso cref="Items.Results.SqlResultWithDataSet"/> /// <seealso cref="Sql.Statement"/> public Boolean TryOpen(Statement aStatement, out SqlResultWithDataSet aResult) { // Original: // return TryOpen(aStatement, CreateConnection(), out aResult); SqlConnection oConnection = null; Boolean bReturnValue = true; aResult = null; try { oConnection = CreateConnection(); } catch (Exception aException) { bReturnValue = false; aResult = new SqlResultWithDataSet { Exception = aException, Statistics = null, Results = null }; } finally { if (bReturnValue) { bReturnValue = TryOpen(aStatement, oConnection, out aResult); } } return(bReturnValue); }
/// <summary> /// Tries to create a DataSet object. /// The resulting boolean indicates whether or not the query execution was successful /// </summary> /// <param name="aStatement">The SQL statement structure to execute and return a DataSet on</param> /// <param name="aConnection">A SQL Connection object</param> /// <param name="aCloseConnection">A boolean flag that indicates whether or not the /// connection should be closed after the statement is executed</param> /// <param name="aResult">The result object that contains the DataSet and potential Exception object</param> /// <returns>A boolean result that indicates whether or not the query was able to be successfully executed</returns> /// <seealso cref="Items.Results.SqlResultWithDataSet"/> /// <seealso cref="Sql.Statement"/> /// <seealso cref="System.Data.SqlClient.SqlConnection"/> public Boolean TryOpen(Statement aStatement, SqlConnection aConnection, Boolean aCloseConnection, out SqlResultWithDataSet aResult) { return(TryOpen(aStatement, aConnection, aCloseConnection, null, out aResult)); }
/// <summary> /// Tries to create a DataSet object. /// The resulting boolean indicates whether or not the query execution was successful /// </summary> /// <param name="aStatement">The SQL statement structure to execute and return a DataSet on</param> /// <param name="aConnection">A SQL Connection object</param> /// <param name="aCloseConnection">A boolean flag that indicates whether or not the /// connection should be closed after the statement is executed</param> /// <param name="aResult">The result object that contains the DataSet and potential Exception object</param> /// <param name="aTransaction">The SqlTransaction object that this connection is part of</param> /// <returns>A boolean result that indicates whether or not the query was able to be successfully executed</returns> /// <seealso cref="Items.Results.SqlResultWithDataSet"/> /// <seealso cref="Sql.Statement"/> /// <seealso cref="System.Data.SqlClient.SqlConnection"/> /// <seealso cref="System.Data.SqlClient.SqlTransaction"/> public Boolean TryOpen(Statement aStatement, SqlConnection aConnection, Boolean aCloseConnection, SqlTransaction aTransaction, out SqlResultWithDataSet aResult) { // Assign default values aResult = new Items.Results.SqlResultWithDataSet(); bool bCompleted; try { // Open and execute aResult = Open(aStatement, aConnection, aCloseConnection, aTransaction); bCompleted = true; } catch (Exception e) { bCompleted = false; aResult.Exception = e; } // Done return(bCompleted); }
/* * public string sendJsonSqlMessage(XmppClient client, string message) * { * * return ""; * } */ public string getJsonSqlMessage(string sqlMessage) { // Declare once, keep using var nTimeout = 30; // timeout var bLog = true; var connectionString = ShiolConfiguration.Instance.Config.SqlServerConnection.ConnectionString; // Create with a timeout of 30 seconds and logging as true var oSql = new Sql(connectionString, nTimeout, bLog); var strSql = sqlMessage; SqlResultWithDataSet oResult = oSql.Open(strSql, CommandType.Text); string json = null; try { json = JsonConvert.SerializeObject(oResult.Results.Tables[0]); } catch (Exception e) { Console.WriteLine(e.Message); } return(json); }
/// <summary> /// Tries to create a DataSet object. /// The resulting boolean indicates whether or not the query execution was successful /// </summary> /// <param name="aSqlStatement">The SQL statement to execute and return a DataSet on</param> /// <param name="aCommandType">The type of SQL statement being passed</param> /// <param name="aParameters">A list of parameters that will be used by the statement</param> /// <param name="aResult">The result object that contains the DataSet and potential Exception object</param> /// <returns>A boolean result that indicates whether or not the query was able to be successfully executed</returns> /// <seealso cref="System.Data.CommandType"/> /// <seealso cref="Items.Results.SqlResultWithDataSet"/> /// <seealso cref="System.Data.SqlClient.SqlParameter"/> /// <seealso cref="System.Collections.Generic.IEnumerable{T}"/> public Boolean TryOpen(String aSqlStatement, CommandType aCommandType, out SqlResultWithDataSet aResult, params SqlParameter[] aParameters) { return(TryOpen ( aSqlStatement, aCommandType, aParameters == null ? null : aParameters.AsEnumerable(), out aResult )); }
/// <summary> /// Tries to create a DataSet object. /// The resulting boolean indicates whether or not the query execution was successful /// </summary> /// <param name="aSqlStatement">The SQL statement to execute and return a DataSet on</param> /// <param name="aCommandType">The type of SQL statement being passed</param> /// <param name="aParameters">A list of parameters that will be used by the statement</param> /// <param name="aResult">The result object that contains the DataSet and potential Exception object</param> /// <returns>A boolean result that indicates whether or not the query was able to be successfully executed</returns> /// <seealso cref="System.Data.CommandType"/> /// <seealso cref="Items.Results.SqlResultWithDataSet"/> /// <seealso cref="System.Data.SqlClient.SqlParameter"/> /// <seealso cref="System.Collections.Generic.IEnumerable{T}"/> public Boolean TryOpen(String aSqlStatement, CommandType aCommandType, IEnumerable <SqlParameter> aParameters, out SqlResultWithDataSet aResult) { return(TryOpen ( new Statement { Sql = aSqlStatement, Type = aCommandType, Parameters = aParameters }, out aResult )); }
public bool deletePlantillaExcel(string id) { // Declare once, keep using var nTimeout = 30; // timeout var bLog = true; var connectionString = ShiolConfiguration.Instance.Config.SqlServerConnection.ConnectionString; // Create with a timeout of 30 seconds and logging as true var oSql = new Sql(connectionString, nTimeout, bLog); SqlResultWithDataSet oResult = oSql.Open("Delete from PlantillasProcesadas Where plantillaProcesadaID = @id", CommandType.Text, new SqlParameter("id", id) ); return(true); }
public string getPlantilla(string id) { // Declare once, keep using var nTimeout = 30; // timeout var bLog = true; var connectionString = ShiolConfiguration.Instance.Config.SqlServerConnection.ConnectionString; // Create with a timeout of 30 seconds and logging as true var oSql = new Sql(connectionString, nTimeout, bLog); var strSql = "SELECT tp.nombre, p.* FROM PlantillasProcesadas p Left Join tipoPlantillas tp On tp.tipoPlantillaID = p.tipoPlantillaID Where plantillaProcesadaID='" + id + "'"; SqlResultWithDataSet oResult = oSql.Open(strSql, CommandType.Text); string json = null; List <PlantillaExcel> fields = new List <PlantillaExcel>(); try { if (oResult.Results.Tables[0].Rows.Count > 0) { DataRow row = oResult.Results.Tables[0].Rows[0]; PlantillaExcel field = new PlantillaExcel(row["plantillaProcesadaID"].ToString().Trim(), row["usuarioID"].ToString()); field.estado = row["estado"].ToString() == "2" ? "Procesado" : (row["estado"].ToString() == "1" ? "Procesando" : "No Procesado"); field.tipo_nombre = row["nombre"].ToString(); field.tipo = row["tipoPlantillaID"].ToString().Trim(); field.archivo = row["rutaArchivo"].ToString(); field.fecha_creacion = row["fechaCarga"].ToString(); field.fecha_proceso = row["fechaProceso"].ToString(); //field.descripcion = "xxxx"; fields.Add(field); json = JsonConvert.SerializeObject(field); } } catch (Exception e) { Console.WriteLine(e.Message); } return(json); }
/// <summary> /// Executes a SQL statement and returns the value of the statement /// </summary> /// <param name="aStatement">The SQL statement structure to execute</param> /// <param name="aConnection">The connection to use for this SQL statement</param> /// <param name="aCloseConnection">If true, the connection will be closed after execution</param> /// <param name="aTransaction">The SqlTransaction object to use during execution</param> /// <returns>An object of type <typeparam name="T">T</typeparam></returns> /// <seealso cref="Sql.Statement"/> /// <seealso cref="System.Data.SqlClient.SqlConnection"/> public SqlResultWithValue <T> Execute <T>(Statement aStatement, SqlConnection aConnection, Boolean aCloseConnection, SqlTransaction aTransaction) { // Call .Open() and return the first row of the first column. SqlResultWithDataSet oResults = Open(aStatement, aConnection, aCloseConnection, aTransaction); DataTable oTable = oResults.Results.Tables[0]; // Open(aStatement).Tables[0]; // Create our T variable T oRetval = default(T); // Now that we have the table, grab the first row and first value if (oTable.Rows.Count > 0 && oTable.Rows[0].ItemArray.Any()) { oRetval = (T)Convert.ChangeType(oTable.Rows[0][0], typeof(T)); } // Return! return(new SqlResultWithValue <T>() { Exception = null, Value = oRetval, Statistics = oResults.Statistics }); }
public string getEmpresa(string id) { // Declare once, keep using var nTimeout = 30; // timeout var bLog = true; var connectionString = ShiolConfiguration.Instance.Config.SqlServerConnection.ConnectionString; // Create with a timeout of 30 seconds and logging as true var oSql = new Sql(connectionString, nTimeout, bLog); var strSql = "SELECT * From Establecimientos Where EstablecimientoID='" + id + "'"; SqlResultWithDataSet oResult = oSql.Open(strSql, CommandType.Text); string json = null; List <Item> fields = new List <Item>(); try { if (oResult.Results.Tables[0].Rows.Count > 0) { DataRow row = oResult.Results.Tables[0].Rows[0]; Item field = new Item(); field.id = row["EstablecimientoID"].ToString(); field.descripcion = row["NombreEstablecimiento"].ToString(); fields.Add(field); json = JsonConvert.SerializeObject(field); } } catch (Exception e) { Console.WriteLine(e.Message); } return(json); }
/// <summary> /// Performs an asynchronous query with a single return value against a database /// </summary> /// <typeparam name="T">The return type expected as a result of the query</typeparam> /// <param name="aStatement">The SQL statement structure to execute</param> /// <param name="aConnection">The connection to use for this SQL statement</param> /// <param name="aCloseConnection">A boolean flag that indicates whether or not the /// connection should be closed after the statement is executed</param> /// <param name="aTransaction">The current transaction that this command should be executed on</param> /// <returns>A Task object that represents the asynchronous request</returns> /// <seealso cref="System.Threading.Tasks.Task{T}"/> /// <seealso cref="SqlMagic.Monitor.Items.Results.SqlResultWithValue{T}"/> /// <seealso cref="namespace SqlMagic.Sql.Statement"/> /// <seealso cref="System.Data.SqlClient.SqlConnection"/> /// <seealso cref="System.Data.SqlClient.SqlTransaction"/> public async Task <SqlResultWithValue <T> > ExecuteAsync <T>(Statement aStatement, SqlConnection aConnection, Boolean aCloseConnection, SqlTransaction aTransaction) { // Make a call to OpenAsync to return a dataset. SqlResultWithDataSet oDataSet = await OpenAsync(aStatement, aConnection, aCloseConnection, aTransaction); // Create a result w/ value opbject SqlResultWithValue <T> oResult = new SqlResultWithValue <T>(); // Assign stuff manually oResult.Exception = oDataSet.Exception; oResult.Statistics = oDataSet.Statistics; // Check for success+actual results if (oDataSet.Success && oDataSet.Results.Tables[0].Rows.Count > 0) { oResult.Value = (T)Convert.ChangeType(oDataSet.Results.Tables[0].Rows[0][0], typeof(T)); } // All done return(oResult); }
public bool authenticateUser(string user, string password) { try { // Declare once, keep using var nTimeout = 30; // timeout var bLog = true; var connectionString = ShiolConfiguration.Instance.Config.SqlServerConnection.ConnectionString; // Create with a timeout of 30 seconds and logging as true var oSql = new Sql(connectionString, nTimeout, bLog); SqlResultWithDataSet oResult = oSql.Open("SELECT * FROM Usuarios WHERE NombreUsuario = @user And Clave = @password", CommandType.Text, new SqlParameter("user", user), new SqlParameter("password", password) ); return(oResult.Results.Tables[0].Rows.Count > 0 ? true : false); } catch (Exception ex) { return(false); } }
public DataTable getContabilidadEstadoResultadosDataTable(Request req, Response res) { // Declare once, keep using var nTimeout = 30; // timeout var bLog = true; var connectionString = ShiolConfiguration.Instance.Config.SqlServerConnection.ConnectionString; // Create with a timeout of 30 seconds and logging as true var oSql = new Sql(connectionString, nTimeout, bLog); /* * exec UCO_Mayorizados_Select_PorMes_ComparativoPorHotel @TipoFormatoGGPPID=2,@TipoCuentaID=0,@Ejercicio=2017, * @NivelID=0,@HotelID=0,@EstablecimientoID=112,@AreaID=0,@UnidadNegocioID=0, * @Periodo='PeriodoID<=12',@OrderByExpression='',@FormatoReporte=2 */ //Where Statement int HotelID = req.PathVariables["HotelID"] != null?Convert.ToInt32(req.PathVariables["HotelID"]) : 0; columns = new List <Column>(); _columns = new List <Column>(); columns.Add(new Column("Descripcion", "Descripcion")); if (HotelID > 0) { var strSqlC = @"Select * from Hoteles Where HotelID=@HotelId"; SqlResultWithDataSet oResult1 = oSql.Open(strSqlC, CommandType.Text, new SqlParameter("HotelId", HotelID) ); columns.Add(new Column((oResult1.Results.Tables[0].Rows.Count == 1 ? oResult1.Results.Tables[0].Rows[0]["NombreHotel"].ToString().Trim() : ""), "H1")); } else { columns.Add(new Column("Todos", "Todos")); columns.Add(new Column("SALKANTAY", "H1")); columns.Add(new Column("LARES", "H2")); columns.Add(new Column("EL MERCADO", "H3")); columns.Add(new Column("VIÑAK", "H4")); columns.Add(new Column("CORPORATIVO", "H5")); columns.Add(new Column("HUACAHUASI", "H6")); columns.Add(new Column("OFICINA CUSCO", "H7")); } foreach (Column col in columns) { _columns.Add(new Column("", col.accessor)); } if (req.PathVariables["mes"] == null || req.PathVariables["tipo"] == null || req.PathVariables["moneda"] == null) { return(new DataTable()); } int Ejercicio = req.PathVariables["periodo"] != null?Convert.ToInt32(req.PathVariables["periodo"]) : 2017; int EstablecimientoID = req.PathVariables["empresa"] != null?Convert.ToInt32(req.PathVariables["empresa"]) : 112; int Periodo = req.PathVariables["mes"] != null?Convert.ToInt32(req.PathVariables["mes"]) : 13; int TipoFormato = req.PathVariables["TipoFormatoGGPPID"] != null?Convert.ToInt32(req.PathVariables["TipoFormatoGGPPID"]) : 2; string Moneda = req.PathVariables["moneda"] != null ? req.PathVariables["moneda"] : "USD"; //Soles string sTipo = req.PathVariables["tipo"] != null ? req.PathVariables["tipo"] : "mensual"; //0 int Tipo = sTipo == "mensual" ? 1 : 0; bool isSoles = (Moneda == "USD" ? false : true); /* * exec UCO_Mayorizados_Select_PorMes_ComparativoPorHotel @TipoFormatoGGPPID=2,@TipoCuentaID=0,@Ejercicio=2018,@NivelID=0,@HotelID=0,@EstablecimientoID=112,@AreaID=0,@UnidadNegocioID=0,@Periodo='PeriodoID<=5',@OrderByExpression='',@FormatoReporte=2 */ var strSql = @"exec UCO_Mayorizados_Select_PorMes_ComparativoPorHotel @TipoFormatoGGPPID=" + TipoFormato + @",@TipoCuentaID= 0,@Ejercicio=" + Ejercicio + @", @NivelID = 0,@HotelID = " + HotelID + @",@EstablecimientoID = " + EstablecimientoID + @",@AreaID = 0,@UnidadNegocioID = 0, @Periodo = 'PeriodoID " + (Tipo == 1?"=":"<=") + Periodo + "',@OrderByExpression = '',@FormatoReporte = 2"; SqlResultWithDataSet oResult = oSql.Open(strSql, CommandType.Text); return(oResult.Results.Tables[0]); }
public string createPlantilla(string json) { createFilePlantilla plantilla = new createFilePlantilla(); try { plantilla = JsonConvert.DeserializeObject <createFilePlantilla>(json); }catch (Exception ex) { Console.WriteLine("JsonConvert" + ex.Message); } try { // Declare once, keep using var nTimeout = 30; // timeout var bLog = true; var connectionString = ShiolConfiguration.Instance.Config.SqlServerConnection.ConnectionString; // Create with a timeout of 30 seconds and logging as true var oSql = new Sql(connectionString, nTimeout, bLog); SqlResultWithDataSet oResult = oSql.Open("Select * from correlativos c Where nombretabla = 'PlantillasProcesadas'", CommandType.Text ); var plantillaProcesadaID = oResult.Results.Tables[0].Rows.Count == 1 ? oResult.Results.Tables[0].Rows[0]["numerogenerado"].ToString().Trim() : "0"; // string sql = "INSERT INTO PlantillasProcesadas (plantillaProcesadaID, tipoPlantillaID, rutaArchivo, fechaCarga, usuarioID, estado) VALUES("+ (Convert.ToInt32(plantillaProcesadaID) + 1) + ", '"+ plantilla.tipo + "', '"+ plantilla.tipo + "', @fecha, 1, '0'); " oResult = oSql.Open("INSERT INTO PlantillasProcesadas (plantillaProcesadaID, tipoPlantillaID, rutaArchivo, fechaCarga, usuarioID, estado) VALUES(@id, @tipo, @ruta, GETDATE(), 1, '0'); ", CommandType.Text, new SqlParameter("id", Convert.ToInt32(plantillaProcesadaID) + 1), new SqlParameter("tipo", plantilla.tipo), new SqlParameter("ruta", plantilla.files[0].title) ); oResult = oSql.Open("Update correlativos Set numerogenerado = @corr Where correlativoid = @id", CommandType.Text, new SqlParameter("id", 3103), new SqlParameter("corr", Convert.ToInt32(plantillaProcesadaID) + 1) ); plantilla.id = plantillaProcesadaID; }catch (Exception ex) { Console.WriteLine(ex.Message); } return(JsonConvert.SerializeObject(plantilla)); /*SELECT TOP 1000 [plantillaProcesadaID] * ,[fechaCarga] * ,[horaCarga] * ,[tipoPlantillaID] * ,[usuarioID] * ,[rutaArchivo] * ,[fechaProceso] * ,[horaProceso] * ,[usuarioIDProcesa] * ,[estado] * FROM[SHIOL_DATOS].[dbo].[PlantillasProcesadas] */ }
public bool processPlantillaExcel(string id) { // Declare once, keep using var nTimeout = 30; // timeout var bLog = true; PlantillaExcel plantilla = getPlantillaField(id); if (plantilla == null) { return(false); } string path = ShiolConfiguration.Instance.Config.DirPlantillas; //ConfigurationManager.AppSettings["dirPlantillas"]; //@"D:\SGH_Sistemas\"; var connectionString = ShiolConfiguration.Instance.Config.SqlServerConnection.ConnectionString; // Create with a timeout of 30 seconds and logging as true var oSql = new Sql(connectionString, nTimeout, bLog); string file = Path.Combine(path, plantilla.archivo); // @"D:\SGH_Sistemas\ShiolExcelService\ShiolWinSvc\FastExcel\FastExcelDemo\bin\Debug\SHIOL 1-31 FINAL.XLSX"; if (!File.Exists(file)) { Console.WriteLine("Archivo : " + file + " no existe"); return(false); } List <TableField> fields = getExcelStructure(file); if (fields.Count > 0) { string TableName = createTable(fields, oSql); Console.WriteLine("Generated : " + TableName); createRecords(file, fields, TableName, oSql); SqlResultWithDataSet oResult = oSql.Open("USP_Plantilla_Excel_CargaCuentasPorPagar_RH @nombreTabla = '" + TableName + "';", CommandType.Text ); /* * oResult = oSql.Open("IF OBJECT_ID('dbo."+TableName+"', 'U') IS NOT NULL DROP TABLE dbo."+TableName+"; ", * CommandType.Text * ); */ oResult = oSql.Open("Update PlantillasProcesadas Set estado = 2 Where plantillaProcesadaID = @id", CommandType.Text, new SqlParameter("id", id) ); Console.WriteLine("Processed : " + TableName); return(true); } else { Console.WriteLine("No Processed..."); return(false); } }
public string getListExcelFiles(Request req, Response res) { // Declare once, keep using var nTimeout = 30; // timeout var bLog = true; var connectionString = ShiolConfiguration.Instance.Config.SqlServerConnection.ConnectionString; // Create with a timeout of 30 seconds and logging as true var oSql = new Sql(connectionString, nTimeout, bLog); /*SELECT TOP 1000 [plantillaProcesadaID] * ,[fechaCarga] * ,[horaCarga] * ,[tipoPlantillaID] * ,[usuarioID] * ,[rutaArchivo] * ,[fechaProceso] * ,[horaProceso] * ,[usuarioIDProcesa] * ,[estado] * FROM[SHIOL_DATOS].[dbo].[PlantillasProcesadas] */ int Estado = req.PathVariables["estado"] != null?Convert.ToInt32(req.PathVariables["estado"]) : -1; int Tipo = req.PathVariables["tipo"] != null?Convert.ToInt32(req.PathVariables["tipo"]) : -1; string FechaCreacion = req.PathVariables["fecha_creacion"] != null ? req.PathVariables["fecha_creacion"].Substring(0, 10) : ""; string strWhere = ""; if (Tipo > -1) { strWhere = "Where " + "p.tipoPlantillaID" + "=" + Tipo; } if (Estado > -1) { strWhere += strWhere == "" ? "Where p.Estado ='" + Estado + "' " : " And p.Estado ='" + Estado + "' "; } if (FechaCreacion != "") { strWhere += strWhere == "" ? "Where FORMAT(fechaCarga, 'yyyy-MM-dd') ='" + FechaCreacion + "' " : " And FORMAT(fechaCarga, 'yyyy-MM-dd') ='" + FechaCreacion + "' "; } //OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY; string strCount = "0"; SqlResultWithDataSet oResult = null; try { var strSql = "SELECT count(*) as _total FROM PlantillasProcesadas p Left Join tipoPlantillas tp On tp.tipoPlantillaID = p.tipoPlantillaID " + strWhere; oResult = oSql.Open(strSql, CommandType.Text); strCount = oResult.Results.Tables[0].Rows[0]["_total"].ToString(); strSql = "SELECT tp.nombre, p.* FROM PlantillasProcesadas p Left Join tipoPlantillas tp On tp.tipoPlantillaID = p.tipoPlantillaID " + strWhere + " ORDER by p.fechaCarga DESC OFFSET " + req.PathVariables["_start"] + " ROWS FETCH NEXT " + (Convert.ToInt32(req.PathVariables["_end"]) - Convert.ToInt32(req.PathVariables["_start"])) + " ROWS ONLY;"; oResult = oSql.Open(strSql, CommandType.Text); }catch (Exception ex) { Console.WriteLine(ex.Message); } string json = null; List <PlantillaExcel> fields = new List <PlantillaExcel>(); try { foreach (DataRow row in oResult.Results.Tables[0].Rows) { PlantillaExcel field = new PlantillaExcel(row["plantillaProcesadaID"].ToString().Trim(), row["usuarioID"].ToString()); field.estado = row["estado"].ToString() == "2" ? "Procesado" : (row["estado"].ToString() == "1" ? "En Proceso" : "No Procesado"); field.tipo_nombre = row["nombre"].ToString(); field.tipo = row["tipoPlantillaID"].ToString(); field.archivo = row["rutaArchivo"].ToString(); field.fecha_creacion = row["fechaCarga"].ToString(); field.fecha_proceso = row["fechaProceso"].ToString(); fields.Add(field); } json = JsonConvert.SerializeObject(fields); } catch (Exception e) { Console.WriteLine(e.Message); } res.httpListenerResponse.Headers.Add("Access-Control-Expose-Headers", "X-Total-Count"); res.httpListenerResponse.Headers.Add("X-Total-Count", strCount); return(json); }
static void TranferenciaVentas() { DateTime dateDesde = new DateTime(2016, 01, 01); DateTime dateHasta = new DateTime(2016, 12, 30); String Usuario = "5093"; // Declare once, keep using var nTimeout = 30; // timeout var bLog = true; //var connectionString1 = "user id=sa;password=server$123$;data source=localhost;initial catalog=SHIOL_DATOS_MLP;Connection Timeout=30;"; var connectionString = ConfigurationManager.ConnectionStrings["cadena"].ConnectionString; // Create with a timeout of 30 seconds and logging as true var oSql = new Sql(connectionString, nTimeout, bLog); Console.WriteLine("Start : " + DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss.fff")); var watch = Stopwatch.StartNew(); var strSql = "Select valor from ParametrosGenerales where parametrogeneralID = '2010'"; SqlResultWithDataSet oResult = oSql.Open(strSql, CommandType.Text); var EstablecimientoID = oResult.Results.Tables[0].Rows.Count == 1 ? oResult.Results.Tables[0].Rows[0]["valor"].ToString().Trim() : ""; var Periodo = dateDesde.Year.ToString(); var NombreTablaCabecera = "ACC_" + EstablecimientoID + "_" + Periodo; var NombreTablaDetalle = "ACD_" + EstablecimientoID + "_" + Periodo; //Deleting Header and Detail Tables oSql.Open("Delete from " + NombreTablaCabecera + ";" + "Delete from " + NombreTablaDetalle, CommandType.Text ); // Creating Local Virtual Tables Header and Detail oResult = oSql.Open(@"SET FMTONLY ON; Select TOP 1 * from " + NombreTablaCabecera + ";" + "Select TOP 1 * from " + NombreTablaDetalle + "; SET FMTONLY OFF;", CommandType.Text ); DataTable tablaHeader = new DataTable(); DataTable tablaDetail = new DataTable(); if (oResult.Results.Tables.Count < 2) { Console.WriteLine("No se encuantran las tablas:" + NombreTablaCabecera + " o " + NombreTablaDetalle + " de este periodo en la base de datos"); } else { tablaHeader = oResult.Results.Tables[0]; tablaHeader.TableName = "ACC"; tablaDetail = oResult.Results.Tables[1]; tablaDetail.TableName = "ACD"; } SqlResultWithDataSet oResult1 = oSql.Open("UCOTRA_VENTAS_SQL", CommandType.StoredProcedure, new SqlParameter("Desde", dateDesde.ToString("dd-MM-yyyy")), new SqlParameter("Hasta", dateHasta.ToString("dd-MM-yyyy")), new SqlParameter("UsuarioID", "0"), new SqlParameter("Movimiento", "0"), new SqlParameter("IPEquipo", "0"), new SqlParameter("UsuarioactualEquipo", "0"), new SqlParameter("NombreEquipo", "0") ); DataSet dsTables = oResult1.Results; //DataSet dataSet = oResult.Results; dsTables.Tables[0].TableName = "Documentos"; DataTable tablaDocumentos = dsTables.Tables[0]; dsTables.Tables[1].TableName = "PlanDeCuentas"; DataTable tablaPlanDeCuentas = dsTables.Tables[1]; dsTables.Tables[2].TableName = "DetalleEventos"; DataTable tablaDetalleEventos = dsTables.Tables[2]; /* * //Establecer Relaciones * DataRelation planDeCuentasRel = dsTables.Relations.Add("PlanDeCuenta", * new DataColumn[] { dsTables.Tables["Documentos"].Columns["EstablecimientoID1"] , dsTables.Tables["Documentos"].Columns["PlanCuentaID"] }, * new DataColumn[] { dsTables.Tables["PlanDeCuentas"].Columns["EstablecimientoID"], dsTables.Tables["PlanDeCuentas"].Columns["PlanCuentaID"]}, false); */ // Console.WriteLine("Records : " + tablaDocumentos.Rows.Count.ToString()); /****************************************** * * Aquí empieza propiamente el proceso * ******************************************/ //Correlativos Para Formato de Comprobante Cabecera Int32 RegistroID = 0; // CorrelativoIDMensual = 0; Int32 CorrelativoIDMensualComprobanteAsiento = 0; Double TotalDebeCuadre = 0.00; Double TotalHaberCuadre = 0.00; Double TotalDebeSolesCuadre = 0.00; Double TotalDebeDolaresCuadre = 0.00; Double TotalHaberSolesCuadre = 0.00; Double TotalHaberDolaresCuadre = 0.00; Int32 AsientoContableID = 0; Int32 AsientoContableDetalleID = 0; String PlanCuentaID = ""; foreach (DataRow rowDocumento in dsTables.Tables["Documentos"].Rows) { String DocumentoId = rowDocumento["DocumentoId"].ToString(); // Console.WriteLine(rowDocumento["TransaccionDocumentoId"] + " - " + rowDocumento["TipoDocumentoID"]); Int32 EmpresaID = 0; if (Convert.ToDouble(rowDocumento["IGV"].ToString()) == 0 && Convert.ToInt32(rowDocumento["Turista"].ToString()) == 1) { EmpresaID = Convert.ToInt32(rowDocumento["EmpresaIDTuristas"].ToString()); } else { if (DocumentoId.Substring(0, 2) == "03" || DocumentoId.ToString().Substring(0, 2) == "123" || DocumentoId.ToString().Substring(0, 2) == "12B") { EmpresaID = Convert.ToInt32(rowDocumento["EmpresaIDParticular"].ToString()); } } /* * var intBack = (SqlResult)oSql.Execute("sp_ACC_ExisteRegistro", * CommandType.StoredProcedure, [ * new SqlParameter("NombreTabla", NombreTabla), * new SqlParameter("ReferenciaSHIOL", rowDocumento["TransaccionDocumentoID"].ToString().Trim()), * new SqlParameter("TablaMovimientoID", rowDocumento["TablaMovimientoID"].ToString().Trim()) * ] * ); */ var TablaMovimientoID = "137"; //Indica que tipo de provision de venta se Hace /* * strSql = "Select Count(*) FROM " + NombreTabla + " Where ReferenciaSHIOL = '" + rowDocumento["TransaccionDocumentoID"].ToString().Trim() + "' " + " AND TablaMovimientoID = '" + TablaMovimientoID + "'"; + + oResult = oSql.Open(strSql, CommandType.Text); + var LPRegistrosExiste = oResult.Results.Tables[0].Rows.Count == 1 ? true : false; + + + Console.WriteLine(LPRegistrosExiste.ToString()); */ //Console.WriteLine(rowDocumento["TransaccionDocumentoId"] + " - " + rowDocumento["TipoDocumentoID"]); // Console.WriteLine(EmpresaID.ToString()); // Console.WriteLine(rowDocumento["TransaccionDocumentoId"].ToString()); if (AsientoContableID == 0) { var intBack = oSql.Execute <Int32>("USP_GeneraID", CommandType.StoredProcedure, new SqlParameter("lpTabla", "ASIENTOSCONTABLES_" + Periodo), new SqlParameter("Codigo", "0")); AsientoContableID = intBack.Value; } else { AsientoContableID++; } if (AsientoContableDetalleID == 0) { var intBack = oSql.Execute <Int32>("USP_GeneraID", CommandType.StoredProcedure, new SqlParameter("lpTabla", "AsientosContablesDetalle_" + Periodo), new SqlParameter("Codigo", "0")); AsientoContableDetalleID = intBack.Value; } else { AsientoContableDetalleID++; } String CodigoComprobante = ""; if (rowDocumento["FormatoComprobante"].ToString().Trim() == "1") { if (RegistroID == 0) { //Console.WriteLine(rowDocumento["SubDiarioID"].ToString()); //Console.WriteLine(rowDocumento["FechaEmision"].ToString()); var intBack = oSql.Execute <Int32>("USP_GeneraIDMensual", CommandType.StoredProcedure, new SqlParameter("MemoID", "0"), new SqlParameter("UsuarioID", "0"), new SqlParameter("Formato", rowDocumento["SubDiarioID"].ToString()), new SqlParameter("Dia", rowDocumento["FechaEmision"].ToString()), new SqlParameter("Codigo", "0") ); RegistroID = intBack.Value; } else { RegistroID++; } DateTime FechaEmision = DateTime.Parse(rowDocumento["FechaEmision"].ToString()); CodigoComprobante = (FechaEmision.Month < 10 ? "0" : "") + FechaEmision.Month.ToString().Trim() + rowDocumento["HotelIDformato"].ToString() + rowDocumento["Localidad_CODIGO"].ToString() + "0000" + RegistroID.ToString().PadLeft(5, '0').Substring(RegistroID.ToString().PadLeft(5, '0').Length - 5, 5); // AsientoContableID = RegistroID; } else { if (CorrelativoIDMensualComprobanteAsiento == 0) { var intBack = oSql.Execute <Int32>("UCO_GeneraIDMensualComprobanteAsiento", CommandType.StoredProcedure, new SqlParameter("Periodo", rowDocumento["Periodo"].ToString()), new SqlParameter("Mensual", rowDocumento["Mes"].ToString()), new SqlParameter("EstablecimientoID", rowDocumento["EstablecimientoID"].ToString()), new SqlParameter("HotelID", rowDocumento["HotelID"].ToString()), new SqlParameter("SubDiarioID", rowDocumento["SubDiarioID"].ToString()), new SqlParameter("Codigo", "0") ); CorrelativoIDMensualComprobanteAsiento = intBack.Value; } else { CorrelativoIDMensualComprobanteAsiento++; } AsientoContableID = CorrelativoIDMensualComprobanteAsiento; } // Insertar en Tabla de Cabecera AddHeaderTable(AsientoContableID, CodigoComprobante, TablaMovimientoID, tablaHeader, rowDocumento); //Insertar Detalle Step 001 Double Total = Convert.ToDouble(rowDocumento["total"]); Double MontoDebe = Total > 0 ? Math.Round(Total, 2) : 0.00; Double MontoHaber = Total > 0 ? 0.00 : Math.Round(Total, 2); String Referencia = rowDocumento["Cliente"].ToString(); Double ImporteSoles = rowDocumento["Cliente"].ToString() == "1" ? Math.Round(MontoDebe + MontoHaber, 2) : Math.Round((MontoDebe + MontoHaber) * Convert.ToDouble(rowDocumento["TipoCambio"]), 2); Double ImporteDolares = rowDocumento["Cliente"].ToString() == "2" ? Math.Round(MontoDebe + MontoHaber, 2) : Math.Round(MontoDebe + MontoHaber, 2); String AsientoContableDetallIDTransferencia = "0"; TotalDebeCuadre += MontoDebe; TotalHaberCuadre += MontoHaber; if (MontoDebe > 0) { TotalDebeSolesCuadre += ImporteSoles; TotalDebeDolaresCuadre += ImporteDolares; } else { TotalHaberSolesCuadre += ImporteSoles; TotalHaberDolaresCuadre += ImporteDolares; } PlanCuentaID = rowDocumento["PlanCuentaID"].ToString(); MontoHaber = Convert.ToDouble(rowDocumento["Total"].ToString()) > 0 ? 0.00 : Convert.ToDouble(rowDocumento["Total"].ToString()); MontoDebe = Convert.ToDouble(rowDocumento["Total"].ToString()) > 0 ? Convert.ToDouble(rowDocumento["Total"].ToString()) : 0.00; ImporteSoles = rowDocumento["Cliente"].ToString() == "1" ? Math.Round(MontoDebe + MontoHaber, 2) : Math.Round((MontoDebe + MontoHaber) * Convert.ToDouble(rowDocumento["TipoCambio"]), 2); ImporteDolares = rowDocumento["Cliente"].ToString() == "2" ? Math.Round(MontoDebe + MontoHaber, 2) : Math.Round(MontoDebe + MontoHaber, 2); AddDetailTable(rowDocumento["Cliente"].ToString(), PlanCuentaID, AsientoContableID, AsientoContableDetalleID, MontoDebe, MontoHaber, ImporteSoles, ImporteDolares, TablaMovimientoID, tablaDetail, rowDocumento); if (Convert.ToInt32(rowDocumento["Estado"].ToString()) != 3) { if (Convert.ToDouble(rowDocumento["IGV"].ToString()) != 0.00) { PlanCuentaID = rowDocumento["CuentaIGV"].ToString(); Referencia = rowDocumento["Cliente"].ToString(); MontoDebe = Convert.ToDouble(rowDocumento["Total"].ToString()) > 0 ? 0.00 : Convert.ToDouble(rowDocumento["IGV"].ToString()); MontoHaber = Convert.ToDouble(rowDocumento["Total"].ToString()) > 0 ? Convert.ToDouble(rowDocumento["IGV"].ToString()) : 0.00; ImporteSoles = rowDocumento["Cliente"].ToString() == "1" ? Math.Round(MontoDebe + MontoHaber, 2) : Math.Round((MontoDebe + MontoHaber) * Convert.ToDouble(rowDocumento["TipoCambio"]), 2); ImporteDolares = rowDocumento["Cliente"].ToString() == "2" ? Math.Round(MontoDebe + MontoHaber, 2) : Math.Round(MontoDebe + MontoHaber, 2); AddDetailTable(Referencia, PlanCuentaID, AsientoContableID, AsientoContableDetalleID, MontoDebe, MontoHaber, ImporteSoles, ImporteDolares, TablaMovimientoID, tablaDetail, rowDocumento); } if (Convert.ToDouble(rowDocumento["Servicio"].ToString()) != 0.00) { PlanCuentaID = rowDocumento["CuentaServicio"].ToString();; Referencia = rowDocumento["Cliente"].ToString(); MontoHaber = Convert.ToDouble(rowDocumento["Total"].ToString()) > 0 ? 0.00 : Convert.ToDouble(rowDocumento["Servicio"].ToString()); MontoDebe = Convert.ToDouble(rowDocumento["Total"].ToString()) > 0 ? Convert.ToDouble(rowDocumento["Servicio"].ToString()):0.00; ImporteSoles = rowDocumento["Cliente"].ToString() == "1" ? Math.Round(MontoDebe + MontoHaber, 2) : Math.Round((MontoDebe + MontoHaber) * Convert.ToDouble(rowDocumento["TipoCambio"]), 2); ImporteDolares = rowDocumento["Cliente"].ToString() == "2" ? Math.Round(MontoDebe + MontoHaber, 2) : Math.Round(MontoDebe + MontoHaber, 2); AddDetailTable(Referencia, PlanCuentaID, AsientoContableID, AsientoContableDetalleID, MontoDebe, MontoHaber, ImporteSoles, ImporteDolares, TablaMovimientoID, tablaDetail, rowDocumento); } if (Convert.ToDouble(rowDocumento["Propina"].ToString()) != 0.00) { PlanCuentaID = rowDocumento["CuentaPropina"].ToString(); Referencia = rowDocumento["Cliente"].ToString(); MontoHaber = Convert.ToDouble(rowDocumento["Total"].ToString()) > 0 ? 0.00 : Convert.ToDouble(rowDocumento["Propina"].ToString()); MontoDebe = Convert.ToDouble(rowDocumento["Total"].ToString()) > 0 ? Convert.ToDouble(rowDocumento["Propina"].ToString()) : 0.00; ImporteSoles = rowDocumento["Cliente"].ToString() == "1" ? Math.Round(MontoDebe + MontoHaber, 2) : Math.Round((MontoDebe + MontoHaber) * Convert.ToDouble(rowDocumento["TipoCambio"]), 2); ImporteDolares = rowDocumento["Cliente"].ToString() == "2" ? Math.Round(MontoDebe + MontoHaber, 2) : Math.Round(MontoDebe + MontoHaber, 2); AddDetailTable(Referencia, PlanCuentaID, AsientoContableID, AsientoContableDetalleID, MontoDebe, MontoHaber, ImporteSoles, ImporteDolares, TablaMovimientoID, tablaDetail, rowDocumento); } if (Convert.ToInt32(rowDocumento["Prepago"].ToString()) == 1 && Convert.ToInt32(rowDocumento["PrepagosNoConsiderarVentas"].ToString()) == 1) { Int32 MonedaId = Convert.ToInt32(rowDocumento["MonedaId"].ToString()); if (Convert.ToInt32(rowDocumento["Vinculada"].ToString()) == 1 && (DocumentoId.Substring(0, 2) == "01" || DocumentoId.Substring(0, 2) == "08") && Convert.ToInt32(rowDocumento["Turista"].ToString()) == 0) { if (MonedaId == 1) { PlanCuentaID = Convert.ToString(rowDocumento["PlanCuentaIDVinculadaAnticipoSoles"]); } else { PlanCuentaID = Convert.ToString(rowDocumento["PlanCuentaIDVinculadaAnticipoDolares"]); } } else { if (MonedaId == 1) { PlanCuentaID = Convert.ToString(rowDocumento["FacturaCuentaSolesPrepago"]); } else { PlanCuentaID = Convert.ToString(rowDocumento["FacturaCuentaDolaresPrepago"]); } Referencia = "ANTICIPO"; //Console.WriteLine("ANTICIPO"); ImporteDolares = MonedaId == 1 ? Math.Round(MontoDebe + MontoHaber, 2) : Math.Round((MontoDebe + MontoHaber) * Convert.ToDouble(rowDocumento["TipoCambio"].ToString()), 2); ImporteSoles = MonedaId == 2 ? Math.Round(MontoDebe + MontoHaber, 2) : Math.Round((MontoDebe + MontoHaber) * Convert.ToDouble(rowDocumento["TipoCambio"].ToString()), 2); if (MontoDebe > 0) { ; // Poner cuadre completar } else { } Double Diferencia = Math.Round(TotalDebeCuadre - TotalHaberCuadre, 2); Double DiferenciaSoles = Math.Round(TotalDebeSolesCuadre - TotalHaberSolesCuadre, 2); Double DiferenciaDolares = Math.Round(TotalDebeDolaresCuadre - TotalHaberDolaresCuadre, 2); if (Math.Abs(Diferencia) < 0.05 || Math.Abs(DiferenciaSoles) < 0.05 || Math.Abs(DiferenciaDolares) < 0.05) { } //PlanCuentaID = Pla // Añadir Aquí registro // Console.WriteLine("Adding Record"); AddDetailTable(Referencia, PlanCuentaID, AsientoContableID, AsientoContableDetalleID, MontoDebe, MontoHaber, ImporteSoles, ImporteDolares, TablaMovimientoID, tablaDetail, rowDocumento); } } else { //Console.WriteLine(" DETALLE DE EVENTOS"); var query = tablaDetalleEventos.AsEnumerable().Where(d => d.Field <String>("DocumentoId") == DocumentoId); foreach (var row in query) { Int32 MonedaID = Convert.ToInt32(row["MonedaIDPaidOut"].ToString()); if (Convert.ToDouble(row["TotalPaidOut"].ToString()) != 0.00) { //Console.WriteLine(row["DocumentoId"].ToString()); PlanCuentaID = row["PlanCuentaID"].ToString(); Referencia = rowDocumento["Cliente"].ToString(); MontoHaber = Convert.ToDouble(rowDocumento["TotalPaidOut"].ToString()) > 0 ? 0.00 : Convert.ToDouble(rowDocumento["TotalPaidOut"].ToString()); MontoDebe = Convert.ToDouble(rowDocumento["TotalPaidOut"].ToString()) > 0 ? 0.00 :Math.Round(Convert.ToDouble(row["TotalPaidOut"].ToString()), 2); ImporteSoles = MonedaID == 1 ? Math.Round(MontoDebe + MontoHaber, 2) : Math.Round((MontoDebe + MontoHaber) * Convert.ToDouble(rowDocumento["TipoCambio"]), 2); ImporteDolares = MonedaID == 2 ? Math.Round(MontoDebe + MontoHaber, 2) : Math.Round(MontoDebe + MontoHaber, 2); AddDetailTable(Referencia, PlanCuentaID, AsientoContableID, AsientoContableDetalleID, MontoDebe, MontoHaber, ImporteSoles, ImporteDolares, TablaMovimientoID, tablaDetail, rowDocumento); } MontoDebe = Convert.ToDouble(row["TotalTipoProducto"].ToString()) > 0 ? 0.00 : Math.Round(Convert.ToDouble(row["TotalTipoProducto"].ToString()), 2); MontoHaber = Convert.ToDouble(row["TotalTipoProducto"].ToString()) < 0 ? 0.00 : Math.Round(Convert.ToDouble(row["TotalTipoProducto"].ToString()), 2); Referencia = row["nombre"].ToString(); ImporteSoles = MonedaID == 1 ? Math.Round(MontoDebe + MontoHaber, 2) : Math.Round((MontoDebe + MontoHaber) * Convert.ToDouble(rowDocumento["TipoCambio"]), 2); ImporteDolares = MonedaID == 2 ? Math.Round(MontoDebe + MontoHaber, 2) : Math.Round((MontoDebe + MontoHaber) / Convert.ToDouble(rowDocumento["TipoCambio"]), 2); Double Diferencia = Math.Round(TotalDebeCuadre - TotalHaberCuadre, 2); Double DiferenciaSoles = Math.Round(TotalDebeSolesCuadre - TotalHaberSolesCuadre, 2); Double DiferenciaDolares = Math.Round(TotalDebeDolaresCuadre - TotalHaberDolaresCuadre, 2); if (Math.Abs(Diferencia) < 0.05 || Math.Abs(DiferenciaSoles) < 0.05 || Math.Abs(DiferenciaDolares) < 0.05) { if (MontoHaber > 0.00) { MontoHaber += Diferencia; ImporteSoles += DiferenciaSoles; ImporteDolares += DiferenciaDolares; } if (MontoDebe > 0.00) { if (Diferencia > 0) { Diferencia *= -1; } else { Diferencia = Math.Abs(Diferencia); } if (DiferenciaSoles > 0) { DiferenciaSoles *= -1; } else { DiferenciaSoles = Math.Abs(DiferenciaSoles); } if (DiferenciaDolares > 0.00) { DiferenciaDolares *= -1; } else { DiferenciaDolares = Math.Abs(DiferenciaDolares); } MontoDebe += Diferencia; ImporteSoles += DiferenciaSoles; ImporteDolares += DiferenciaDolares; } } PlanCuentaID = row["PlanCuentaID"].ToString(); Referencia = row["nombre"].ToString(); AddDetailTable(Referencia, PlanCuentaID, AsientoContableID, AsientoContableDetalleID, MontoDebe, MontoHaber, ImporteSoles, ImporteDolares, TablaMovimientoID, tablaDetail, rowDocumento); } } } } bulkCopy(connectionString, NombreTablaCabecera, tablaHeader); bulkCopy(connectionString, NombreTablaDetalle, tablaDetail); watch.Stop(); Console.WriteLine("Total Time:" + watch.ElapsedMilliseconds / 1000); Console.WriteLine("End : " + DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss.fff")); Console.ReadKey(); }
static void Test() { // Declare once, keep using var nTimeout = 30; // timeout var bLog = true; var connectionString = "user id=sa;password=server$123$;data source=localhost;initial catalog=Shiol_Datos_MLP;Connection Timeout=30;"; // Create with a timeout of 30 seconds and logging as true var oSql = new Sql(connectionString, nTimeout, bLog); // Now let's roll! SqlResultWithDataSet oResult = oSql.Open("SELECT * FROM ParametrosGenerales"); DataSet dataSet = oResult.Results; //// I'm going to re-use oSql and oResult from above ^^^ oResult = oSql.Open("SELECT * FROM ParametrosGenerales WHERE Estado = @id", CommandType.Text, new SqlParameter("id", 1) ); //SQLMagic makes it incredibly simple to write proper queries: /* * oResult = oSql.Open("SELECT * FROM tbl WHERE tbl_id IN (@id1, @id2, @id3)", * CommandType.Text, * new SqlParameter("id1", 1), * new SqlParameter("id2", 2), * new SqlParameter("id3", 3) * ); */ // Stored Procedures with parameters? oSql.Execute("sp_Productos", CommandType.StoredProcedure, new SqlParameter("TipoProductoID", "1224") ); var intBack = (SqlResult)oSql.Execute("sp_Productos", CommandType.StoredProcedure, new SqlParameter("TipoProductoID", "1224") ); // What about return values? /* var datetime = oSql.Execute<DateTime>("SELECT GETDATE()"); * var intBack = oSql.Execute<Int32>("SELECT 1");*/ // Stored Procedure that has a return value? var intBackAgain = oSql.Execute <Int32>("sp_Productos", CommandType.StoredProcedure); //Asynchronous Support //SQLMagic, with compilation flag "NET45" set, supports the async/await model of.NET, and implements it in a very clean, easy to use manner: // Converting is VERY easy! #if NET45 var oResult = await oSql.OpenAsync("SELECT * FROM aVeryLargeTable"); var nValue = await oSql.ExecuteAsync <Int32>("SELECT bigNumberComputationThatResultsInInteger"); var nProcedure = await oSql.ExecuteAsync <Int32>("sp_LongRunningProcedure", CommandType.StoredProcedure); #endif //Transaction Support //SQLMagic gives you the ability to Begin, Commit, and RollBack transactions with and without asychronous capability: // Converting is VERY easy! #if NET45 // Start a Transaction: SqlTransaction oTransaction = oSql.BeginTransaction(); // The asynchronous version: SqlTransaction oTransaction = await oSql.BeginTransactionAsync(); // one of the overloads that SQLMagic has is the ability to specify a transaction! // public SqlResultWithDataSet Open(Statement aStatement, SqlConnection aConnection, Boolean aCloseConnection, SqlTransaction aTransaction) // Statement is merely a SQLMagic struct that groups up some parameters from earlier: Statement oStatement = new Statement { Sql = "INSERT INTO tbl VALUES(@val1)", Type = CommandType.Text, Parameters = new List <SqlParameters>() { new SqlParameter("val1", "value") } }; oSql.Open(oStatement, oTransaction.Connection, false, oTransaction); // Open a DataSet of the given statement, connection, don't close it, and use that transaction // Once you're all done, you end! oSql.EndTransaction(oTransaction, true); // true or false indicates COMMIT or ROLLBACK! // The asynchronous version: await oSql.EndTransactionAsync(oTransaction); #endif //Manual Connection Creation //SQLMagic lets you create connections yourself(remember that SQLMagic has overloads to indicate a SqlConnection object!): // Synchronous SqlConnection oConnection = oSql.CreateConnection(true); // Converting is VERY easy! #if NET45 // Asynchronous SqlConnection oConnection = await oSql.CreateConnectionAsync(true); #endif // The boolean value indicates whether or not the connection is automatically opened when created //TryOpen/TryExecute/TryExecute<T> //SQLMagic can attempt to execute a query for you and, instead of throwing an exception, will simply return a Boolean value that indicates success, and use an out parameter to store your result: // Variables //SqlResultWithDataSet oResult; if (oSql.TryOpen("SELECT * FROM tbl WHER tbl.id = 1", out oResult)) { // This fails because of syntax ^ } else { // Because it failed, you can handle the exception here! //MessageBox.Show(oResult.Exception.ToString()); } }
/// <summary> /// Tries to create a DataSet object. /// The resulting boolean indicates whether or not the query execution was successful /// </summary> /// <param name="aSqlStatement">The SQL statement to execute and return a DataSet on</param> /// <param name="aCommandType">The type of SQL statement being passed</param> /// <param name="aResult">The result object that contains the DataSet and potential Exception object</param> /// <returns>A boolean result that indicates whether or not the query was able to be successfully executed</returns> /// <seealso cref="System.Data.CommandType"/> /// <seealso cref="Items.Results.SqlResultWithDataSet"/> public Boolean TryOpen(String aSqlStatement, CommandType aCommandType, out SqlResultWithDataSet aResult) { return(TryOpen(aSqlStatement, aCommandType, null, out aResult)); }
/// <summary> /// Creates a DataSet that contains zero or more DataTables based on the SQL Statement passed /// </summary> /// <param name="aStatement">The SQL statement structure to open</param> /// <param name="aConnection">The connection to use for this SQL statement</param> /// <param name="aCloseConnection">A boolean flag that indicates whether or not the /// connection should be closed after the statement is executed</param> /// <param name="aTransaction">The SqlTransaction object that this connection is part of</param> /// <returns>A DataSet that contains a set of DataTables</returns> /// <seealso cref="System.Data.DataSet"/> /// <seealso cref="Sql.Statement"/> /// <seealso cref="System.Data.SqlClient.SqlConnection"/> /// <seealso cref="System.Data.SqlClient.SqlTransaction"/> public SqlResultWithDataSet Open(Statement aStatement, SqlConnection aConnection, Boolean aCloseConnection, SqlTransaction aTransaction) { // Create the connection SqlDataReader oReader = null; DataSet oDataSet = null; SqlResultWithDataSet oResult = new SqlResultWithDataSet(); DataTable oSchema = null; // Create a command SqlCommand oCommand = new SqlCommand { CommandText = aStatement.Sql, CommandType = aStatement.Type, CommandTimeout = iTimeout, Connection = aConnection, Transaction = aTransaction }; // Add the parameters if (aStatement.Parameters != null) { oCommand.Parameters.AddRange(aStatement.Parameters.ToArray()); } // Do a log of the command being executed if (iLogging) { } // Set back oCommand.CommandText = aStatement.Sql; try { // Store the reader oReader = oCommand.ExecuteReader(aCloseConnection ? CommandBehavior.CloseConnection : CommandBehavior.Default); // Schema // oResult.DataTableSchema = oReader.GetSchemaTable(); // Set the statistics oResult.Statistics = aConnection.RetrieveStatistics(); // Post-Execution if (iLogging) { } // Convert oDataSet = oReader.ToDataSet(); // Set oResult.Results = oDataSet; // Close! oReader.Close(); // Give back the result set return(oResult); } catch (Exception) { // ToDo: Log the error here but rethrow the error! throw; } finally { // Close the connection if necessary: if (aCloseConnection && aConnection.State == ConnectionState.Open && aConnection != null) { aConnection.Close(); aConnection.Dispose(); } } }
public void createRecords(string FilePath, List <TableField> fields, string tableName, Sql oSql) { DateTimeFormatInfo DateTsysFormat = CultureInfo.CurrentCulture.DateTimeFormat; FileInfo existingFile = new FileInfo(FilePath); Console.WriteLine(FilePath); using (ExcelPackage package = new ExcelPackage(existingFile)) { // get the first worksheet in the workbook ExcelWorksheet worksheet = package.Workbook.Worksheets["RH"]; //INSERT INTO table_name (column1, column2, column3, ...) //VALUES(value1, value2, value3, ...); string strCommand = "INSERT INTO " + tableName; string strFields = " ("; int x = 0; foreach (var field in fields) { strFields += (x > 0 ? "," : "") + field.name; x++; } strFields += ")"; strCommand += strFields + " "; Boolean header = false; for (int row = 1; row < worksheet.Cells.Rows; row++) { if (!header) { header = true; continue; } string strValues = "VALUES("; x = 0; string crc = ""; for (int col = 1; col <= fields.Count; col++) { //Console.WriteLine(fields[cell.ColumnNumber - 1].Name + " -- " + cell.Value.ToString() + " -- " + cell.GetType()); string value = worksheet.Cells[row, col].Value == null ? "" : worksheet.Cells[row, col].Value.ToString().Trim(); DateTime result; if (IsDate(value, out result)) { // do something with result value = result.ToString("yyyyMMdd hh:mm:ss"); // ("yyyy-MM-dd HH:mm:ss.fff"); // Console.WriteLine(value); } strValues += (x > 0 ? "," : "") + "'" + value + "'"; //.Replace("\"","").Replace("'","") x++; crc += value; if (x == fields.Count) { break; } } strValues += ")"; if (crc == "") { return; } string strCommandExec = strCommand + strValues; try { SqlResultWithDataSet oResult = oSql.Open(strCommandExec, CommandType.Text); } catch (Exception ex) { Console.WriteLine(ex.Message + "----" + strCommandExec); } } // Read the rows using the worksheet index // Worksheet indexes are start at 1 not 0 // This method is slightly faster to find the underlying file (so slight you probably wouldn't notice) //worksheet = fastExcel.Read(1); } }