public void r101Implementation(SystemMenuModel instance) { System.Collections.ArrayList mass = new System.Collections.ArrayList(); System.Collections.ArrayList key1 = new System.Collections.ArrayList(); System.Collections.ArrayList key2 = new System.Collections.ArrayList(); System.Collections.ArrayList key3 = new System.Collections.ArrayList(); using (SqlText sql = new SqlText(String.Format("SELECT PageName, PageDescription, PageRoles, PageUrl FROM SystemMenu"))) { System.Data.Common.DbDataReader readerSender = sql.ExecuteReader(); while (readerSender.Read()) { //Web.Sitemap XmlDocument xmlDoc = new XmlDocument(); xmlDoc.Load(HttpContext.Current.Server.MapPath("~/Web.sitemap")); XmlNodeList elementList = xmlDoc.GetElementsByTagName("siteMapNode"); foreach (XmlNode node in elementList) { if (node.Attributes["title"].Value == readerSender["PageName"].ToString()) { XmlAttribute description = xmlDoc.CreateAttribute("description"); node.Attributes.Append(description); node.Attributes["description"].Value = readerSender["PageDescription"].ToString(); XmlAttribute url = xmlDoc.CreateAttribute("url"); node.Attributes.Append(url); node.Attributes["url"].Value = readerSender["PageUrl"].ToString(); XmlAttribute roles = xmlDoc.CreateAttribute("roles"); node.Attributes.Append(roles); node.Attributes["roles"].Value = readerSender["PageRoles"].ToString(); xmlDoc.Save(HttpContext.Current.Server.MapPath("~/Web.sitemap")); } } //Web.config xmlDoc = new XmlDocument(); xmlDoc.Load(HttpContext.Current.Server.MapPath("~/web.config")); bool pathXml = true; elementList = xmlDoc.GetElementsByTagName("location"); foreach (XmlNode node in elementList) { if (node.Attributes["path"].Value == readerSender["PageUrl"].ToString().Substring(2)) { //node.InnerXml pathXml = false; string rp = "<system.web><authorization><allow roles=\"" + readerSender["PageRoles"].ToString() + "\" /><deny users=\" * \" /></authorization></system.web>"; node.InnerXml = rp; xmlDoc.Save(HttpContext.Current.Server.MapPath("~/web.config")); } } if (pathXml && readerSender["PageRoles"].ToString() != "*") { XmlElement locationNode = xmlDoc.CreateElement("location"); locationNode.SetAttribute("path", readerSender["PageUrl"].ToString().Substring(2)); XmlNodeList elementListConfig = xmlDoc.GetElementsByTagName("configuration"); XmlElement swNode = xmlDoc.CreateElement("system.web"); locationNode.AppendChild(swNode); XmlElement authorizationNode = xmlDoc.CreateElement("authorization"); swNode.AppendChild(authorizationNode); XmlElement allowNode = xmlDoc.CreateElement("allow"); allowNode.SetAttribute("roles", readerSender["PageRoles"].ToString()); authorizationNode.AppendChild(allowNode); XmlElement denyNode = xmlDoc.CreateElement("deny"); denyNode.SetAttribute("users", " * "); authorizationNode.AppendChild(denyNode); foreach (XmlNode nodeConfig in elementListConfig) { nodeConfig.AppendChild(locationNode); xmlDoc.Save(HttpContext.Current.Server.MapPath("~/web.config")); } } } readerSender.Close(); readerSender.Dispose(); } }
} // End Sub WriteArray public virtual void SerializeDataTableAsAssociativeJsonArray(System.Data.Common.DbCommand cmd, System.IO.Stream output, bool pretty, System.Text.Encoding enc) { Newtonsoft.Json.JsonSerializer ser = new Newtonsoft.Json.JsonSerializer(); using (System.IO.TextWriter sw = new System.IO.StreamWriter(output, enc)) { using (Newtonsoft.Json.JsonTextWriter jsonWriter = new Newtonsoft.Json.JsonTextWriter(sw)) { if (pretty) { jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented; } // jsonWriter.WriteStartObject(); // jsonWriter.WritePropertyName("tables"); // jsonWriter.WriteStartArray(); using (System.Data.Common.DbConnection con = this.Connection) { cmd.Connection = con; if (con.State != System.Data.ConnectionState.Open) { con.Open(); } try { using (System.Data.Common.DbDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess | System.Data.CommandBehavior.CloseConnection )) { do { // jsonWriter.WriteStartObject(); // tbl = new Table(); //jsonWriter.WritePropertyName("columns"); //// WriteArray(jsonWriter, dr); //WriteAssociativeArray(jsonWriter, dr); //jsonWriter.WritePropertyName("rows"); jsonWriter.WriteStartArray(); if (dr.HasRows) { string[] columns = new string[dr.FieldCount]; for (int i = 0; i < dr.FieldCount; i++) { columns[i] = dr.GetName(i); } // Next i while (dr.Read()) { object[] thisRow = new object[dr.FieldCount]; // jsonWriter.WriteStartArray(); // object[] thisRow = new object[dr.FieldCount]; jsonWriter.WriteStartObject(); // tbl = new Table(); for (int i = 0; i < dr.FieldCount; ++i) { jsonWriter.WritePropertyName(columns[i]); object obj = dr.GetValue(i); if (obj == System.DBNull.Value) { obj = null; } jsonWriter.WriteValue(obj); } // Next i // jsonWriter.WriteEndArray(); // tbl.Rows.Add(thisRow); jsonWriter.WriteEndObject(); } // Whend } // End if (dr.HasRows) jsonWriter.WriteEndArray(); // jsonWriter.WriteEndObject(); // ser.Tables.Add(tbl); } while (dr.NextResult()); } // End using dr } catch (System.Exception ex) { System.Console.WriteLine(ex.Message); throw; } if (con.State != System.Data.ConnectionState.Closed) { con.Close(); } } // End using con // jsonWriter.WriteEndArray(); // jsonWriter.WriteEndObject(); jsonWriter.Flush(); } // End Using jsonWriter } // End Using sw } // End Sub SerializeDataTableAsAssociativeJsonArray
} // End Function GetClass public virtual System.Collections.Generic.List <T> GetList <T>(System.Data.IDbCommand cmd) { System.Collections.Generic.List <T> lsReturnValue = new System.Collections.Generic.List <T>(); T tThisValue = default(T); System.Type tThisType = typeof(T); lock (cmd) { using (System.Data.Common.DbDataReader rdr = ExecuteReader(cmd)) { lock (rdr) { if (IsSimpleType(tThisType)) { while (rdr.Read()) { object objVal = rdr.GetValue(0); // tThisValue = System.Convert.ChangeType(objVal, T), tThisValue = (T)ConvertResult <T>(objVal, true); lsReturnValue.Add(tThisValue); } // End while (idr.Read()) } else { int iFieldCount = rdr.FieldCount; Setter_t <T>[] mems = new Setter_t <T> [iFieldCount]; for (int i = 0; i < iFieldCount; ++i) { string strName = rdr.GetName(i); mems[i] = LinqHelper.GetSetter <T>(strName); } // Next i while (rdr.Read()) { tThisValue = System.Activator.CreateInstance <T>(); for (int i = 0; i < iFieldCount; ++i) { Setter_t <T> setter = mems[i]; if (setter != null) { object objVal = rdr.GetValue(i); setter(tThisValue, objVal); } } // Next i lsReturnValue.Add(tThisValue); } // Whend } // End if IsSimpleType(tThisType) rdr.Close(); } // End Lock rdr } // End Using rdr } // End lock cmd return(lsReturnValue); } // End Function GetList
} // End Sub MultipleLargeDataSets public static void MultipleLargeDataSets(System.IO.Stream strm, string strSQL) { Newtonsoft.Json.JsonSerializer serializer = new Newtonsoft.Json.JsonSerializer(); using (System.IO.StreamWriter output = new System.IO.StreamWriter(strm)) { using (Newtonsoft.Json.JsonTextWriter jsonWriter = new Newtonsoft.Json.JsonTextWriter(output)) { jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented; jsonWriter.WriteStartObject(); jsonWriter.WritePropertyName("Tables"); jsonWriter.WriteStartArray(); using (System.Data.Common.DbDataReader dr = SQL.ExecuteReader(strSQL , System.Data.CommandBehavior.CloseConnection | System.Data.CommandBehavior.SequentialAccess )) { do { jsonWriter.WriteStartObject(); // tbl = new Table(); jsonWriter.WritePropertyName("Columns"); jsonWriter.WriteStartArray(); for (int i = 0; i < dr.FieldCount; ++i) { jsonWriter.WriteStartObject(); jsonWriter.WritePropertyName("ColumnName"); jsonWriter.WriteValue(dr.GetName(i)); jsonWriter.WritePropertyName("FieldType"); jsonWriter.WriteValue(dr.GetFieldType(i).AssemblyQualifiedName); jsonWriter.WriteEndObject(); } // Next i jsonWriter.WriteEndArray(); jsonWriter.WritePropertyName("Rows"); jsonWriter.WriteStartArray(); if (dr.HasRows) { while (dr.Read()) { object[] thisRow = new object[dr.FieldCount]; jsonWriter.WriteStartArray(); // object[] thisRow = new object[dr.FieldCount]; for (int i = 0; i < dr.FieldCount; ++i) { jsonWriter.WriteValue(dr.GetValue(i)); } // Next i jsonWriter.WriteEndArray(); // tbl.Rows.Add(thisRow); } // Whend } // End if (dr.HasRows) jsonWriter.WriteEndArray(); jsonWriter.WriteEndObject(); // ser.Tables.Add(tbl); } while (dr.NextResult()); } // End Using dr jsonWriter.WriteEndArray(); jsonWriter.WriteEndObject(); jsonWriter.Flush(); output.Flush(); output.BaseStream.Flush(); output.Close(); // context.Response.Output.Flush(); // context.Reponse.OutputStream.Flush(); // context.Response.Flush(); } // End Using jsonWriter } // End using output } // End Sub MultipleLargeDataSets
public IEnumerable <DataTransferObject.Foundation.BasicPriceView> QueryBasicPrice(string airline, string departure, string arrival, Core.Pagination pagination) { IList <DataTransferObject.Foundation.BasicPriceView> result = new List <DataTransferObject.Foundation.BasicPriceView>(); string fields = "[Id],[Airline],[Departure],[Arrival],[FlightDate],[ETDZDate],[Price],[Mileage],ModifyTime"; string catelog = "[dbo].[T_BasicPrice]"; string orderbyFiled = "[Airline] DESC,[FlightDate] DESC,[Departure],[Arrival],ModifyTime desc"; string where = ""; if (!string.IsNullOrWhiteSpace(airline)) { where += "([Airline] IS NULL OR [Airline] = '" + airline + "') AND "; } if (!string.IsNullOrWhiteSpace(departure) || !string.IsNullOrWhiteSpace(arrival)) { if (!string.IsNullOrWhiteSpace(departure) && string.IsNullOrWhiteSpace(arrival)) { where += "([Departure] = '" + departure + "' OR [Arrival] = '" + departure + "') AND "; } if (!string.IsNullOrWhiteSpace(arrival) && string.IsNullOrWhiteSpace(departure)) { where += "([Departure] = '" + arrival + "' OR [Arrival] = '" + arrival + "') AND "; } if (!string.IsNullOrWhiteSpace(arrival) && !string.IsNullOrWhiteSpace(departure)) { where += "(([Departure] = '" + arrival + "' AND [Arrival] = '" + departure + "') OR ([Departure] = '" + departure + "' AND [Arrival] = '" + arrival + "')) AND "; } } if (where.Length > 0) { where = where.Remove(where.Length - 5, 5); } using (DbOperator dbOperator = new DbOperator(Provider, ConnectionString)) { dbOperator.AddParameter("@iField", fields); dbOperator.AddParameter("@iCatelog", catelog); dbOperator.AddParameter("@iCondition", where); dbOperator.AddParameter("@iOrderBy", orderbyFiled); dbOperator.AddParameter("@iPagesize", pagination.PageSize); dbOperator.AddParameter("@iPageIndex", pagination.PageIndex); dbOperator.AddParameter("@iGetCount", pagination.GetRowCount); System.Data.Common.DbParameter totalCount = dbOperator.AddParameter("@oTotalCount"); totalCount.DbType = System.Data.DbType.Int32; totalCount.Direction = System.Data.ParameterDirection.Output; using (System.Data.Common.DbDataReader reader = dbOperator.ExecuteReader("dbo.P_Pagination", System.Data.CommandType.StoredProcedure)) { while (reader.Read()) { DataTransferObject.Foundation.BasicPriceView basicPriceView = new DataTransferObject.Foundation.BasicPriceView(); basicPriceView.Id = reader.GetGuid(0); basicPriceView.Airline = reader.IsDBNull(1) ? string.Empty : reader.GetString(1); basicPriceView.Departure = reader.GetString(2); basicPriceView.Arrival = reader.GetString(3); basicPriceView.FlightDate = reader.GetDateTime(4); basicPriceView.ETDZDate = reader.GetDateTime(5); basicPriceView.Price = reader.GetDecimal(6); basicPriceView.Mileage = reader.GetDecimal(7); basicPriceView.ModifyTime = reader.IsDBNull(8) ? new DateTime(2013, 10, 26, 0, 0, 0) : reader.GetDateTime(8); result.Add(basicPriceView); } } if (pagination.GetRowCount) { pagination.RowCount = (int)totalCount.Value; } } return(result); }
} // End Sub SaveAssembly public static void SaveAssembly2(string assemblyName, string destinationPath) { string sql = @"SELECT af.name, af.content FROM sys.assemblies a INNER JOIN sys.assembly_files af ON a.assembly_id = af.assembly_id WHERE a.name = @assemblyname"; using (System.Data.Common.DbConnection conn = new System.Data.SqlClient.SqlConnection("context connection=true")) //Create current context connection { using (System.Data.Common.DbCommand cmd = new System.Data.SqlClient.SqlCommand(sql, (System.Data.SqlClient.SqlConnection)conn)) { System.Data.Common.DbParameter param = new System.Data.SqlClient.SqlParameter("@assemblyname", System.Data.SqlDbType.NVarChar); param.Value = assemblyName; // param.Size = 128; cmd.Parameters.Add(param); using (System.IO.Stream fs = new System.IO.FileStream("logo" + "pub_id" + ".bmp", System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.Write)) { using (System.IO.BinaryWriter bw = new System.IO.BinaryWriter(fs)) { long startIndex = 0; var buffer = new byte[1024]; int bufferSize = buffer.Length; if (cmd.Connection.State != System.Data.ConnectionState.Open) { cmd.Connection.Open(); //Open the context connetion } using (System.Data.Common.DbDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) //Iterate through assembly files { string assemblyFileName = reader.GetString(0); //get assembly file name from the name (first) column long retval = reader.GetBytes(1, startIndex, buffer, 0, bufferSize); // Continue reading and writing while there are bytes beyond the size of the buffer. while (retval == bufferSize) { bw.Write(buffer); bw.Flush(); // Reposition the start index to the end of the last buffer and fill the buffer. startIndex += bufferSize; retval = reader.GetBytes(1, startIndex, buffer, 0, bufferSize); } // Whend // Write the remaining buffer. bw.Write(buffer, 0, (int)retval); bw.Flush(); bw.Close(); } // Whend reader.Read } // End Using reader } // End using bw fs.Flush(); fs.Close(); } // End using fs } // End using cmd if (conn.State != System.Data.ConnectionState.Closed) { conn.Close(); } } // End Using conn } // End Sub SaveAssembly2
public ActionStatus SaveHardwareKit(Entity.KitVerifyRequest requestData, bool isEdit) { var response = new ActionStatus(); try { logger.InfoLog(Constants.ACTION_ENTRY, "HardwareKitRepository.SaveHardwareKit"); var createdHardwareKits = new List <BulkUploadResponse>(); var xmlData = string.Empty; using (var stringwriter = new System.IO.StringWriter()) { var serializer = new XmlSerializer(requestData.GetType()); serializer.Serialize(stringwriter, requestData); xmlData = stringwriter.ToString(); } using (var sqlDataAccess = new SqlDataAccess(ConnectionString)) { List <System.Data.Common.DbParameter> parameters = sqlDataAccess.CreateParams(component.helper.SolutionConfiguration.CurrentUserId, component.helper.SolutionConfiguration.Version); parameters.Add(sqlDataAccess.CreateParameter("data", xmlData.ToString(), DbType.Xml, ParameterDirection.Input)); parameters.Add(sqlDataAccess.CreateParameter("isEdit", (isEdit == false ? 0 : 1), DbType.Boolean, ParameterDirection.Input)); System.Data.Common.DbDataReader dbDataReader = sqlDataAccess.ExecuteReader(sqlDataAccess.CreateCommand("[HardwareKit_AddUpdate]", CommandType.StoredProcedure, null), parameters.ToArray()); while (dbDataReader.Read()) { var item = new Entity.BulkUploadResponse(); if (dbDataReader["kitGuid"] != null) { item.kitGuid = new Guid(dbDataReader["kitGuid"].ToString().ToUpper()); item.kitTypeGuid = new Guid(dbDataReader["kitTypeGuid"].ToString().ToUpper()); item.kitCode = (dbDataReader["kitCode"].ToString()); createdHardwareKits.Add(item); } else { response.Message = dbDataReader["fieldname"].ToString().ToUpper(); response.Data = "Unable to save Hardware Kit!"; response.Success = false; } } if (createdHardwareKits.Count != 0 || createdHardwareKits.Count == 1) { response.Success = true; response.Message = "Hardware Kit saved successfully!"; response.Data = createdHardwareKits; } else { response.Success = false; response.Data = createdHardwareKits; response.Message = "Unable to save Hardware Kit!"; } } logger.InfoLog(Constants.ACTION_EXIT, "HardwareKitRepository.SaveHardwareKit"); } catch (Exception ex) { response.Success = false; response.Message = ex.Message.ToString(); logger.ErrorLog(Constants.ACTION_EXCEPTION, ex); } return(response); }
public List <SqlVarParameter> GetParameters(DB.Coding.DataUtility dataUtility, SelectStatementInfo selectStatement, ref List <SqlVarParameter> allSqlVarParameters) { List <SqlVarParameter> sqlVarParameters = ParseVarName(selectStatement.sqlWithOutSubSelect); if (sqlVarParameters.Count > 0) { string sqlParametersSelect = "select "; for (int i = 0; i < sqlVarParameters.Count; i++) { if (i != 0) { sqlParametersSelect += ","; } sqlParametersSelect += sqlVarParameters[i].fullName; } sqlParametersSelect += " from " + selectStatement.fromClause + " where 1>2 "; Regex fromReg = new Regex(@"\s+from\s+", RegexOptions.IgnoreCase); System.Data.Common.DbCommand cmd = dataUtility.GetDbCommand(FormatSql(sqlParametersSelect), dataUtility.con); System.Data.DataTable dt = new System.Data.DataTable(); System.Data.Common.DbDataReader reader = cmd.ExecuteReader(); dt = reader.GetSchemaTable(); reader.Read(); for (int i = 0; i < dt.Rows.Count; i++) { SqlVarParameter parameter = sqlVarParameters[i]; DB.Coding.Field field = new DB.Coding.Field(); field.name = dt.Rows[i]["BaseColumnName"].ToString(); field.structFieldName = dt.Rows[i]["ColumnName"].ToString(); field.allowNull = Convert.ToBoolean(dt.Rows[i]["AllowDBNull"]); field.csTypeLink = dataUtility.csTypeDic[dt.Rows[i]["DataType"].ToString().Split('.')[1]]; if (dataUtility.dbType == DB.DBType.Sqlite) { field.sqlType = dt.Rows[i]["DataTypeName"].ToString(); } else { field.sqlType = reader.GetDataTypeName(i); } field.isId = Convert.ToBoolean(dt.Rows[i]["IsKey"]); field.length = Convert.ToInt32(dt.Rows[i]["ColumnSize"]); field.localType = dt.Rows[i]["DataType"].ToString(); field.isId = Convert.ToBoolean(dt.Rows[i]["IsKey"]); field.position = Convert.ToInt32(dt.Rows[i]["ColumnOrdinal"]); field.dbType = dataUtility.GetDbType(Convert.ToInt32(dt.Rows[i]["ProviderType"])); //field.hasDefault = dt.Rows[i]["DefaultValue"] != null; //field.defautlValue = field.hasDefault ? dt.Rows[i]["DefaultValue"].ToString() : null; if (!string.IsNullOrEmpty(field.sqlType)) { if (dataUtility.typeDic.ContainsKey(field.sqlType.ToLower())) { field.simpleType = dataUtility.typeDic[field.sqlType.ToLower()].simpleType; } else { field.simpleType = "String"; } } else { field.simpleType = ""; } parameter.field = field; allSqlVarParameters.Add(parameter); } reader.Close(); } //分析子查询里的参数 for (int i = 0; i < selectStatement.selects.Count; i++) { GetParameters(dataUtility, selectStatement.selects[i], ref allSqlVarParameters); } return(sqlVarParameters); }
public List <DB.Coding.Field> GetFields(DB.Coding.DataUtility dataUtility, SelectStatementInfo selectStatement) { List <DB.Coding.Field> fields = new List <DB.Coding.Field>(); string selectFiledsSql = string.Format("select {0} from {1} where 1>2", selectStatement.selectClause, selectStatement.fromClause); System.Data.Common.DbCommand cmd = dataUtility.GetDbCommand(FormatSql(selectFiledsSql), dataUtility.con); System.Data.DataTable dt = new System.Data.DataTable(); System.Data.Common.DbDataReader reader = cmd.ExecuteReader(); dt = reader.GetSchemaTable(); reader.Read(); Regex nameReg = new Regex(@"^[_0-9a-zA-Z]+$"); for (int i = 0; i < dt.Rows.Count; i++) { DB.Coding.Field field = new DB.Coding.Field(); field.name = dt.Rows[i]["BaseColumnName"].ToString(); field.structFieldName = dt.Rows[i]["ColumnName"].ToString(); //查看名称是否合法,不合法则重命名. if (!nameReg.IsMatch(field.name)) { field.name = "_column" + i.ToString(); } if (!nameReg.IsMatch(field.structFieldName)) { field.structFieldName = "_column" + i.ToString(); } field.allowNull = Convert.ToBoolean(dt.Rows[i]["AllowDBNull"]); field.csTypeLink = dataUtility.csTypeDic[dt.Rows[i]["DataType"].ToString().Split('.')[1]]; if (dataUtility.dbType == DB.DBType.Sqlite) { field.sqlType = dt.Rows[i]["DataTypeName"].ToString(); } else { field.sqlType = reader.GetDataTypeName(i); } field.isId = Convert.ToBoolean(dt.Rows[i]["IsKey"]); field.length = Convert.ToInt32(dt.Rows[i]["ColumnSize"]); field.localType = dt.Rows[i]["DataType"].ToString(); field.isId = Convert.ToBoolean(dt.Rows[i]["IsKey"]); field.position = Convert.ToInt32(dt.Rows[i]["ColumnOrdinal"]); field.dbType = dataUtility.GetDbType(Convert.ToInt32(dt.Rows[i]["ProviderType"])); //field.hasDefault = dt.Rows[i]["DefaultValue"] != null; //field.defautlValue = field.hasDefault ? dt.Rows[i]["DefaultValue"].ToString() : null; //如果没有相对应的数据库类型 if (!string.IsNullOrEmpty(field.sqlType)) { if (dataUtility.typeDic.ContainsKey(field.sqlType.ToLower())) { field.simpleType = dataUtility.typeDic[field.sqlType.ToLower()].simpleType; } else { switch (field.localType) { case "System.Int16": field.simpleType = "Number"; break; case "System.Int32": field.simpleType = "Number"; break; case "System.Int64": field.simpleType = "Number"; break; case "System.UInt16": field.simpleType = "Number"; break; case "System.UInt32": field.simpleType = "Number"; break; case "System.UInt64": field.simpleType = "Number"; break; case "System.Byte": field.simpleType = "Number"; break; case "System.SByte": field.simpleType = "Number"; break; case "System.Single": field.simpleType = "Number"; break; case "System.Decimal": field.simpleType = "Number"; break; case "System.Double": field.simpleType = "Number"; break; case "System.TimeSpan": field.simpleType = "Time"; break; case "System.DateTime:": field.simpleType = "Time"; break; default: field.simpleType = field.simpleType = "String"; break; } } } else { switch (field.localType) { case "System.Int16": field.simpleType = "Number"; break; case "System.Int32": field.simpleType = "Number"; break; case "System.Int64": field.simpleType = "Number"; break; case "System.UInt16": field.simpleType = "Number"; break; case "System.UInt32": field.simpleType = "Number"; break; case "System.UInt64": field.simpleType = "Number"; break; case "System.Byte": field.simpleType = "Number"; break; case "System.SByte": field.simpleType = "Number"; break; case "System.Single": field.simpleType = "Number"; break; case "System.Decimal": field.simpleType = "Number"; break; case "System.Double": field.simpleType = "Number"; break; case "System.TimeSpan": field.simpleType = "Time"; break; case "System.DateTime:": field.simpleType = "Time"; break; default: field.simpleType = field.simpleType = "String"; break; } } fields.Add(field); } reader.Close(); return(fields); }
public void SerializeLargeDataset(HttpContext context) { string strSQL = @" SELECT TOP 10 * FROM T_Benutzer; SELECT TOP 10 * FROM T_Benutzergruppen; -- SELECT * FROM T_Benutzer LIMIT 10; -- SELECT * FROM T_Benutzergruppen LIMIT 10; -- SELECT * FROM T_Benutzer OFFSET 0 FETCH NEXT 10 ROWS ONLY; -- SELECT * FROM T_Benutzergruppen OFFSET 0 FETCH NEXT 10 ROWS ONLY; "; Newtonsoft.Json.JsonSerializer ser = new Newtonsoft.Json.JsonSerializer(); using (Newtonsoft.Json.JsonTextWriter jsonWriter = new Newtonsoft.Json.JsonTextWriter(context.Response.Output)) { jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented; jsonWriter.WriteStartObject(); jsonWriter.WritePropertyName("Tables"); jsonWriter.WriteStartArray(); using (System.Data.Common.DbConnection con = SQL.CreateConnection()) { if (con.State != System.Data.ConnectionState.Open) { con.Open(); } using (System.Data.Common.DbCommand cmd = con.CreateCommand()) { cmd.CommandText = strSQL; using (System.Data.Common.DbDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess | System.Data.CommandBehavior.CloseConnection )) { do { jsonWriter.WriteStartObject(); // tbl = new Table(); jsonWriter.WritePropertyName("Columns"); jsonWriter.WriteStartArray(); for (int i = 0; i < dr.FieldCount; ++i) { jsonWriter.WriteStartObject(); jsonWriter.WritePropertyName("ColumnName"); jsonWriter.WriteValue(dr.GetName(i)); jsonWriter.WritePropertyName("FieldType"); jsonWriter.WriteValue(GetAssemblyQualifiedNoVersionName(dr.GetFieldType(i))); jsonWriter.WriteEndObject(); } // Next i jsonWriter.WriteEndArray(); jsonWriter.WritePropertyName("Rows"); jsonWriter.WriteStartArray(); if (dr.HasRows) { while (dr.Read()) { object[] thisRow = new object[dr.FieldCount]; jsonWriter.WriteStartArray(); // object[] thisRow = new object[dr.FieldCount]; for (int i = 0; i < dr.FieldCount; ++i) { jsonWriter.WriteValue(dr.GetValue(i)); } // Next i jsonWriter.WriteEndArray(); // tbl.Rows.Add(thisRow); } // Whend } // End if (dr.HasRows) jsonWriter.WriteEndArray(); jsonWriter.WriteEndObject(); // ser.Tables.Add(tbl); } while (dr.NextResult()); } // End using dr } // End using cmd if (con.State != System.Data.ConnectionState.Closed) { con.Close(); } } // End using con jsonWriter.WriteEndArray(); jsonWriter.WriteEndObject(); jsonWriter.Flush(); } // End Using jsonWriter context.Response.Output.Flush(); context.Response.OutputStream.Flush(); context.Response.Flush(); } // End Sub SerializeLargeDataset
private void FillresultsNew <T>(Type currentType, EntityCollection <T> records, System.Data.Common.DbDataReader result) where T : class, IDataEntity { var CurrentTypeProperties = currentType.GetProperties().ToList(); while (result.Read()) { //now iterate through the properties //if the property has a maped field with Field attribute defined // we set the property value from that field //otherwise we se the property value from the field with name same as property's name T newEntity = (T)Activator.CreateInstance(typeof(T)); for (int i = 0; i < CurrentTypeProperties.Count; i++) { var property = CurrentTypeProperties[i]; DataInterface.Attribbutes.Field FieldAttribute = (DataInterface.Attribbutes.Field)(from attr in property.GetCustomAttributes(true) where attr.GetType() == typeof(DataInterface.Attribbutes.Field) select attr).FirstOrDefault(); string _fieldName; if (FieldAttribute != null) { _fieldName = FieldAttribute.Name; } else { _fieldName = property.Name; } try { if (result[_fieldName] != DBNull.Value) { if (result[_fieldName].GetType() == typeof(Int64)) { if (property.PropertyType == typeof(string)) { property.SetValue(newEntity, result[_fieldName].ToString()); } else { property.SetValue(newEntity, Convert.ToInt32(result[_fieldName])); } } else { if (result[_fieldName].GetType() == typeof(Int32)) { if (property.PropertyType == typeof(string)) { property.SetValue(newEntity, result[_fieldName].ToString()); } else { property.SetValue(newEntity, result[_fieldName]); } } else { property.SetValue(newEntity, result[_fieldName]); } } } } catch (Exception ex) { var aa = ex; } } if (_Encryptor != null) { if (newEntity is IEncryptableClass) { _Encryptor.Decryptproperties((IEncryptableClass)newEntity); } } records.Add(newEntity); } }
} // End Sub SerializeLargeDataset public void SerializeLargeTable(HttpContext context) { Newtonsoft.Json.JsonSerializer ser = new Newtonsoft.Json.JsonSerializer(); using (Newtonsoft.Json.JsonTextWriter jsonWriter = new Newtonsoft.Json.JsonTextWriter(context.Response.Output)) { jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented; jsonWriter.WriteStartObject(); using (System.Data.Common.DbConnection con = SQL.CreateConnection()) { if (con.State != System.Data.ConnectionState.Open) { con.Open(); } using (System.Data.Common.DbCommand cmd = con.CreateCommand()) { cmd.CommandText = "SELECT TOP 10000 * FROM T_LOG_SAP_Interface"; using (System.Data.Common.DbDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess | System.Data.CommandBehavior.CloseConnection )) { jsonWriter.WritePropertyName("Columns"); jsonWriter.WriteStartArray(); for (int i = 0; i < dr.FieldCount; i++) { string colName = dr.GetName(i); System.Type t = dr.GetFieldType(i); jsonWriter.WriteStartObject(); jsonWriter.WritePropertyName("ColumnName"); jsonWriter.WriteValue(colName); jsonWriter.WritePropertyName("DataType"); jsonWriter.WriteValue(GetAssemblyQualifiedNoVersionName(t)); // jsonWriter.WritePropertyName("DateTimeMode"); // jsonWriter.WriteValue(column.DateTimeMode.ToString()); jsonWriter.WriteEndObject(); } jsonWriter.WriteEndArray(); jsonWriter.WritePropertyName("Rows"); jsonWriter.WriteStartArray(); if (dr.HasRows) { int fieldCount = dr.FieldCount; while (dr.Read()) { jsonWriter.WriteStartArray(); for (int i = 0; i < fieldCount; ++i) { object obj = dr.GetValue(i); jsonWriter.WriteValue(obj); } // Next i jsonWriter.WriteEndArray(); jsonWriter.Flush(); context.Response.Output.Flush(); context.Response.Flush(); } // Whend while (dr.Read()) } // End if (dr.HasRows) dr.Close(); jsonWriter.WriteEndArray(); } // End using dr } // End using cmd if (con.State != System.Data.ConnectionState.Closed) { con.Close(); } } // End using con jsonWriter.WriteEndObject(); jsonWriter.Flush(); context.Response.Output.Flush(); context.Response.OutputStream.Flush(); context.Response.Flush(); } // End Using jsonWriter } // End Sub SerializeLargeTable
private void DumpTable(db mydb, string database, string table, string outdir) { if (excludeTables.Any(c => useRegexpTables ? Regex.IsMatch(table, c) : c == table)) { return; } if (!Directory.Exists(outdir)) { Directory.CreateDirectory(outdir); } string filename = Path.Combine(outdir, table + ".txt"); if (!overwrite) { if (File.Exists(filename)) { return; } } string tablename = db.GetTableName(dbprovider, database, table); // Check number of rows string sqlCount = _sql = $"select count(*) from {tablename}"; int rowcount; object o = mydb.ExecuteScalarSQL(sqlCount); if (o.GetType() == typeof(long)) { rowcount = (int)(long)o; } else { rowcount = (int)o; } if (!exportempty && rowcount == 0) { return; } string sql = GetTableQuery(mydb, tablename, rowcount); using (StreamWriter sw = new StreamWriter(filename)) { using (System.Data.Common.DbDataReader reader = mydb.ExecuteReaderSQL(sql)) { List <int> columns = new List <int>(); for (int i = 0; i < reader.FieldCount; i++) { string colname = reader.GetName(i); if (excludeColumns.Any(c => useRegexpColumns ? Regex.IsMatch(colname, c) : c == colname)) { continue; } if (reader.GetFieldType(i) == typeof(byte[]) && !binaryhex && !binaryfile) { continue; } columns.Add(i); } if (sortColumns) { columns = columns .OrderBy(c => reader.GetName(c)) .Select(c => c) .ToList(); } // Write column names if (header) { bool isFirstCol = true; for (int i = 0; i < columns.Count; i++) { if (reader.GetFieldType(i) == typeof(byte[]) && !binaryhex) { continue; } if (!isFirstCol) { sw.Write(separator); } isFirstCol = false; sw.Write(reader.GetName(columns[i])); } sw.WriteLine(); } c_c += columns.Count; if (maxrows != -1 && rowcount > maxrows) { sw.WriteLine($"{rowcount} rows."); } else { // Write data int rownum = 0; while (reader.Read()) { bool isFirstCol = true; for (int i = 0; i < columns.Count; i++) { if (reader.GetFieldType(i) != typeof(byte[])) { if (!isFirstCol) { sw.Write(separator); } isFirstCol = false; if (escapecharacters) { sw.Write(FixValue(reader.GetValue(columns[i]))); } else { sw.Write(reader.GetValue(columns[i])); } } else { if (binaryhex) { if (!isFirstCol) { sw.Write(separator); } isFirstCol = false; if (!reader.IsDBNull(i)) { int length = (int)reader.GetBytes(i, 0, null, 0, 0); byte[] buffer = new byte[length]; reader.GetBytes(i, 0, buffer, 0, length); sw.Write(FixBinaryValue(buffer)); } } if (binaryfile) { if (!reader.IsDBNull(i)) { string filename_data = Path.Combine(outdir, $"{table}_{reader.GetName(i)}_{rownum}.{extension}"); int length = (int)reader.GetBytes(i, 0, null, 0, 0); byte[] buffer = new byte[length]; reader.GetBytes(i, 0, buffer, 0, length); WriteBinaryfile(filename_data, buffer); } } } } sw.WriteLine(); rownum++; } } reader.Close(); } } }
/// <summary> /// Merges the indexes. All the "synthetic index" magic happens here through SQL queries. /// </summary> /// <param name='refTask'> /// Reference task. /// </param> /// <param name='curTask'> /// Current task. /// </param> /// <exception cref='Exception'> /// throws Exception if not called after a Create(). /// </exception> internal void MergeIndexes(long refTask, long curTask) { if (indexDbConn == null) { throw new Exception("Must call Create() prior to MergeIndexes()"); } string refIndexName = Path.Combine(Utilities.ConfigManager.GetValue("Backups.IndexFolder"), "t" + refTask + ".idx"); string rcurIndexName = Path.Combine(Utilities.ConfigManager.GetValue("Backups.IndexFolder"), "t" + curTask + ".idx"); string attachRefQuery = "ATTACH DATABASE \"" + refIndexName + "\" AS reft"; string attachCurQuery = "ATTACH DATABASE \"" + rcurIndexName + "\" AS curt"; System.Data.Common.DbCommand comm = indexDbConn.CreateCommand(); foreach (string curRootDrive in GetrootDrives()) { comm.CommandText = attachRefQuery; comm.ExecuteNonQuery(); comm.CommandText = attachCurQuery; comm.ExecuteNonQuery(); // step1 : merge unchanged items (only existing in reference backup) /*string mergeUnchangedQ = "INSERT INTO items" +" SELECT ri.id, ri.parentid, ri.name, ri.chunk, ri.data" +" FROM reft.items ri, reft.bchunks rc, reft.rootdrives rrd WHERE ri.chunk = rc.id" +" AND rc.rootdrive = rrd.id AND rrd.mountpath='"+curRootDrive+"'" +" AND ri.id NOT IN" +" (SELECT ci.id FROM curt.items ci, curt.bchunks cc, curt.rootdrives crd WHERE ci.chunk = cc.id" +" AND cc.rootdrive = crd.id AND crd.mountpath='"+curRootDrive+"')";*/ string mergeUnchangedQ = "INSERT INTO items" + " SELECT ri.id, ri.parentid, ri.name, ri.chunk, ri.data" + " FROM reft.items ri, reft.bchunks rc, reft.rootdrives rrd WHERE ri.chunk = rc.id" + " AND rc.rootdrive = rrd.id AND rrd.mountpath='" + curRootDrive + "'" + " AND NOT EXISTS" + " (SELECT ci.id FROM curt.items ci, curt.bchunks cc, curt.rootdrives crd WHERE ci.chunk = cc.id" + " AND cc.rootdrive = crd.id AND crd.mountpath='" + curRootDrive + "' AND ci.id = ri.id)"; comm.CommandText = mergeUnchangedQ; int res = comm.ExecuteNonQuery(); Logger.Append(Severity.DEBUG, "MergeIndexes : mergequery1 = " + mergeUnchangedQ + ", inserted " + res + " unchanged items."); // step2 : merge chunks corresponding to these untouched items string mergeOldChunks = "INSERT INTO bchunks SELECT rc.rootdrive , rc.id , rc.taskid , rc.name , rc.stor1 , rc.stor2 , rc.stor3" + " FROM reft.bchunks rc, reft.rootdrives rrd" + " WHERE rc.rootdrive = rrd.id AND rrd.mountpath='" + curRootDrive + "'"; comm.CommandText = mergeOldChunks; comm.ExecuteNonQuery(); // step3 : merge totally new items (non existent in previous backups so non-existent in merge index) //string mergeNewQ = "insert into items select * from curt.items"; /*string mergeNewQ = "INSERT INTO items" +" SELECT ri.id, ri.parentid, ri.name, ri.chunk, ri.data" +" FROM reft.items ri, reft.bchunks rc, reft.rootdrives rrd WHERE ri.chunk = rc.id" +" AND rc.rootdrive = rrd.id AND rrd.mountpath='"+curRootDrive+"'" +" AND ri.id NOT IN" +" (SELECT ci.id FROM items ci, bchunks cc, rootdrives crd WHERE ci.chunk = cc.id" +" AND cc.rootdrive = crd.id AND crd.mountpath='"+curRootDrive+"')";*/ string mergeNewQ = "INSERT INTO items" + " SELECT ri.id, ri.parentid, ri.name, ri.chunk, ri.data" + " FROM curt.items ri, curt.bchunks rc, curt.rootdrives rrd WHERE ri.chunk = rc.id" + " AND rc.rootdrive = rrd.id AND rrd.mountpath='" + curRootDrive + "'" + " AND ri.id NOT IN" + " (SELECT ci.id FROM items ci, bchunks cc, rootdrives crd WHERE ci.chunk = cc.id" + " AND cc.rootdrive = crd.id AND crd.mountpath='" + curRootDrive + "')"; comm.CommandText = mergeNewQ; res = comm.ExecuteNonQuery(); Logger.Append(Severity.DEBUG, "MergeIndexes : mergequery2 (newq) = " + mergeNewQ + ", inserted " + res + " new items."); //step 4 : merge modified items (oresent in both ref and cur indexes). This is the most complicated/evoluted step as we need to merge the old metadata // with the new one for partial files (changed blocks tracking) string getModifiedItemsQ = "SELECT i.id, i.parentid, i.name, i.chunk, i.data" + " FROM reft.items i"//, reft.items ri, curt.items ci" + " WHERE i.id IN (SELECT id from curt.items)"; //+" AND i.id IN (SELECT id from reft.items)"; comm.CommandText = getModifiedItemsQ; System.Data.Common.DbDataReader reader = comm.ExecuteReader(System.Data.CommandBehavior.SequentialAccess); while (reader.Read()) { } Logger.Append(Severity.DEBUG, "MergeIndexes : mergequery3 (MODq) = " + getModifiedItemsQ); } //string mergeNewChunks = "INSERT INTO bchunks SELECT * FROM curt.bchunks"; //string cleanupChunks = "DELETE FROM bchunks where id not in (SELECT distinct(bchunk) from //comm.CommandText = mergeNewChunks; //comm.ExecuteNonQuery(); }
/// <summary> /// 获取体验券 /// </summary> /// <param name="sql"></param> /// <returns></returns> public static List <Experience> GetExperienceList(string sql) { System.Data.Common.DbDataReader dbreader = null; List <Experience> list = new List <Experience>(); try { dbreader = DbHelper.ExecuteReader(sql); while (dbreader.Read()) { Experience exp = new Experience(); exp.Id = DBNull.Value != dbreader["Id"] ? Convert.ToInt16(dbreader["Id"].ToString()) : 0; exp.Name = DBNull.Value != dbreader["EX_NAME"] ? dbreader["EX_NAME"].ToString() : string.Empty; exp.Type = DBNull.Value != dbreader["EX_TYPE"] ? Convert.ToInt16(dbreader["EX_TYPE"].ToString()) : 0; exp.Annount = DBNull.Value != dbreader["EX_ANMOUNT"] ? Convert.ToDecimal(dbreader["EX_ANMOUNT"].ToString()) : 0; exp.Rceharge = DBNull.Value != dbreader["EX_RECHARGE"] ? Convert.ToDecimal(dbreader["EX_RECHARGE"].ToString()) : 0; exp.Num = DBNull.Value != dbreader["EX_NUM"] ? Convert.ToInt16(dbreader["EX_NUM"].ToString()) : 0; //if (dbreader["EX_STARTDATE"] != null && !string.IsNullOrEmpty(dbreader["EX_STARTDATE"].ToString())) // exp.StartDate = Convert.ToDateTime(dbreader["EX_STARTDATE"]); //if (dbreader["EX_ENDDATE"] != null && !string.IsNullOrEmpty(dbreader["EX_ENDDATE"].ToString())) // exp.EndDate = Convert.ToDateTime(dbreader["EX_ENDDATE"]); if (System.DBNull.Value != dbreader["EX_STARTDATE"]) { exp.StartDate = Convert.ToDateTime(dbreader["EX_STARTDATE"]); } else { exp.StartDate = DateTime.MinValue; } if (System.DBNull.Value != dbreader["EX_ENDDATE"]) { exp.EndDate = Convert.ToDateTime(dbreader["EX_ENDDATE"]); } else { exp.EndDate = DateTime.MinValue; } //exp.EndDate = System.DBNull.Value != dbreader["EX_ENDDATE"] ? Convert.ToDateTime(dbreader["EX_ENDDATE"]) : DateTime.MinValue; exp.CreatID = DBNull.Value != dbreader["EX_CREATID"] ? dbreader["EX_CREATID"].ToString() : string.Empty; exp.Effective = DBNull.Value != dbreader["EX_EFFECTIVE"] ? Convert.ToInt16(dbreader["EX_EFFECTIVE"].ToString()) : 0; //exp.EffectiveTime = System.DBNull.Value != dbreader["EX_EFFECTIVETIME"] ? Convert.ToDateTime(dbreader["EX_EFFECTIVETIME"]) : DateTime.MinValue; if (System.DBNull.Value != dbreader["EX_EFFECTIVETIME"]) { exp.EffectiveTime = Convert.ToDateTime(dbreader["EX_EFFECTIVETIME"]); } else { exp.EffectiveTime = DateTime.MinValue; } list.Add(exp); } } catch (Exception ex) { throw new Exception(ex.Message, ex); } finally { if (null != dbreader) { dbreader.Close(); dbreader.Dispose(); } } return(list); }
public override void boton_Click(int indice) { DataSet ds; //frmReportes visor; Dialogos.frmSeleccionAnioMes frmFecha; EmpresaEntity empresa = new ConsultaEmpresas().getById(1); switch (indice) { case 0: //Jornada Legal ds = Model.DB.ejecutarDataSet(Model.TipoComando.SP, "reporteHorarios"); Model.DB.ejecutarDataSet(ref ds, Model.TipoComando.SP, "empresaConsultar", "@idEmpresa", 1); Sueldos.Reportes.CrystalReport.ReportesCreador.JornadaLegal(ds); break; case 1: //Consulta de campos empledo //TODO Incorporar el filtro de estado de empleado Dialogos.frmSeleccionCampoEmpleado seleccionCampo = new Sueldos.View.Dialogos.frmSeleccionCampoEmpleado(); if (seleccionCampo.ShowDialog() == DialogResult.OK) { if (seleccionCampo.Filtrado) { if (seleccionCampo.Historico) { ds = Model.DB.ejecutarDataSet(Model.TipoComando.SP, "ReporteEmpleadosPorCampoEmpleadoValorHistorico", "indice", seleccionCampo.Indice, "contenido", seleccionCampo.Contenido, "idEstado", seleccionCampo.Estado, "idLiquidacion", seleccionCampo.LiquidacionID); } else { ds = Model.DB.ejecutarDataSet(Model.TipoComando.SP, "ReporteEmpleadosPorCampoEmpleadoValor", "indice", seleccionCampo.Indice, "contenido", seleccionCampo.Contenido, "idEstado", seleccionCampo.Estado); } ds.Tables[0].TableName = "ReporteEmpleadosPorCampoEmpleado"; } else { if (seleccionCampo.Historico) { ds = Model.DB.ejecutarDataSet(Model.TipoComando.SP, "ReporteEmpleadosPorCampoEmpleadoHistorico", "indice", seleccionCampo.Indice, "tipo", seleccionCampo.Tipo, "idEstado", seleccionCampo.Estado, "idLiquidacion", seleccionCampo.LiquidacionID); } else { ds = Model.DB.ejecutarDataSet(Model.TipoComando.SP, "ReporteEmpleadosPorCampoEmpleado", "indice", seleccionCampo.Indice, "tipo", seleccionCampo.Tipo, "idEstado", seleccionCampo.Estado); } ds.Tables[0].TableName = "ReporteEmpleadosPorCampoEmpleado"; } EmpresaEntity emp = new ConsultaEmpresas().getById(1); Sueldos.Reportes.CrystalReport.ReportesCreador.ConsultaDeCamposEmpledo( ds, seleccionCampo.ContenidoDescripcion, seleccionCampo.LiquidacionDescripcion, seleccionCampo.IndiceDescripcion, seleccionCampo.EstadoDescripcion, emp.RazonSocial, Application.ProductVersion); } break; case 2: //Acumulados por Tipo frmFecha = new Sueldos.View.Dialogos.frmSeleccionAnioMes(); if (frmFecha.ShowDialog() == DialogResult.OK) { ds = Model.DB.ejecutarDataSet(Model.TipoComando.SP, "reporteAcumuladosPorTipo", "anioMes", frmFecha.AnioMes); Sueldos.Reportes.CrystalReport.ReportesCreador.AcumuladosPorTipo(ds); } break; case 3: //Sindicatos //TODO cpereyra MessageBox.Show("No implementado todavia"); Dialogos.frmSeleccionItem selItem = new Dialogos.frmSeleccionItem(); selItem.Nombre = "Sindicato"; selItem.Lista = Model.DB.ejecutarDataSet(Model.TipoComando.SP, "tablasConsultarContenidoyDetalleParaCombo", "tabla", "empleadosSueldos", "indice", 14); selItem.HabilitarTodos = false; if (selItem.ShowDialog() == DialogResult.OK) { frmFecha = new Sueldos.View.Dialogos.frmSeleccionAnioMes(); if (frmFecha.ShowDialog() == DialogResult.OK) { ds = Model.DB.ejecutarDataSet(Model.TipoComando.SP, "reporteSindicatos", "idSindicato", selItem.SelectedID, "anioMes", frmFecha.AnioMes); List <string> titulos = new List <string>(); System.Data.Common.DbDataReader rs = Model.DB.ejecutarDataReader(Model.TipoComando.SP, "reporteSindicatosDescripcion", "idSindicato", selItem.SelectedID); while (rs.Read()) { titulos.Add(rs["Descripcion"].ToString()); } Sueldos.Reportes.CrystalReport.ReportesCreador.Sindicatos(ds, titulos, selItem.SelectedDescripcion, frmFecha.AnioMesDescripcion); } } break; case 4: //Vacaciones Por Año frmFecha = new Sueldos.View.Dialogos.frmSeleccionAnioMes(); if (frmFecha.ShowDialog() == DialogResult.OK) { string fecha = "31/12/" + frmFecha.Anio; DataSet resultado = Model.DB.ejecutarDataSet(Model.TipoComando.SP, "vacacionesCalculaDiasTodos", "fechaTope", fecha); resultado.DataSetName = "vacacionesCalculaDiasTodos"; Model.DataSetTo.CSV(resultado, Model.Delimitador.PuntoComa); resultado.Dispose(); } break; case 5: //Fechas de Jubilación ds = Model.DB.ejecutarDataSet(Model.TipoComando.SP, "ReporteEmpleadosPorFechaDeJubilacion"); Sueldos.Reportes.CrystalReport.ReportesCreador.FechasDeJubilacion(ds, empresa.RazonSocial, Application.ProductVersion); break; } }
void backgroundWorker_DoWork(object sender, System.ComponentModel.DoWorkEventArgs e) { try { MeasureGroup mg = currentStat.IntermediateMeasureGroup; DataSource oDataSource = mg.Parent.DataSource; DsvTableBinding oTblBinding = new DsvTableBinding(mg.Parent.DataSourceView.ID, MeasureGroupHealthCheckPlugin.GetTableIdForDataItem(mg.Measures[0].Source)); DataTable dtTable = mg.ParentDatabase.DataSourceViews[oTblBinding.DataSourceViewID].Schema.Tables[oTblBinding.TableID]; //check whether this fact table uses an alternate datasource if (dtTable.ExtendedProperties.ContainsKey("DataSourceID")) { oDataSource = mg.ParentDatabase.DataSources[dtTable.ExtendedProperties["DataSourceID"].ToString()]; } Microsoft.DataWarehouse.Design.DataSourceConnection openedDataSourceConnection = Microsoft.DataWarehouse.DataWarehouseUtilities.GetOpenedDataSourceConnection((object)null, oDataSource.ID, oDataSource.Name, oDataSource.ManagedProvider, oDataSource.ConnectionString, oDataSource.Site, false); try { if (openedDataSourceConnection != null) { openedDataSourceConnection.QueryTimeOut = 0; } else { throw new Exception("Couldn't open connection from data source " + oDataSource.Name); } command = openedDataSourceConnection.CreateCommand(); command.CommandText = currentStat.SQL; if (backgroundWorker.CancellationPending) { return; } System.Data.Common.DbDataReader reader = null; try { try { reader = command.ExecuteReader(); } catch (Exception innerEx) { if (backgroundWorker.CancellationPending) { return; } else { throw innerEx; } } if (!backgroundWorker.CancellationPending && reader.Read()) { lock (command) { if (Convert.IsDBNull(reader["OriginalRecordCount"])) { currentStat.OriginalRecordCount = null; } else { currentStat.OriginalRecordCount = Convert.ToInt64(reader["OriginalRecordCount"]); } if (Convert.IsDBNull(reader["CompressedRecordCount"])) { currentStat.CompressedRecordCount = null; } else { currentStat.CompressedRecordCount = Convert.ToInt64(reader["CompressedRecordCount"]); } if (Convert.IsDBNull(reader["MatrixDimensionRecordCount"])) { currentStat.MatrixDimensionRecordCount = null; } else { currentStat.MatrixDimensionRecordCount = Convert.ToInt64(reader["MatrixDimensionRecordCount"]); } currentStat.Status = M2MMatrixCompressionPlugin.M2MMatrixCompressionStat.M2MMatrixCompressionStatStatus.Complete; } foreach (M2MMatrixCompressionPlugin.M2MMatrixCompressionStat stat in _list) { if (stat != currentStat && currentStat.IntermediateMeasureGroupName == stat.IntermediateMeasureGroupName && stat.SQL == currentStat.SQL) { stat.OriginalRecordCount = currentStat.OriginalRecordCount; stat.CompressedRecordCount = currentStat.CompressedRecordCount; stat.MatrixDimensionRecordCount = currentStat.MatrixDimensionRecordCount; stat.Status = M2MMatrixCompressionPlugin.M2MMatrixCompressionStat.M2MMatrixCompressionStatStatus.Complete; } } } } finally { try { if ((reader != null) && !reader.IsClosed) { reader.Close(); } } catch { } command = null; } } finally { try { openedDataSourceConnection.Close(); } catch { } } } catch (Exception ex) { currentStat.Error = ex.Message + "\r\n" + ex.StackTrace; foreach (M2MMatrixCompressionPlugin.M2MMatrixCompressionStat stat in _list) { if (stat != currentStat && currentStat.IntermediateMeasureGroupName == stat.IntermediateMeasureGroupName && stat.SQL == currentStat.SQL) { stat.Error = currentStat.Error; } } } }
public List <string> GetFldsValues(string strSql, List <string> lstFlds) { if (lstFlds == null) { return(null); } if (lstFlds.Count < 1) { return(null); } System.Data.Common.DbDataReader reader = null; List <string> lstReturn = null; try { System.Data.SqlClient.SqlCommand mycmd = cnn.CreateCommand(); mycmd.CommandText = strSql; reader = mycmd.ExecuteReader(); if (reader == null) { bSuccess = false; strError = "内容不存在"; return(null); } if (!reader.HasRows) { bSuccess = true; strError = "内容为空"; reader.Close(); return(null); } lstReturn = new List <string>(); if (reader.Read()) { for (int i = 0; i < lstFlds.Count; i++) { if (!Convert.IsDBNull(reader[lstFlds[i]])) { string strRT = reader[lstFlds[i]].ToString().TrimEnd(); lstReturn.Add(strRT); } else { lstReturn.Add(""); } } } reader.Close(); bSuccess = true; } catch (System.Exception ex) { bSuccess = false; strError = ex.Message; if (reader != null && !reader.IsClosed) { reader.Close(); } return(null); } return(lstReturn); }
public override void ExecuteResult(System.Web.Mvc.ControllerContext context) { context.HttpContext.Response.ContentType = "application/json"; context.HttpContext.Response.ContentEncoding = System.Text.Encoding.UTF8; using (Newtonsoft.Json.JsonTextWriter jsonWriter = new Newtonsoft.Json.JsonTextWriter(context.HttpContext.Response.Output)) { jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented; jsonWriter.WriteStartObject(); jsonWriter.WritePropertyName("Tables"); jsonWriter.WriteStartArray(); using (System.Data.Common.DbConnection con = SQL.CreateConnection()) { if (con.State != System.Data.ConnectionState.Open) { con.Open(); } using (System.Data.Common.DbCommand cmd = this.GetCommand(con)) { using (System.Data.Common.DbDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess | System.Data.CommandBehavior.CloseConnection )) { do { jsonWriter.WriteStartObject(); // tbl = new Table(); jsonWriter.WritePropertyName("Columns"); jsonWriter.WriteStartArray(); for (int i = 0; i < dr.FieldCount; ++i) { jsonWriter.WriteStartObject(); jsonWriter.WritePropertyName("ColumnName"); jsonWriter.WriteValue(dr.GetName(i)); jsonWriter.WritePropertyName("FieldType"); jsonWriter.WriteValue(SQL.GetAssemblyQualifiedNoVersionName(dr.GetFieldType(i))); jsonWriter.WriteEndObject(); } // Next i jsonWriter.WriteEndArray(); jsonWriter.WritePropertyName("Rows"); jsonWriter.WriteStartArray(); if (dr.HasRows) { while (dr.Read()) { object[] thisRow = new object[dr.FieldCount]; jsonWriter.WriteStartArray(); // object[] thisRow = new object[dr.FieldCount]; for (int i = 0; i < dr.FieldCount; ++i) { jsonWriter.WriteValue(dr.GetValue(i)); } // Next i jsonWriter.WriteEndArray(); // tbl.Rows.Add(thisRow); } // Whend } // End if (dr.HasRows) jsonWriter.WriteEndArray(); jsonWriter.WriteEndObject(); // ser.Tables.Add(tbl); } while (dr.NextResult()); } // End using dr } // End using cmd if (con.State != System.Data.ConnectionState.Closed) { con.Close(); } } // End using con jsonWriter.WriteEndArray(); jsonWriter.WriteEndObject(); jsonWriter.Flush(); } // End Using jsonWriter context.HttpContext.Response.Output.Flush(); context.HttpContext.Response.OutputStream.Flush(); context.HttpContext.Response.Flush(); } // End Sub SerializeLargeDataset
public static void Test() { System.Data.SqlClient.SqlConnectionStringBuilder csb = new System.Data.SqlClient.SqlConnectionStringBuilder(); csb.DataSource = System.Environment.MachineName; csb.IntegratedSecurity = true; if (!csb.IntegratedSecurity) { csb.UserID = "DAL_Test"; csb.Password = "******"; } // End if (!csb.IntegratedSecurity) csb.InitialCatalog = "COR_Basic_Demo_V4"; cDAL DAL = cDAL.CreateInstance(); DAL.ConnectionString = csb.ConnectionString; using (System.Data.Common.DbConnection dbConnection = DAL.GetConnection()) { object objUser1 = DAL.ExecuteScalar("SELECT TOP 1 BE_User FROM T_Benutzer ORDER BY BE_User;", dbConnection); object objUser2 = DAL.ExecuteScalar("SELECT TOP 1 BE_User FROM T_Benutzer ORDER BY BE_ID;", dbConnection); DAL.ExecuteNonQuery("UPDATE T_Benutzer SET BE_Hash = BE_Hash;", dbConnection); DAL.ExecuteNonQuery("UPDATE T_Benutzer SET BE_Hash = BE_Hash;", dbConnection); #if WITH_CONNECTION //using (System.Data.Common.DbDataReader reader = DAL.ExecuteReader("SELECT * FROM T_Benutzer; SELECT * FROM T_Benutzergruppen;", dbConnection)) using (System.Data.Common.DbDataReader reader = DAL.ExecuteReader_Buggy("SELECT * FROM T_Benutzer; SELECT * FROM T_Benutzergruppen;")) #else DAL.ExecuteReader("SELECT * FROM T_Benutzer; SELECT * FROM T_Benutzergruppen;", delegate(System.Data.Common.DbDataReader reader) #endif { do { for (int i = 0; i < reader.FieldCount; ++i) { string fieldName = reader.GetName(i); System.Type fieldType = reader.GetFieldType(i); System.Console.WriteLine("{0}:\t{1}\t{2}", i, fieldName, fieldType.ToString()); } // Next i if (reader.HasRows) { int rowCount = 1; while (reader.Read()) { System.Console.WriteLine(@"Row {0}", rowCount); for (int i = 0; i < reader.FieldCount; ++i) { string fieldName = reader.GetName(i); object fieldValue = reader.GetValue(i); System.Console.WriteLine(@" - {0}: {1}", fieldName, System.Convert.ToString(fieldValue)); } // Next i ++rowCount; } // Whend --rowCount; } // End if (reader.HasRows) } while (reader.NextResult()); } // End Using reader #if !WITH_CONNECTION ); #endif object objUser3 = DAL.ExecuteScalar("SELECT TOP 1 BE_User FROM T_Benutzer ORDER BY BE_Hash;", dbConnection); object objUser4 = DAL.ExecuteScalar("SELECT TOP 1 BE_User FROM T_Benutzer ORDER BY BE_Passwort;", dbConnection); } // End Using dbConnection } // End Sub Test
public ActionStatus UploadHardwareKit(List <Entity.HardwareKitDTO> requestData) { var response = new ActionStatus(); try { logger.InfoLog(Constants.ACTION_ENTRY, "HardwareKitRepository.SaveHardwareKit"); List <Guid> createdHardwareKits = new List <Guid>(); var stringResult = string.Empty; using (var stringwriter = new System.IO.StringWriter()) { var serializer = new XmlSerializer(requestData.GetType()); serializer.Serialize(stringwriter, requestData); stringResult = stringwriter.ToString(); } stringResult = stringResult.Replace("HardwareKitDTO", "HardwareKit").Replace("KitDeviceDTO", "KitDevice"); XDocument requestXML = XDocument.Parse(stringResult); requestXML = new XDocument(new XElement("HardwareKits", requestXML.Root)); using (var sqlDataAccess = new SqlDataAccess(ConnectionString)) { List <System.Data.Common.DbParameter> parameters = sqlDataAccess.CreateParams(component.helper.SolutionConfiguration.CurrentUserId, component.helper.SolutionConfiguration.Version); parameters.Add(sqlDataAccess.CreateParameter("data", requestXML.ToString(), DbType.Xml, ParameterDirection.Input)); System.Data.Common.DbDataReader dbDataReader = sqlDataAccess.ExecuteReader(sqlDataAccess.CreateCommand("[KitDevice_AddUpdate]", CommandType.StoredProcedure, null), parameters.ToArray()); while (dbDataReader.Read()) { if (dbDataReader["guid"] != null) { createdHardwareKits.Add(new Guid(dbDataReader["guid"].ToString().ToUpper())); } else { response.Message = dbDataReader["fieldname"].ToString().ToUpper(); response.Data = null; response.Success = false; } } if (createdHardwareKits.Count != 0 && createdHardwareKits.Count == 1) { // requestData.Guid = createdHardwareKits.FirstOrDefault(); response.Success = true; response.Data = requestData; } else { response.Success = false; response.Data = requestData; response.Message = "Unable to save Hardware-Kit"; } //var result = DataUtils.DataReaderToList<Entity.HardwareKitResponse>(dbDataReader, null); //result.Count = int.Parse(parameters.Where(p => p.ParameterName.Equals("count")).FirstOrDefault().Value.ToString()); } logger.InfoLog(Constants.ACTION_EXIT, "HardwareKitRepository.SaveHardwareKit"); } catch (Exception ex) { response.Success = false; response.Message = ex.Message.ToString(); logger.ErrorLog(Constants.ACTION_EXCEPTION, ex); } return(response); }
/// <summary> /// Procesa las fichadas de un dia de un legajo tratando de obtener el total /// de horas extras trabajadas /// </summary> /// <param name="fecha"></param> /// <param name="legajo"></param> private void procesaDia(DateTime fecha, int legajo) { int horaEntrada = 0; int horaSalida = 0; int limiteDia = 0; int minutosExtras = 0; int horaDesde = 0; int horaHasta = 0; int idTipoHoraExtra = 0; int cont = 1; int idDia; double limiteHs50 = 0; limiteHs50 = this.consultaLimiteHs50(); // Model.DB.Enlazado = true; System.Data.Common.DbDataReader rs = Model.DB.ejecutarDataReader(Model.TipoComando.SP, "relojConsultarFichadasFechaEnMinutos", "@legajo", legajo, "@fecha", fecha); System.Data.Common.DbDataReader rsHorasExtrasDefiniciones = null; //limpio horas extras del legajo antes de procesar Console.WriteLine("eliminando registro: " + legajo + " fecha: " + fecha); Model.DB.ejecutarProceso(Model.TipoComando.SP, "horasExtrasEliminar", "@legajo", legajo, "@fecha", fecha); //determino dia de la semana. siempre 0-dom/6-sab idDia = (int)fecha.DayOfWeek; if (rs.HasRows) { while (rs.Read()) //recorro fichadas del dia { this.pbProceso.Value += 1; if (Convert.ToInt32(rs["limiteDia"]) == 24) { //horaEntrada = Convert.ToInt32(rs["minutos"]); horaEntrada = 24 * 60 - horaSalida; } else { if (Convert.ToInt32(rs["minutos"]) < 0) { horaEntrada = Convert.ToInt32(rs["minutos"]); } if (Convert.ToInt32(rs["minutos"]) > 0) { horaSalida = Convert.ToInt32(rs["minutos"]); } } if (horaEntrada < 0) { horaEntrada = -1 * horaEntrada; } if (horaSalida < 0) { horaSalida = -1 * horaSalida; } if (cont % 2 == 0) //solo cuando es par verifico { rsHorasExtrasDefiniciones = Model.DB.ejecutarDataReader(Model.TipoComando.SP, "horasExtrasDefinicionesConsultarDia", "@idDia", idDia); while (rsHorasExtrasDefiniciones.Read()) //recorro deficiones de hs. extras para ese dia { if (this.verificaSiFeriado(fecha)) { limiteDia = 0; horaDesde = 0; //00:00:00 horaHasta = 1440; //24:00:00 idTipoHoraExtra = 89; } else { limiteDia = Convert.ToInt32(rsHorasExtrasDefiniciones["limiteDia"]); horaDesde = pasaAminutos(Convert.ToDateTime(rsHorasExtrasDefiniciones["horaDesde"])); horaHasta = pasaAminutos(Convert.ToDateTime(rsHorasExtrasDefiniciones["horaHasta"])); idTipoHoraExtra = Convert.ToInt32(rsHorasExtrasDefiniciones["idTipoHoraExtra"]); } if (horaEntrada > horaSalida) //(limiteDia == 24) //&& (horaEntrada>=horaDesde)) { //minutosExtras = this.obtieneMinutosExtras(horaDesde, 24*60, 24*60-horaSalida, 24*60); minutosExtras = this.obtieneMinutosExtras(horaDesde, 24 * 60, horaEntrada, 24 * 60); //minutosExtras += this.obtieneMinutosExtras(0, horaHasta, 0, horaEntrada); minutosExtras += this.obtieneMinutosExtras(0, horaHasta, 0, horaSalida); } else { //en caso de nocturnas no definidas en el horario del empleado if (horaDesde > horaHasta) { minutosExtras = this.obtieneMinutosExtras(0, horaHasta, horaEntrada, horaSalida); } else { minutosExtras = this.obtieneMinutosExtras(horaDesde, horaHasta, horaEntrada, horaSalida); } } //horas al 50% Lu a Vi > 10 Hs (540 min) if (idDia >= 1 && idDia <= 5 && idTipoHoraExtra == 80) { if (minutosExtras > limiteHs50 * 60) { minutosExtras = (int)(minutosExtras - (limiteHs50 * 60)); } else { minutosExtras = 0; } } Console.WriteLine("total id: " + Convert.ToInt32(rsHorasExtrasDefiniciones["id"]) + " minutos extras: " + minutosExtras.ToString()); Console.WriteLine("insertando registro: " + legajo + " tipo Hora: " + idTipoHoraExtra); if (minutosExtras > 0) { Model.DB.ejecutarProceso(Model.TipoComando.SP, "horasExtrasActualizar", "@legajo", legajo, "@fecha", fecha, "@idTipoHoraExtra", idTipoHoraExtra, "cantidad", minutosExtras); } Thread.Sleep(100); //OJO PARA CONTROLAR timeout } rsHorasExtrasDefiniciones.Close(); Model.DB.desconectarDB(); } cont++; } rs.Close(); Model.DB.desconectarDB(); // Model.DB.Enlazado = false; } }