public void Map() { DataTable dt = new DataTable(); DataRow row = null; IDataReader dataReader = null; dt.Columns.Add(new DataColumn("id", typeof(int))); dt.Columns.Add(new DataColumn("name", typeof(string))); dt.Columns.Add(new DataColumn("prop01", typeof(int))); dt.Columns.Add(new DataColumn("unset")); row = dt.NewRow(); row["id"] = 5; row["name"] = "idIs5"; row["prop01"] = DBNull.Value; dt.Rows.Add(row); row = dt.NewRow(); row["id"] = DBNull.Value; row["name"] = "idWithDBNull"; row["prop01"] = 2; dt.Rows.Add(row); dataReader = dt.CreateDataReader(); var entities = FieldMapper.Map<MyEntity01>(new FieldAdoDataReader(dataReader)).ToList(); Assert.IsTrue(entities.Count == 2, "All entities are created"); Assert.IsTrue(entities[0].Id == 5, "Field mapping of int is correct"); Assert.IsTrue(entities[0].Name == "idIs5", "Field mapping of string is correct"); Assert.IsTrue(entities[0].Prop01 == 99, "Field custom default value is set"); Assert.IsTrue(entities[0].Unset == default(string), "Field decorated but not returned by the data source is mapped with type's default value"); Assert.IsTrue(entities[0].UnsetWithDefaultValue == "unset", "Field decorated but not returned by the data source is mapped with field's default value"); Assert.IsTrue(entities[0].IgnoreDecorated == default(string), "Field decorated with [Ignore] is ignored"); Assert.IsTrue(entities[0].IgnoreNotDecorated == default(string), "Field not decorated with [Ignore] but not returned by the data source is ignored"); Assert.IsTrue(entities[1].Id == default(int), "Field mapping of int with DBNull value is correct"); Assert.IsTrue(entities[1].Prop01 == 2, "Field with default value is mapped with its' correct value if provided by the data source"); }
public void GetValueOrDefaultTest() { var dt = new DataTable(); dt.Columns.Add("int32", typeof(Int32)); dt.Rows.Add(new Object[] { DBNull.Value }); dt.Rows.Add(new Object[] { 42 }); dt.Rows.Add(new Object[] { DBNull.Value }); dt.Rows.Add(new Object[] { 42 }); using (var dr = dt.CreateDataReader()) { // Reading a DBNull.Value into a nullable Int32 will succeed. dr.Read(); dr.GetValueOrDefault<Int32?>("int32"); // Reading 42 into a nullable Int32 will succeed. dr.Read(); dr.GetValueOrDefault<Int32?>("int32"); // Reading a DBNull.Value into a non-nullable Int32 will throw an InvalidCastException. dr.Read(); Assert.Catch<InvalidCastException>(() => dr.GetValueOrDefault<Int32>("int32")); // Reading 42 into a non-nullable Int32 will succeed. dr.Read(); dr.GetValueOrDefault<Int32>("int32"); } }
private void btnPotvrdi_Click(object sender, EventArgs e) { IList <int> listaSirovina = new List <int>(); SqlConnection myConnection = new SqlConnection(@"Data Source=31.147.204.119\PISERVER,1433;Initial Catalog=17015_DB;Integrated Security=False;User ID=17015_User;Password=546YQ8yy;Connect Timeout=15;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"); SqlDataAdapter myDataAdapter = new SqlDataAdapter("SELECT id_sirovina FROM primka_sirovina WHERE id_primke = " + idPrimke, myConnection); DataTable dataTable = new System.Data.DataTable(); myDataAdapter.Fill(dataTable); using (IDataReader reader = dataTable.CreateDataReader()) while (reader.Read()) { listaSirovina.Add(reader.GetInt32(reader.GetOrdinal("id_sirovina"))); } if (listaSirovina.Count.Equals(0)) { MessageBox.Show("Ne možete kreirati prazan dokument!", "Upozorenje", MessageBoxButtons.OK, MessageBoxIcon.Error); } else { foreach (int item in listaSirovina) { int trenutnaKolicina = Int32.Parse(sirovinaTableAdapter.DohvatiKolicinu(item).ToString()); int zaprimljenaKolicina = Int32.Parse(primka_sirovinaTableAdapter.DohvatiKolicinu(item, idPrimke).ToString()); int novaKolicina = PomocneClass.dajPovecanuKolicinu(trenutnaKolicina, zaprimljenaKolicina); sirovinaTableAdapter.UpdateQueryAzurirajKolicinu(novaKolicina, item); } this.Close(); } }
/// <summary> /// Compares the schema. /// </summary> /// <param name="tableName">Name of the table.</param> /// <param name="connectionString">The connection string.</param> /// <param name="table">The table.</param> /// <returns></returns> public bool CompareSchema(string tableName, string connectionString, DataTable table) { bool schemaMatch = false; SqlConnection connection = new SqlConnection(connectionString); string stagingPrefix = System.Configuration.ConfigurationManager.AppSettings.Get("SapStagingTablePrefix"); if (IsTableExists(stagingPrefix + tableName, connectionString)) { string selectStatement = "SELECT TOP(1) * FROM " + stagingPrefix + tableName; SqlCommand cmd = new SqlCommand(selectStatement); cmd.Connection = connection; connection.Open(); try { SqlDataReader dr = cmd.ExecuteReader(); schemaMatch = dr.GetSchemaTable().Equals(table.CreateDataReader().GetSchemaTable()); } catch (Exception) { connection.Close(); return false; } } connection.Close(); return schemaMatch; }
public void SetUp() { _mockCache = MockComponentProvider.CreateDataCacheProvider(); MockComponentProvider.CreateEventLogController(); _hostSettingsTable = new DataTable("HostSettings"); var nameCol = _hostSettingsTable.Columns.Add("SettingName"); _hostSettingsTable.Columns.Add("SettingValue"); _hostSettingsTable.Columns.Add("SettingIsSecure"); _hostSettingsTable.PrimaryKey = new[] {nameCol}; _hostSettingsTable.Rows.Add("String_1_S", "String_1_S", true); _hostSettingsTable.Rows.Add("String_2_S", "String_1_S", true); _hostSettingsTable.Rows.Add("String_3_U", "Value_3_U", false); _hostSettingsTable.Rows.Add("String_4_U", "Value_4_U", false); _hostSettingsTable.Rows.Add("Int_5_U", "5", false); _hostSettingsTable.Rows.Add("Int_6_S", "6", true); _hostSettingsTable.Rows.Add("Double_7_S", "7", true); _hostSettingsTable.Rows.Add("Double_8_U", "8", false); _hostSettingsTable.Rows.Add("Bool_9_U", false, false); _hostSettingsTable.Rows.Add("Bool_10_S", false, true); _mockData = MockComponentProvider.CreateDataProvider(); _mockData.Setup(c => c.GetHostSettings()).Returns(_hostSettingsTable.CreateDataReader()); _mockData.Setup(c => c.GetProviderPath()).Returns(String.Empty); DataCache.ClearCache(); }
public IDataReader LoadTableData() { DataTable mockTableData = new DataTable(); mockTableData.Columns.Add("Table_Catalog"); mockTableData.Columns.Add("Table_Schema"); mockTableData.Columns.Add("Table_Name"); DataRow firstRow = mockTableData.NewRow(); firstRow["Table_Catalog"] = "TestCatalog"; firstRow["Table_Schema"] = "TestSchema"; firstRow["Table_Name"] = "FirstTestTable"; mockTableData.Rows.Add(firstRow); DataRow secondRow = mockTableData.NewRow(); secondRow["Table_Catalog"] = "TestCatalog"; secondRow["Table_Schema"] = "TestSchema"; secondRow["Table_Name"] = "SecondTestTable"; mockTableData.Rows.Add(secondRow); DataRow thirdRow = mockTableData.NewRow(); thirdRow["Table_Catalog"] = "TestCatalog"; thirdRow["Table_Schema"] = "TestSchema"; thirdRow["Table_Name"] = "ThirdTestTable"; mockTableData.Rows.Add(thirdRow); return mockTableData.CreateDataReader(); }
static void TesteDataTable() { DataTable tabTeste = new DataTable(); DataRow linhaTeste; DataColumn col01 = new DataColumn("col01", typeof(string)); DataColumn col02 = new DataColumn("col02", typeof(string)); tabTeste.Columns.Add(col01); tabTeste.Columns.Add(col02); int linha=1; while(linha <=10) { linhaTeste = tabTeste.NewRow(); linhaTeste["col01"] = "Fabio"; linhaTeste["col02"] = linha.ToString(); tabTeste.Rows.Add(linhaTeste); linha+=1; } DataTableReader rd = tabTeste.CreateDataReader(); while(rd.Read()) { Console.WriteLine(rd[0].ToString()); Console.WriteLine(rd[1].ToString()); } Console.ReadLine(); }
protected void UploadAs_Click(object sender, EventArgs e) { if (DataSetName.Text.Trim().Length != 0 && DataSetUpload.HasFile) { //move most to uploader //handle spaces in file name String uploadPath = System.IO.Path.Combine(System.IO.Path.GetTempPath().ToString(), DataSetUpload.FileName); DataSetUpload.SaveAs(uploadPath); String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.IO.Path.GetTempPath().ToString() + ";Extended Properties='text;HDR=Yes;FMT=Delimited'"; connection = new OleDbConnection(connectionString); OleDbCommand cmd = new OleDbCommand("SELECT * FROM " + DataSetUpload.FileName, connection); da = new OleDbDataAdapter(cmd); connection.Open(); dt = new System.Data.DataTable(); da.Fill(dt); System.Data.DataSet ds = new System.Data.DataSet(DataSetName.Text.Trim()); String[] parameters = { "main" }; ds.Load(dt.CreateDataReader(), System.Data.LoadOption.OverwriteChanges, parameters); Session.Add("table", dt); Session.Add("connection", connection); Session.Add("adapter", da); Registry.Registry registry = Registry.Registry.getRegistry(Session); registry.registerDataset(ds); DatasetList.Items.Add(ds.DataSetName); } }
private void LoginRandomEmployee() { DataTable employeeTable = new DataTable(); Person employee = null; using (OracleConnection objConn = new OracleConnection(Global.ConnectionString)) { OracleCommand objCmd = new OracleCommand("TICKETS_QUERIES.getRandomEmployee", objConn) { BindByName = true, CommandType = CommandType.StoredProcedure }; objCmd.Parameters.Add("p_Return", OracleDbType.RefCursor, ParameterDirection.ReturnValue); try { objConn.Open(); OracleDataAdapter adapter = new OracleDataAdapter(objCmd); adapter.Fill(employeeTable); employee = Mapper.DynamicMap<IDataReader, List<Person>>(employeeTable.CreateDataReader()).FirstOrDefault(); } catch (Exception) { Response.Redirect("../Default.aspx"); } objConn.Close(); } if (employee != null) { Global.CurrentPerson = employee; Global.CurrentPerson.accountType = Account.Faculty; } else Response.Redirect("../Default.aspx"); }
/// <summary> /// 从 DataTale 对象中逐行读取记录并将记录转化为 T 类型的集合 /// </summary> /// <typeparam name="T">目标类型参数</typeparam> /// <param name="reader">DataTale 对象。</param> /// <returns>指定类型的对象集合。</returns> public static List <T> ConvertToObject <T>(DataTable table) where T : class { return(table == null ? new List <T>() : ConvertToObject <T>(table.CreateDataReader() as IDataReader)); }
public object BusinessCreateLight(List<BusinessLight> businesses, Converter converter, object dataObject) { DataTable dt = new DataTable(); dt.Columns.Add("ExternalId", typeof(string)); dt.Columns.Add("BusinessId", typeof(int)); dt.Columns.Add("GenderFCheckIns", typeof(int)); dt.Columns.Add("GenderMCheckIns", typeof(int)); Random rnd = new Random(); int icount = 0; businesses.ForEach(x => { DataRow dr = dt.NewRow(); dr["ExternalId"] = x.ExternalID; dr["BusinessId"] = rnd.Next(); dr["GenderFCheckIns"] = icount; dr["GenderMCheckIns"] = icount; icount = icount + 10; dt.Rows.Add(dr); }); DataTableReader dtr = dt.CreateDataReader(); while(dtr.Read()) { converter(dtr, dataObject); } //dt.Select().ToList().ForEach(x=> converter(x,dataObject)); return dt; }
static void Main() { var dt = new System.Data.DataTable(); dt.Columns.Add("ID", typeof(String)); dt.Columns.Add("Val", typeof(int)); dt.Rows.Add("1", 10); dt.Rows.Add("2", 20); dt.Rows.Add("3", 10); dt.Rows.Add("4", 20); System.Data.DataTableReader dr = dt.CreateDataReader(); for (int I = 0; I < dr.FieldCount; I++) { Console.Write(dr.GetName(I) + ","); } Console.WriteLine(); while (dr.Read()) { for (int I = 0; I < dr.FieldCount; I++) { Console.Write(dr[dr.GetName(I)] + ","); } Console.WriteLine(); } }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { if (Global.CurrentPerson == null) Response.Redirect("Index.aspx"); DataTable seasonsTable = new DataTable(); List<Season> seasons = new List<Season>(); using (OracleConnection objConn = new OracleConnection(Global.ConnectionString)) { // Set up the seasons command var seasonsCommand = new OracleCommand("TICKETS_QUERIES.getSeasonsForPurchase", objConn) { BindByName = true, CommandType = CommandType.StoredProcedure }; seasonsCommand.Parameters.Add("p_Return", OracleDbType.RefCursor, ParameterDirection.ReturnValue); seasonsCommand.Parameters.Add("p_PersonId", OracleDbType.Int64, Global.CurrentPerson.person_id, ParameterDirection.Input); try { // Execute the queries and auto map the results to models objConn.Open(); var seasonsAdapter = new OracleDataAdapter(seasonsCommand); seasonsAdapter.Fill(seasonsTable); seasons = Mapper.DynamicMap<IDataReader, List<Season>>(seasonsTable.CreateDataReader()); } catch (Exception) { Response.Redirect("Index.aspx"); } objConn.Close(); } // Fill list dropdowns with data from the database if (seasons.Count > 0) { var seasonsWithEvents = seasons.GroupBy(s => s.season_id).Select(season => new Season() { season_id = season.First().season_id, name = season.First().name, price = season.First().price, ticket_count = season.First().ticket_count, event_names = seasons.Where(ev => ev.season_id == season.First().season_id).Select(en => en.event_name).ToList() }).ToList(); SeasonDropDown.DataTextField = "name"; SeasonDropDown.DataValueField = "season_id"; SeasonDropDown.DataSource = seasonsWithEvents; SeasonDropDown.DataBind(); SeasonListView.DataSource = seasonsWithEvents; SeasonListView.DataBind(); } if (Request.QueryString["Success"] != null) { Error.Text = "Successfully purchased season ticket!"; Error.Visible = true; } } }
static void Main(string[] args) { //Create map once Mapper.CreateMap<IDataReader, Person>(); //Setup a DataTable with Data var dataTable = new DataTable(); dataTable.Columns.Add(new DataColumn("Name", typeof (string))); dataTable.Columns.Add(new DataColumn("Age", typeof (int))); dataTable.Rows.Add("Phillip", "26"); dataTable.Rows.Add("jchannon", "3"); //Create a reader from the DataTable //This part could be from SQL or where ever var reader = dataTable.CreateDataReader(); //Convert the contents of the IDataReader to a collection var result = Mapper.Map<IDataReader, IEnumerable<Person>>(reader); foreach (var person in result) { Console.WriteLine(string.Format("{0} is {1} years old", person.Name, person.Age)); } Console.ReadKey(); }
public IDataReader BuildDataReader() { var authorizationSetDataTable = new DataTable(); authorizationSetDataTable.Columns.Add(FieldName.SmallInt, typeof(Int16)); authorizationSetDataTable.Columns.Add(FieldName.Int, typeof(Int32)); authorizationSetDataTable.Columns.Add(FieldName.BigInt, typeof(Int64)); authorizationSetDataTable.Columns.Add(FieldName.Guid, typeof(Guid)); authorizationSetDataTable.Columns.Add(FieldName.Float, typeof(float)); authorizationSetDataTable.Columns.Add(FieldName.Double, typeof(Double)); authorizationSetDataTable.Columns.Add(FieldName.Decimal, typeof(Decimal)); authorizationSetDataTable.Columns.Add(FieldName.DateTime, typeof(DateTime)); authorizationSetDataTable.Columns.Add(FieldName.Byte, typeof(Byte)); authorizationSetDataTable.Columns.Add(FieldName.Boolean, typeof(Boolean)); authorizationSetDataTable.Columns.Add(FieldName.Something, typeof(DateTime)); var authorizationSetDataRow = authorizationSetDataTable.NewRow(); authorizationSetDataRow[FieldName.SmallInt] = 22; authorizationSetDataRow[FieldName.Int] = 6134; authorizationSetDataRow[FieldName.BigInt] = 61346154; authorizationSetDataRow[FieldName.Guid] = Guid.NewGuid(); authorizationSetDataRow[FieldName.Float] = 642.61; authorizationSetDataRow[FieldName.Double] = 67164.64; authorizationSetDataRow[FieldName.Decimal] = 94341.61; authorizationSetDataRow[FieldName.DateTime] = DateTime.Now; authorizationSetDataRow[FieldName.Byte] = 0x12; authorizationSetDataRow[FieldName.Boolean] = true; authorizationSetDataRow[FieldName.Something] = DateTime.MaxValue; authorizationSetDataTable.Rows.Add(authorizationSetDataRow); return authorizationSetDataTable.CreateDataReader(); }
public static DataTableReader GetData(string connStr, string query, Properties props) { DataTable results = new DataTable(); using (SOConnection connection = new SOConnection(connStr)) { using (SOCommand command = new SOCommand(query, connection)) { using (SODataAdapter adapter = new SODataAdapter(command)) { foreach (Property prop in props) { if (prop.Value != null) { command.Parameters.AddWithValue(prop.Name, prop.Value); } } connection.DirectExecution = true; connection.Open(); adapter.Fill(results); } } connection.Close(); } return results.CreateDataReader(); }
protected void Page_Load(object sender, EventArgs e) { if (Global.CurrentPerson == null || string.IsNullOrEmpty(Request.QueryString["Series"])) Response.Redirect("Index.aspx"); SeriesId = long.Parse(Request.QueryString["Series"]); DataTable eventSeatsTable = new DataTable(); List<Models.EventSeats> eventSeatsModel = new List<Models.EventSeats>(); using (OracleConnection objConn = new OracleConnection(Global.ConnectionString)) { // Set up the getEventSeats command var eventSeatsCommand = new OracleCommand("TICKETS_QUERIES.getEventSeats", objConn) { BindByName = true, CommandType = CommandType.StoredProcedure }; eventSeatsCommand.Parameters.Add("p_Return", OracleDbType.RefCursor, ParameterDirection.ReturnValue); eventSeatsCommand.Parameters.Add("p_SeriesId", OracleDbType.Int64, SeriesId, ParameterDirection.Input); eventSeatsCommand.Parameters.Add("p_PersonId", OracleDbType.Int64, Global.CurrentPerson.person_id, ParameterDirection.Input); try { // Execute the queries and auto map the results to models objConn.Open(); var eventSeatsAdapter = new OracleDataAdapter(eventSeatsCommand); eventSeatsAdapter.Fill(eventSeatsTable); eventSeatsModel = Mapper.DynamicMap<IDataReader, List<Models.EventSeats>>(eventSeatsTable.CreateDataReader()); } catch (Exception) { Response.Redirect("EventSignup.aspx?Series=" + SeriesId); } objConn.Close(); // If the person already has tickets, redirect them to the page where they can review it if (eventSeatsModel.Any()) { Event.Text = eventSeatsModel.FirstOrDefault().name; Date.InnerText = eventSeatsModel.FirstOrDefault().event_datetime.ToString("dddd, MMMM d - h:mm tt"); Section.InnerText = eventSeatsModel.FirstOrDefault().description; string location = string.Format("Row {0}, ", eventSeatsModel.FirstOrDefault().seat_row); if (eventSeatsModel.Count > 1) { location += string.Format("Seats {0}-{1}", eventSeatsModel.Min(t => t.seat_number), eventSeatsModel.Max(t => t.seat_number)); } else { location += "Seat " + eventSeatsModel.FirstOrDefault().seat_number.ToString(); } Location.InnerText = location; Door.InnerText = "Enter By Door " + eventSeatsModel.FirstOrDefault().door; } else Response.Redirect("EventSignup.aspx?Series=" + SeriesId); } }
static IDataReader SetupReader() { var table = new DataTable(); table.Columns.Add("Name", Type.GetType("System.String")); table.Columns.Add("Age", Type.GetType("System.Int32")); table.Rows.Add(new object[] { "John Doe", "21" }); table.Rows.Add(new object[] { "Jane Doe", "19" }); return table.CreateDataReader(); }
public void Initialize() { DataTable table = new DataTable(); DataColumn idColumn = table.Columns.Add("ID", typeof(int)); table.Columns.Add("Name", typeof(string)); table.Rows.Add(new object[] { 1, "Iran" }); table.Rows.Add(new object[] { 2, "Irak" }); table.Rows.Add(new object[] { 3, "Isaac" }); dataReader = table.CreateDataReader(); }
public void DataTableExport(DataTable dataTable, string filePath, string title) { if (string.IsNullOrWhiteSpace(filePath)) { throw new FileNotFoundException("Incorrect file path."); } if ((dataTable == null) || (dataTable.Rows.Count == 0)) throw new ArgumentException("Empty data table."); var document = new Document(); var writer = PdfWriter.GetInstance(document, new FileStream(filePath, FileMode.Create)); document.Open(); // Add title var cell = new PdfPCell(); if (IncludeTitle) { var pdfTitle = new PdfPTable(1); GetPdfCell(ref cell, title, GetFont(18, Font.BOLD), 0); pdfTitle.AddCell(cell); document.Add(pdfTitle); } var table = new PdfPTable(dataTable.Columns.Count); // Add header for (var i = 0; i < dataTable.Columns.Count; i++) { GetPdfCell(ref cell, dataTable.Columns[i].ColumnName, GetFont(16, Font.BOLD), 2); table.AddCell(cell); } // Add content using (var dtReader = dataTable.CreateDataReader()) { while (dtReader.Read()) { for (var i = 0; i < dtReader.FieldCount; i++) { GetPdfCell(ref cell, dtReader.GetValue(i).ToString().Trim(), GetFont(14, Font.NORMAL), 1); table.AddCell(cell); } } } document.Add(table); document.Close(); writer.Close(); }
public DbDataReader CreateReader() { var dt = new DataTable(); dt.Columns.Add("IntCol", typeof(int)); dt.Columns.Add("StringCol", typeof(string)); dt.Columns.Add("DateCol", typeof(DateTime)); for (int i = 0; i < 50; i++) dt.Rows.Add(i, "row " + i, DateTime.Today.AddDays(i)); return dt.CreateDataReader(); }
public void Test() { var dt = new DataTable(); dt.Columns.Add(new DataColumn("Id", typeof(long))); dt.Columns.Add(new DataColumn("Name", typeof(string))); dt.Columns[1].AllowDBNull = true; dt.Columns.Add(new DataColumn("Status", typeof(long))); dt.Columns.Add(new DataColumn("Guid", typeof(Guid))); int count = 5; for (int i = 0; i < count; ++i) { if(i%2 == 0) dt.Rows.Add(i, Guid.NewGuid().ToString(), DBNull.Value, Guid.NewGuid()); else dt.Rows.Add(i, DBNull.Value, 1, Guid.NewGuid()); } dt.AcceptChanges(); IDataReader reader = null; #if !SDK35 reader = dt.CreateDataReader(); var rows = reader.ToList<dynamic>().ToArray(); Assert.AreEqual(count, rows.Length); #else reader = dt.CreateDataReader(); var rows = reader.ToList<IDictionary<string,object>>().ToArray(); Assert.AreEqual(count, rows.Length); #endif reader = dt.CreateDataReader(); var items = reader.ToList<TestClass>().ToArray(); Assert.AreEqual(count, items.Length); reader = dt.CreateDataReader(); items = reader.ToList<TestClass>().ToArray(); Assert.AreEqual(count, items.Length); reader = dt.CreateDataReader(); items = Mapper.Map<IDataReader, List<TestClass>>(reader).ToArray(); Assert.AreEqual(count, items.Length); reader = dt.CreateDataReader(); items = Mapper.Map<IDataReader, TestClass[]>(reader); Assert.AreEqual(count, items.Length); foreach(DataRow row in dt.Rows) { var o1 = row[0]; var o2 = row[1]; var o3 = row[2]; var o4 = row[3]; var i1 = Mapper.Map<object,int>(o1); var i2 = Mapper.Map<object,string>(o2); var i3 = Mapper.Map<object,int>(o3); var i4 = Mapper.Map<object,Guid>(o4); } }
public List<Item> GetAllItems() { List<Item> itemList = new List<Item>(); DataTable dTable = new DataTable(); ItemDAL iDAL = new ItemDAL(); string cmd; cmd = "SELECT * FROM LOLBG.Items WHERE IsDeleted = 0"; dTable = iDAL.GetSqlQueryResults(cmd); itemList = AutoMapper.Mapper.Map<IDataReader, List<Item>>(dTable.CreateDataReader()); return itemList; }
public Item GetItemByID(int id) { Item item = new Item(); DataTable dTable = new DataTable(); ItemDAL iDAL = new ItemDAL(); string cmd; cmd = "SELECT * FROM LOLBG.Items WHERE IsDeleted = 0 AND ID = " + id.ToString(); dTable = iDAL.GetSqlQueryResults(cmd); item = AutoMapper.Mapper.Map<IDataReader, List<Item>>(dTable.CreateDataReader()).First(); return item; }
public void SetUp() { _mockData = MockComponentProvider.CreateDataProvider(); DataTable hostSettingsTable = new DataTable("HostSettings"); var nameCol = hostSettingsTable.Columns.Add("SettingName"); hostSettingsTable.Columns.Add("SettingValue"); hostSettingsTable.Columns.Add("SettingIsSecure"); hostSettingsTable.PrimaryKey = new[] { nameCol }; hostSettingsTable.Rows.Add("PerformanceSetting", "0", false); _mockData.Setup(c => c.GetHostSettings()).Returns(hostSettingsTable.CreateDataReader()); }
// todo - fix the code that breaks this //[Test] public void should_throw_if_no_records_are_found() { var task = TaskFactory<FetchSingleOf<MockObject>>.Create(); var table = new DataTable(); var mockSelectCommand = new Mock<IDbCommand>(); mockSelectCommand.Setup(command => command.ExecuteReader()).Returns(table.CreateDataReader()); task.SelectCommand = mockSelectCommand.Object; // Act & Assert Assert.Throws(typeof(SingleNotFoundException), task.Execute); }
public void CBO_FillObject_binary_to_Array() { var cboTable = new DataTable("CBOTable"); var colValue = System.Text.Encoding.ASCII.GetBytes("Hello This is test"); cboTable.Columns.Add("ArrayProp", typeof(byte[])); cboTable.Rows.Add(colValue); var result = CBO.FillObject<IntPoco>(cboTable.CreateDataReader()); Assert.IsInstanceOfType<IntPoco>(result); Assert.IsNotNull(result); Assert.AreEqual(colValue, result.ArrayProp); }
public List<Champion> GetAllChampions() { DataTable dTable = new DataTable(); List<Champion> championList = new List<Champion>(); ChampionDAL cDAL = new ChampionDAL(); string cmd = "SELECT * FROM LOLBG.Champions"; //Create the command to query for the sql data table dTable = cDAL.GetSqlQueryResults(cmd); //Get the data table //Map the query resulted data table to the champion class: championList = AutoMapper.Mapper.DynamicMap<IDataReader, List<Champion>>(dTable.CreateDataReader()); return championList; }
public Champion GetChampionByID(int id) { DataTable dTable = new DataTable(); Champion champion = new Champion(); ChampionDAL cDAL = new ChampionDAL(); string cmd = "SELECT * FROM LOLBG.Champions WHERE ID = " + id.ToString(); dTable = cDAL.GetSqlQueryResults(cmd); //Map the query resulted data table to the champion class: champion = AutoMapper.Mapper.DynamicMap<IDataReader, List<Champion>>(dTable.CreateDataReader()).First(); return champion; }
private static IDataReader CreateDataReader() { var data = new DataTable(); data.Columns.Add("PersonId", typeof(string)); data.Columns.Add("StartDate", typeof (DateTime)); data.Columns.Add("Key", typeof (string)); data.Columns.Add("TypeId", typeof(int)); data.Columns.Add("Field", typeof(string)); data.Rows.Add(new object[] {"1", DateTime.Now, "", 1}); data.Rows.Add(new object[] {"2", DateTime.Now, null, 2 }); data.Rows.Add(new object[] {"3", DateTime.Now, "1", 3, "Field" }); data.Rows.Add(new object[] {null, DateTime.Now, "1", 4 }); return data.CreateDataReader(); }
public void CBO_FillObject_string() { var cboTable = new DataTable("CBOTable"); var colValue = Guid.NewGuid().ToString(); cboTable.Columns.Add("StringProp", typeof (String)); cboTable.Rows.Add(colValue); //Assert.AreEqual(12, moq.Object["TestColumn"]); var result = CBO.FillObject<IntPoco>(cboTable.CreateDataReader()); Assert.IsInstanceOf<IntPoco>(result); Assert.IsNotNull(result); Assert.AreEqual(colValue, result.StringProp); }
private DataTable CreateAllProductTable() { DbCommand comm = GenericDataAccess.CreateCommand(); string country = (string)(Session["country"]); string countryn = (string)(Session["countryname"]); // set the stored procedure name comm.CommandText = "SELECT Product.productName,Product.productShortDesc,Product.productPrice,Product.productImagePath,Country.countryName,Vendor.vendorName,Product.productID FROM dbo.Product INNER JOIN dbo.Country ON Product.productCountryId=Country.countryId INNER JOIN dbo.Vendor ON Product.vendorId=Vendor.vendorId;"; comm.CommandType = CommandType.Text; comm.Connection.Open(); // Execute the command and save the results in a DataTable DbDataReader reader = comm.ExecuteReader(); DataTable dt = new DataTable(); dt.Load(reader); DbDataReader reader1 = dt.CreateDataReader(); String table = "<h3 style='margin-left:200px'>Products from All over the World</h3>"; int nop1 = nop; while (reader1.Read()) { if (nop1 == 0) { nop1 = nop; rows += "<br />"; } string PName = reader1.GetString(0); string PSDesc = reader1.GetString(1); double PPrice = reader1.GetDouble(2); if (PPrice > max) { max = PPrice; } string PImg = reader1.GetString(3); string vendor = reader1.GetString(5); int Pid = reader1.GetInt32(6); rows += "<div class='prodtable'><img src=" + PImg + " height=100 width=100 /><br /><b>Name: </b>" + PName + " <br /><b>Vendor: </b>" + vendor + " <br /><b>Price: </b>" + PPrice + "<br /><br /><a class=hButton href='Shopping.aspx?productId="+Pid+"'>View Product</a><br /></div>"; nop1--; } reader.Close(); tableproduct.InnerHtml =table+rows ; return dt; }
private void dgvPopisPoruka_SelectionChanged(object sender, EventArgs e) { if (osvjezi == false) { idPoruke = Int32.Parse(dgvPopisPoruka.Rows[dgvPopisPoruka.SelectedRows[0].Index].Cells[0].Value.ToString()); SqlConnection myConnection = new SqlConnection(@"Data Source=31.147.204.119\PISERVER,1433;Initial Catalog=17015_DB;Integrated Security=False;User ID=17015_User;Password=546YQ8yy;Connect Timeout=15;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"); SqlDataAdapter myDataAdapter = new SqlDataAdapter("SELECT id_poruka, sadrzaj FROM poruka WHERE id_poruka = " + idPoruke, myConnection); DataTable dataTable = new System.Data.DataTable(); myDataAdapter.Fill(dataTable); using (IDataReader reader = dataTable.CreateDataReader()) while (reader.Read()) { outputSadrzajPoruke.Text = reader.GetString(reader.GetOrdinal("sadrzaj")); } } }
public static void SetFillCode(DevExpress.XtraEditors.LookUpEdit lookup, System.Data.DataTable dtSource, string DisplayMember, string ValueMember, CaptoinStyle style, int AutoSearchColumnIndex) { try { DataTable dt = dtSource.Clone(); DataRow r = dt.NewRow(); if (style == CaptoinStyle.NullString) { r[ValueMember] = ""; r[DisplayMember] = ""; dt.Rows.Add(r); } else if (style == CaptoinStyle.SelectText) { r[ValueMember] = ""; r[DisplayMember] = "선택하세요"; dt.Rows.Add(r); } dt.Load(dtSource.CreateDataReader()); lookup.Properties.SearchMode = DevExpress.XtraEditors.Controls.SearchMode.AutoComplete; lookup.Properties.DataSource = dt; lookup.Properties.DisplayMember = DisplayMember; lookup.Properties.ValueMember = ValueMember; lookup.Properties.AutoSearchColumnIndex = AutoSearchColumnIndex; if (dt.Rows.Count > 0) { lookup.EditValue = dt.Rows[0][ValueMember].ToString(); } } catch (Exception ex) { throw ex; } }
// todo - fix the code that breaks this //[Test] public void should_throw_if_more_than_one_record_is_found() { var task = TaskFactory<FetchSingleOf<MockObject>>.Create(); var table = new DataTable(); table.Columns.Add("Name", Type.GetType("System.String")); table.Columns.Add("Age", Type.GetType("System.Int32")); table.Rows.Add(new object[] { "John Doe", "21" }); table.Rows.Add(new object[] { "John Doe", "21" }); var mockSelectCommand = new Mock<IDbCommand>(); mockSelectCommand.Setup(command => command.ExecuteReader()).Returns(table.CreateDataReader()); task.SelectCommand = mockSelectCommand.Object; // Act & Assert Assert.Throws(typeof(SingleNotFoundException), task.Execute); }
public int Import(DataTable table) { IDataReader dataReader = table.CreateDataReader(); ReadingRepository.Reading(dataReader); IEnumerable<DemandView> demandList = ReadingRepository.DataList.Where(x => CityPermissions.FirstOrDefault(y => y == x.City) != null); if (demandList.Count() > 0) { foreach (DemandView demand in demandList) { int id = DemandRepository.SaveDemand(demand); int weekNum = (int)Math.Ceiling((DateTime.Now - new DateTime(DateTime.Now.Year, 1, 1)).Days / (float)7); //progressRepository.SaveProgress(id, weekNum, demand.ProgressDescription); } } return demandList.Count(); }
public List <Entity.User> LoadListMapData(System.Data.DataTable data) { //Map the entity data... System.Data.DataTableReader reader = data.CreateDataReader(); List <Entity.User> list = new List <User>(data.Rows.Count); //Record set... while (reader.Read()) { list.Add( new User( Int32.Parse(reader[UserMap.Names.id].ToString()), reader[UserMap.Names.sqlSession].ToString(), new UserRole( Int32.Parse(reader[UserMap.Names.roleId].ToString()), reader[UserMap.Names.roleName].ToString(), reader[UserMap.Names.roleDescription].ToString() ), new UserIdentity( reader[UserMap.Names.email].ToString(), reader[UserMap.Names.userGreeting].ToString(), reader[UserMap.Names.userName].ToString(), reader[UserMap.Names.firstName].ToString(), reader[UserMap.Names.lastName].ToString(), reader[UserMap.Names.lastLoginText].ToString(), reader[UserMap.Names.createdText].ToString(), reader[UserMap.Names.editedText].ToString(), DateTime.Parse(reader[UserMap.Names.lastLogin].ToString()), DateTime.Parse(reader[UserMap.Names.created].ToString()), DateTime.Parse(reader[UserMap.Names.edited].ToString()), reader[UserMap.Names.editor].ToString(), Boolean.Parse(reader[UserMap.Names.active].ToString()) ))); } if (reader != null) { reader.Dispose(); } return(list); }
public List <Entity.SQLEnumeration> EnumerationMapData(System.Data.DataTable data) { System.Data.DataTableReader reader = data.CreateDataReader(); List <Entity.SQLEnumeration> list = new List <SQLEnumeration>(data.Rows.Count); while (reader.Read()) { list.Add(new SQLEnumeration( Int16.Parse(reader[UserMap.Names.enumValue].ToString()), reader[UserMap.Names.enumName].ToString(), reader[UserMap.Names.enumDescription].ToString(), Int16.Parse(reader[UserMap.Names.enumSort].ToString()) )); } if (reader != null) { reader.Dispose(); } return(list); }
public Entity.User SaveUserMapData(System.Data.DataTable data) { //Map the entity data... System.Data.DataTableReader reader = data.CreateDataReader(); Entity.User user = null; //Single record... if (reader.Read()) { user = new User( Int32.Parse(reader[UserMap.Names.id].ToString()), reader[UserMap.Names.sqlSession].ToString(), new UserRole( Int32.Parse(reader[UserMap.Names.roleId].ToString()), reader[UserMap.Names.roleName].ToString(), reader[UserMap.Names.roleDescription].ToString() ), new UserIdentity( reader[UserMap.Names.email].ToString(), reader[UserMap.Names.userGreeting].ToString(), reader[UserMap.Names.userName].ToString(), reader[UserMap.Names.firstName].ToString(), reader[UserMap.Names.lastName].ToString(), reader[UserMap.Names.lastLoginText].ToString(), reader[UserMap.Names.createdText].ToString(), reader[UserMap.Names.editedText].ToString(), DateTime.Parse(reader[UserMap.Names.lastLogin].ToString()), DateTime.Parse(reader[UserMap.Names.created].ToString()), DateTime.Parse(reader[UserMap.Names.edited].ToString()), reader[UserMap.Names.editor].ToString(), Boolean.Parse(reader[UserMap.Names.active].ToString()) )); } if (reader != null) { reader.Dispose(); } return(user); }
public Entity.Session <NullT> InitializeMapData(System.Data.DataTable data) { //Map the entity data... System.Data.DataTableReader reader = data.CreateDataReader(); Entity.Session <NullT> init = new Session <NullT>(); //Single record... if (reader.Read()) { init.SessionOk = false; init.ClientMessage = String.Empty; init.ServerMessage = String.Empty; init.SqlKey = reader[UserMap.Names.privateKey].ToString(); init.AppOnline = Boolean.Parse(reader[UserMap.Names.appOnline].ToString()); init.SqlAuthorization = Boolean.Parse(reader[UserMap.Names.sqlAuthorization].ToString()); init.WinAuthorization = Boolean.Parse(reader[UserMap.Names.winAuthorization].ToString()); } if (reader != null) { reader.Dispose(); } return(init); }
public AEAAI03(System.Data.DataTable dtTmp, bool IsNewdata) { InitializeComponent(); IsNewData = IsNewdata; dt.Load(dtTmp.CreateDataReader()); }
public void Process() { // use the following method to update the status on the AsyncProcessorDetail // in the AsyncProcessManager AsyncProcessManager.GetProcessorDetail(ProcessorDetail.ID).UpdateStatusText("Processing has started"); try { int datasets = 0; int errorCount = 0; int processed = 0; int total = 0; //PRIMER PASO //Nos aseguramos que el usuario que va a subir la información tiene una sesión abierta System.Web.Security.MembershipUser usr = System.Web.Security.Membership.GetUser(); if (usr == null) { AsyncProcessManager.FinalizeProcess(ProcessorDetail.ID); } //SEGUNDO PASO //Comprobamos que en los parámetros de importación están definidos el campo Fecha e Indicador así como //evaluar los atributos definidos para la importación int?colFecha = null; int?colValor = null; List <importColumns> jsonDimensions = (List <importColumns>)Newtonsoft.Json.JsonConvert.DeserializeObject <List <importColumns> >(ProcessorDetail.Info); List <DimensionPrompt> vDimensions = new List <DimensionPrompt>(); foreach (importColumns elem in jsonDimensions) { switch (elem.attrid) { case -3: colValor = elem.column; break; case -2: colFecha = elem.column; break; case -1: break; default: DimensionPrompt NuevaDimension = new DimensionPrompt(); NuevaDimension.tablecolumn = elem.column; NuevaDimension.dimensionid = elem.attrid; NuevaDimension.tablename = elem.name; NuevaDimension.importmode = 0; vDimensions.Add(NuevaDimension); break; } } List <importAttributes> jsonAttributes = (List <importAttributes>)Newtonsoft.Json.JsonConvert.DeserializeObject <List <importAttributes> >(ProcessorDetail.Atributtes); foreach (importAttributes elem in jsonAttributes) { DimensionPrompt NuevaDimension = new DimensionPrompt(); NuevaDimension.dimensionid = elem.id; NuevaDimension.valor = elem.name; NuevaDimension.importmode = 1; vDimensions.Add(NuevaDimension); } //Nos aseguramos que existe el campo Fecha y el campo valor if ((!colValor.HasValue) || (!colFecha.HasValue)) { AsyncProcessManager.FinalizeProcess(ProcessorDetail.ID); } //TERCER PASO //Comprobamos que el fichero se subió correctamente y que por lo tanto existe en el disco //duro para leer string path = System.Web.Hosting.HostingEnvironment.MapPath("~/uploads/imports/" + usr.ProviderUserKey.ToString() + "/" + ProcessorDetail.FileName); if (!System.IO.File.Exists(path)) { AsyncProcessManager.FinalizeProcess(ProcessorDetail.ID); } string strData = string.Empty; using (System.IO.StreamReader streamReader = new System.IO.StreamReader(path, System.Text.Encoding.UTF8)) { strData = streamReader.ReadToEnd(); } System.Data.DataTable dtDatos = (System.Data.DataTable)Newtonsoft.Json.JsonConvert.DeserializeObject(strData, (typeof(System.Data.DataTable))); System.Data.DataTableReader dtrDatos = dtDatos.CreateDataReader(); DateTime?dFechaActual = null; decimal? dValorActual = null; string sSelectA, sSelectB, sComa, sNombre; while (dtrDatos.Read()) { //Nueva fila a procesar total++; //Inicializamos la búsqueda sSelectA = string.Empty; sSelectB = string.Empty; sNombre = string.Empty; sComa = string.Empty; //Para cada dimension especificada buscamos aquellos datasets en las que coinciden todas sus dimensiones using (Clases.cKPI_DATASETS objDataset = new Clases.cKPI_DATASETS()) { foreach (DimensionPrompt DimensionActual in vDimensions) { if (DimensionActual.importmode == 0) { DimensionActual.valor = dtrDatos[DimensionActual.tablecolumn].ToString().Replace("'", ""); sNombre = sNombre + sComa + DimensionActual.tablename + " = " + dtrDatos[DimensionActual.tablecolumn].ToString().Replace("'", ""); } else if (DimensionActual.importmode == 1) { sNombre = sNombre + sComa + DimensionActual.valor; } sSelectA = sSelectA + DimensionActual.DimensionSelect(); sSelectB = sSelectB + sComa + DimensionActual.dimensionid.ToString(); sComa = ", "; } objDataset.indicatorid = ProcessorDetail.IndicatorId; objDataset.userid = Convert.ToInt32(usr.ProviderUserKey); if (!objDataset.bExiste(sSelectA, sSelectB)) { datasets++; objDataset.indicatorid = ProcessorDetail.IndicatorId; objDataset.userid = Convert.ToInt32(usr.ProviderUserKey); objDataset.nombre = sNombre; objDataset.dimension = "L"; objDataset.importid = ProcessorDetail.ID; if (objDataset.bInsertar()) { foreach (DimensionPrompt DimensionActual in vDimensions) { using (Clases.cKPI_DIMENSION_VALUES objDimensionValues = new Clases.cKPI_DIMENSION_VALUES()) { objDimensionValues.datasetid = objDataset.datasetid; objDimensionValues.dimensionid = DimensionActual.dimensionid; objDimensionValues.codigo = DimensionActual.valor; objDimensionValues.bInsertarFromImport(); } } } } using (Clases.cKPI_DATASET_VALUES objValor = new Clases.cKPI_DATASET_VALUES()) { objValor.indicatorid = ProcessorDetail.IndicatorId; objValor.datasetid = objDataset.datasetid.Value; objValor.userid = Convert.ToInt32(usr.ProviderUserKey); objValor.importid = ProcessorDetail.ID; dFechaActual = ConvertirStringToFecha(dtrDatos[colFecha.Value].ToString()); dValorActual = ConvertirStringToDecimal(dtrDatos[colValor.Value].ToString()); if ((dFechaActual.HasValue) && (dValorActual.HasValue)) { switch (objDataset.dimension) { case "D": objValor.fecha = dFechaActual; objValor.valor = dValorActual; if (objValor.bGuardarLibre(ProcessorDetail.Modo)) { processed++; } else { errorCount++; } break; case "s": objValor.ejercicio = dFechaActual.Value.Year; objValor.mes = dFechaActual.Value.Month; if (dFechaActual.Value.Day < 8) { objValor.semana = 1; } else if (dFechaActual.Value.Day < 15) { objValor.semana = 2; } else if (dFechaActual.Value.Day < 22) { objValor.semana = 3; } else { objValor.semana = 4; } objValor.valor = dValorActual; if (objValor.bGuardarSemana(ProcessorDetail.Modo)) { processed++; } else { errorCount++; } break; case "Q": objValor.ejercicio = dFechaActual.Value.Year; objValor.mes = dFechaActual.Value.Month; objValor.quincena = dFechaActual.Value.Day < 16 ? 1 : 2; objValor.valor = dValorActual; if (objValor.bGuardarQuincena(ProcessorDetail.Modo)) { processed++; } else { errorCount++; } break; case "M": objValor.ejercicio = dFechaActual.Value.Year; objValor.mes = dFechaActual.Value.Month; objValor.valor = dValorActual; if (objValor.bGuardarMes(ProcessorDetail.Modo)) { processed++; } else { errorCount++; } break; case "T": objValor.ejercicio = dFechaActual.Value.Year; if (dFechaActual.Value.Month < 4) { objValor.trimestre = 1; } else if (dFechaActual.Value.Month < 7) { objValor.trimestre = 2; } else if (dFechaActual.Value.Month < 10) { objValor.trimestre = 3; } else { objValor.trimestre = 4; } objValor.valor = dValorActual; if (objValor.bGuardarTrimestre(ProcessorDetail.Modo)) { processed++; } else { errorCount++; } break; case "S": objValor.ejercicio = dFechaActual.Value.Year; objValor.semestre = dFechaActual.Value.Month < 7 ? 1 : 2; objValor.valor = dValorActual; if (objValor.bGuardarSemestre(ProcessorDetail.Modo)) { processed++; } else { errorCount++; } break; case "A": objValor.ejercicio = dFechaActual.Value.Year; objValor.valor = dValorActual; if (objValor.bGuardarEjercicio(ProcessorDetail.Modo)) { processed++; } else { errorCount++; } break; case "L": objValor.fecha = dFechaActual; objValor.valor = dValorActual; if (objValor.bGuardarLibre(ProcessorDetail.Modo)) { processed++; } else { errorCount++; } break; } } else { errorCount++; if (!dFechaActual.HasValue) { InsertaDetalleError(total, dtrDatos[colFecha.Value].ToString(), "El valor especificado no es un campo de FECHA válido"); } if (!dValorActual.HasValue) { InsertaDetalleError(total, dtrDatos[colValor.Value].ToString(), "El valor especificado no es un campo de NÚMERO válido"); } } } } // in loops, call the UpdateStatus method to determine if you should update the counts // on the current iteration. This will save processing time so your not updating the // counts on every iteration if (UpdateStatus(processed, total)) { // use the following method to update the counts on the AsyncProcessorDetail // in the AsyncProcessManager. This should be included inside any worker loops // you may have or between code blocks to update the user AsyncProcessManager.GetProcessorDetail(ProcessorDetail.ID).UpdateCounts(processed, errorCount, 0, total); } } using (Clases.cKPI_IMPORTS objImport = new Clases.cKPI_IMPORTS()) { objImport.importid = ProcessorDetail.ID; objImport.num_datasets = datasets; objImport.num_data_ok = processed; objImport.num_data_error = errorCount; objImport.finalizado = true; objImport.bModificar(); } // check to see if the process has been cancelled by calling AsyncProcessManager.Continue // this method should be called within an UpdateStatus condition within any loops you have // and should be followed by a break; if (!AsyncProcessManager.Continue(ProcessorDetail.ID)) { AsyncProcessManager.GetProcessorDetail(ProcessorDetail.ID).UpdateStatusText("Processing cancelled"); } AsyncProcessManager.GetProcessorDetail(ProcessorDetail.ID).UpdateStatusText("Processing is complete"); } catch (Exception ex) { string error = ex.Message + ex.StackTrace; } AsyncProcessManager.FinalizeProcess(ProcessorDetail.ID); }
private void WriteDataSetToExcelFile(DataSet ds, String ExcelFile) { DataTableReader dtr = null; System.Data.DataTable dt = null; DataTableReader dsr = null; Application excelApplication = null; Workbook excelWorkbook = null; Worksheet excelWorksheet = null; try { dtr = ds.CreateDataReader(); dt = dtr.GetSchemaTable(); dsr = dt.CreateDataReader(); Int32 ColumnSize = dtr.VisibleFieldCount; String[] ColumnNames = new String[ColumnSize]; Int32 index = 0; while (dsr.Read()) { ColumnNames[index] = dsr.GetString(0); index++; } excelApplication = new Application(); excelApplication.DisplayAlerts = false; //excelApplication.Visible = true; excelWorkbook = excelApplication.Workbooks.Add(Type.Missing); excelWorksheet = (Worksheet)excelWorkbook.Sheets[1]; excelApplication.Calculation = XlCalculation.xlCalculationManual; Int32 ColIdx = 1; Int32 RowIdx = 1; foreach (String ColumnName in ColumnNames) { excelWorksheet.Cells[RowIdx, ColIdx] = ColumnName; ColIdx++; } ColIdx = 1; RowIdx = 2; Int32 Maxrows = ds.Tables[0].Rows.Count; if (dtr.Read()) { for (ColIdx = 1; ColIdx <= ColumnSize; ColIdx++) { if (dtr.GetFieldType(ColIdx - 1) == typeof(String)) { ((Range)excelWorksheet.Cells[RowIdx, ColIdx]).EntireColumn.NumberFormat = "@"; } else if (dtr.GetFieldType(ColIdx - 1) == typeof(Decimal)) { ((Range)excelWorksheet.Cells[RowIdx, ColIdx]).EntireColumn.NumberFormat = "#,##0.00_);(#,##0.00)"; } else if (dtr.GetFieldType(ColIdx - 1) == typeof(DateTime)) { ((Range)excelWorksheet.Cells[RowIdx, ColIdx]).EntireColumn.NumberFormat = "m/d/yyyy"; } else { ((Range)excelWorksheet.Cells[RowIdx, ColIdx]).EntireColumn.NumberFormat = "General"; } excelWorksheet.Cells[RowIdx, ColIdx] = dtr.GetValue(ColIdx - 1); } RowIdx++; } while (dtr.Read()) { for (ColIdx = 1; ColIdx <= ColumnSize; ColIdx++) { excelWorksheet.Cells[RowIdx, ColIdx] = dtr.GetValue(ColIdx - 1); } RowIdx++; } excelApplication.Calculation = XlCalculation.xlCalculationAutomatic; excelWorkbook.SaveAs(ExcelFile, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlShared, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); } catch (Exception e) { throw e; } finally { if (excelWorkbook != null) { excelWorkbook.Close(Type.Missing, Type.Missing, Type.Missing); excelWorkbook = null; } if (excelApplication != null) { excelApplication.DisplayAlerts = true; excelApplication.Quit(); excelApplication = null; } if (dsr != null) { dsr.Close(); dsr.Dispose(); dsr = null; } if (dt != null) { dt.Dispose(); dt = null; } if (dtr != null) { dtr.Close(); dtr.Dispose(); dtr = null; } } }