Search Results

Search found 137 results on 6 pages for 'datareader'.

Page 5/6 | < Previous Page | 1 2 3 4 5 6  | Next Page >

  • Contradictory MySqlReader errors

    - by Lazlo
    MySqlCommand command = connection.CreateCommand(); command.CommandText = string.Format("SELECT * FROM characters WHERE account_id = '{0}'", this.ID); MySqlDataReader reader = command.ExecuteReader(); while (filler.Reader.Read()) { ... } I get an error at the last line saying "Invalid attempt to Read when reader is closed." Now, if I add another line before it, as in: MySqlCommand command = connection.CreateCommand(); command.CommandText = string.Format("SELECT * FROM characters WHERE account_id = '{0}'", this.ID); MySqlDataReader reader = command.ExecuteReader(); reader = command.ExecuteReader(); // Here. while (filler.Reader.Read()) { ... } I get an error at that new line saying "There is already an open DataReader associated with this Connection which must be closed first." Alright, I don't want to get picky here, but is my reader open or closed?

    Read the article

  • Generic function to handle DataType Conversion

    - by Tommo1977
    I have the following function in C#, that I want to pass a MySQL DataReader value/object. If the value of the object passed is a DBNull I need to handle it and return a Null value of that data type e.g null int. Otherwise I want to convert the returned value like Convert.ToInt32(value) or similar. public static T ConvertFromDB<T>(object value) { return value == DBNull.Value ? default(T) : (T)value; } Can anyone offer any help on this ?

    Read the article

  • C# mysqlreader on same connection error

    - by dominiquel
    Hi, I must find a way to do this in C#, if possible... I must loop on my folder list (mysql table), and for each folder I instanciate I must do another query, but when I do this it says : "There is already an open DataReader associated with this Connection" and I am inside a mysqlreader loop already. Note that I have oversimplified the code just to show you, the fact is that I must do queries inside a mysqlreader loop, and it looks to be impossible as they are on the same connection? MySqlConnection cnx = new MySqlConnection(connexionString); cnx.Open(); MySqlCommand command= new MySqlCommand("SELECT * FROM folder WHERE folder_id = " + id, cnx); MySqlDataReader reader= commande.ExecuteReader(); while (reader.Read()) { this.folderList[this.folderList.Length] = new CFolder(reader.GetInt32"folder_id"), cnx); } reader.Close(); cnx.Close();

    Read the article

  • Is closing/disposing an SqlDataReader needed if you are already closing the sqlconnection?

    - by Brian
    I noticed This question, but my question is a bit more specific. Is there any advantage to using using (SqlConnection conn = new SqlConnection(conStr)) { using (SqlCommand command = new SqlCommand()) { // dostuff } } instead of using (SqlConnection conn = new SqlConnection(conStr)) { SqlCommand command = new SqlCommand(); // dostuff } Obviously it does matter run more than one command with the same connection, since closing an SqlDataReader is more efficient than closing and reopening a connection (calling conn.Close();conn.Open(); will also free up the connection). I see many people insist that failure to close the DataReader means leaving open connection resources around, but doesn't that only apply if you don't close the connection?

    Read the article

  • Oracle/c#: How do i use bind variables with select statements to return multiple records?

    - by twiga
    I have a question regarding Oracle bind variables and select statements. What I would like to achieve is do a select on a number different values for the primary key. I would like to pass these values via an array using bind values. select * from tb_customers where cust_id = :1 int[] cust_id = { 11, 23, 31, 44 , 51 }; I then bind a DataReader to get the values into a table. The problem is that the resulting table only contains a single record (for cust_id=51). Thus it seems that each statement is executed independently (as it should), but I would like the results to be available as a collective (single table). A workaround is to create a temporary table, insert all the values of cust_id and then do a join against tb_customers. The problem with this approach is that I would require temporary tables for every different type of primary key, as I would like to use this against a number of tables (some even have combined primary keys). Is there anything I am missing?

    Read the article

  • Render asp.net repeater as-and-when chunks of html is ready (streaming)

    - by sash
    The problem is to render huge reports. Basically we have lots of data that get rendered in html reports (mostly using repeater or gridview). As it so happens, the data started out small and now we have tons of it. There are lots of such reports already built so total rewrite is not an option. Heck, the business is not even letting us page the data. Now server memory is shooting up each time we try to render some reports. So the question is - is there some way we can bind data to repeater and have it stream html to browser as and when chunks are ready? That way we hope to not bring all that data into app server at once. I'm thinking we'll use a datareader or something to get parts of data and render it to browser. Any pointers, links, ideas?

    Read the article

  • Delays in .net app when connecting to oracle db using Oracle.DataAccess

    - by chris
    I have a .net desktop app that connects to an oracle database. At times, there are very noticable delays. I ran a trace on the code, and it was always in the DataReader.Read(). I turned on sql tracing, and found the following, which corresponds to the delays I'm seeing: (2128) [23-MAR-2010 13:00:07:310] nsprecv: reading from transport... (2128) [23-MAR-2010 13:00:07:310] nttrd: entry (2128) [23-MAR-2010 13:00:24:655] nttrd: socket 676 had bytes read=2047 (2128) [23-MAR-2010 13:00:24:655] nttrd: exit (2128) [23-MAR-2010 13:00:24:655] nsprecv: 2047 bytes from transport There's about a 14 second pause in there. I'm pretty sure that there's not a problem in the code, but not sure where to look at next. Is there anyone out there with experience with oracle trace that can explain what's going on?

    Read the article

  • How to deal with large result sets with Linq to Entities?

    - by user169867
    I have a fairly complex linq to entities query that I display on a website. It uses paging so I never pull down more than 50 records at a time for display. But I also want to give the user the option to export the full results to Excel or some other file format. My concern is that there could potentially be a large # of records all being loaded into memory at one time to do this. Is there a way to process a linq result set 1 record at a time like you could w/ a datareader so only 1 record is really being kept in memory at a time? I'd appreciate any help. Thanks

    Read the article

  • F# working with while loop

    - by mamu
    I have a datareader and i want to return collection of rows from it, after reading books for like a day i am not able to find out best way to do it in f#. I can do it normal C# way in F# but that is not why i am using f# Here is what i am trying to achieve let values = while reader.Read() do yield reader.GetString(0), reader.GetInt64(1) Above is how i am trying to do all values get collected into values, which could be dictinary or tuples or any collection yield can't be used in while loop but that is what i am trying to do What could be the best way to achieve this

    Read the article

  • Why is this consider bad practice? or is it? (ASP.Net)

    - by user318573
    Would this code be considered bad practice: <div id="sidebar"> <% =DisplayMeetings(12) %> </div> This is a snippet of code from the default.aspx of a small web app I have worked on. It works perfectly well, runs very fast, but as always, I am aware of the fact that just because it works, doesn't mean it is OK. Basically, the DisplayMeetings subroutine outputs a bunch of formatted HTML (an unordered list actually), with no formatting, just the requisite html, and then my CSS performs all the necessary formatting. The data for generating the list comes from an SQL server database (the parameter controls how many rows to return) and I am using stored procedures and datareader for fast access. This keeps my front-end extraordinary simple and clean, imho, and lets me do all the work in VB or C# in a separate module. I could of course use a databound repeater (and probably 6 or more other methods) of accomplishing the same thing, but are they any better? Other than loosing the design-time features of VS2010?

    Read the article

  • A free standing ASP.NET Pager Web Control

    - by Rick Strahl
    Paging in ASP.NET has been relatively easy with stock controls supporting basic paging functionality. However, recently I built an MVC application and one of the things I ran into was that I HAD TO build manual paging support into a few of my pages. Dealing with list controls and rendering markup is easy enough, but doing paging is a little more involved. I ended up with a small but flexible component that can be dropped anywhere. As it turns out the task of creating a semi-generic Pager control for MVC was fairly easily. Now I’m back to working in Web Forms and thought to myself that the way I created the pager in MVC actually would also work in ASP.NET – in fact quite a bit easier since the whole thing can be conveniently wrapped up into an easily reusable control. A standalone pager would provider easier reuse in various pages and a more consistent pager display regardless of what kind of 'control’ the pager is associated with. Why a Pager Control? At first blush it might sound silly to create a new pager control – after all Web Forms has pretty decent paging support, doesn’t it? Well, sort of. Yes the GridView control has automatic paging built in and the ListView control has the related DataPager control. The built in ASP.NET paging has several issues though: Postback and JavaScript requirements If you look at paging links in ASP.NET they are always postback links with javascript:__doPostback() calls that go back to the server. While that works fine and actually has some benefit like the fact that paging saves changes to the page and post them back, it’s not very SEO friendly. Basically if you use javascript based navigation nosearch engine will follow the paging links which effectively cuts off list content on the first page. The DataPager control does support GET based links via the QueryStringParameter property, but the control is effectively tied to the ListView control (which is the only control that implements IPageableItemContainer). DataSource Controls required for Efficient Data Paging Retrieval The only way you can get paging to work efficiently where only the few records you display on the page are queried for and retrieved from the database you have to use a DataSource control - only the Linq and Entity DataSource controls  support this natively. While you can retrieve this data yourself manually, there’s no way to just assign the page number and render the pager based on this custom subset. Other than that default paging requires a full resultset for ASP.NET to filter the data and display only a subset which can be very resource intensive and wasteful if you’re dealing with largish resultsets (although I’m a firm believer in returning actually usable sets :-}). If you use your own business layer that doesn’t fit an ObjectDataSource you’re SOL. That’s a real shame too because with LINQ based querying it’s real easy to retrieve a subset of data that is just the data you want to display but the native Pager functionality doesn’t support just setting properties to display just the subset AFAIK. DataPager is not Free Standing The DataPager control is the closest thing to a decent Pager implementation that ASP.NET has, but alas it’s not a free standing component – it works off a related control and the only one that it effectively supports from the stock ASP.NET controls is the ListView control. This means you can’t use the same data pager formatting for a grid and a list view or vice versa and you’re always tied to the control. Paging Events In order to handle paging you have to deal with paging events. The events fire at specific time instances in the page pipeline and because of this you often have to handle data binding in a way to work around the paging events or else end up double binding your data sources based on paging. Yuk. Styling The GridView pager is a royal pain to beat into submission for styled rendering. The DataPager control has many more options and template layout and it renders somewhat cleaner, but it too is not exactly easy to get a decent display for. Not a Generic Solution The problem with the ASP.NET controls too is that it’s not generic. GridView, DataGrid use their own internal paging, ListView can use a DataPager and if you want to manually create data layout – well you’re on your own. IOW, depending on what you use you likely have very different looking Paging experiences. So, I figured I’ve struggled with this once too many and finally sat down and built a Pager control. The Pager Control My goal was to create a totally free standing control that has no dependencies on other controls and certainly no requirements for using DataSource controls. The idea is that you should be able to use this pager control without any sort of data requirements at all – you should just be able to set properties and be able to display a pager. The Pager control I ended up with has the following features: Completely free standing Pager control – no control or data dependencies Complete manual control – Pager can render without any data dependency Easy to use: Only need to set PageSize, ActivePage and TotalItems Supports optional filtering of IQueryable for efficient queries and Pager rendering Supports optional full set filtering of IEnumerable<T> and DataTable Page links are plain HTTP GET href Links Control automatically picks up Page links on the URL and assigns them (automatic page detection no page index changing events to hookup) Full CSS Styling support On the downside there’s no templating support for the control so the layout of the pager is relatively fixed. All elements however are stylable and there are options to control the text, and layout options such as whether to display first and last pages and the previous/next buttons and so on. To give you an idea what the pager looks like, here are two differently styled examples (all via CSS):   The markup for these two pagers looks like this: <ww:Pager runat="server" id="ItemPager" PageSize="5" PageLinkCssClass="gridpagerbutton" SelectedPageCssClass="gridpagerbutton-selected" PagesTextCssClass="gridpagertext" CssClass="gridpager" RenderContainerDiv="true" ContainerDivCssClass="gridpagercontainer" MaxPagesToDisplay="6" PagesText="Item Pages:" NextText="next" PreviousText="previous" /> <ww:Pager runat="server" id="ItemPager2" PageSize="5" RenderContainerDiv="true" MaxPagesToDisplay="6" /> The latter example uses default style settings so it there’s not much to set. The first example on the other hand explicitly assigns custom styles and overrides a few of the formatting options. Styling The styling is based on a number of CSS classes of which the the main pager, pagerbutton and pagerbutton-selected classes are the important ones. Other styles like pagerbutton-next/prev/first/last are based on the pagerbutton style. The default styling shown for the red outlined pager looks like this: .pagercontainer { margin: 20px 0; background: whitesmoke; padding: 5px; } .pager { float: right; font-size: 10pt; text-align: left; } .pagerbutton,.pagerbutton-selected,.pagertext { display: block; float: left; text-align: center; border: solid 2px maroon; min-width: 18px; margin-left: 3px; text-decoration: none; padding: 4px; } .pagerbutton-selected { font-size: 130%; font-weight: bold; color: maroon; border-width: 0px; background: khaki; } .pagerbutton-first { margin-right: 12px; } .pagerbutton-last,.pagerbutton-prev { margin-left: 12px; } .pagertext { border: none; margin-left: 30px; font-weight: bold; } .pagerbutton a { text-decoration: none; } .pagerbutton:hover { background-color: maroon; color: cornsilk; } .pagerbutton-prev { background-image: url(images/prev.png); background-position: 2px center; background-repeat: no-repeat; width: 35px; padding-left: 20px; } .pagerbutton-next { background-image: url(images/next.png); background-position: 40px center; background-repeat: no-repeat; width: 35px; padding-right: 20px; margin-right: 0px; } Yup that’s a lot of styling settings although not all of them are required. The key ones are pagerbutton, pager and pager selection. The others (which are implicitly created by the control based on the pagerbutton style) are for custom markup of the ‘special’ buttons. In my apps I tend to have two kinds of pages: Those that are associated with typical ‘grid’ displays that display purely tabular data and those that have a more looser list like layout. The two pagers shown above represent these two views and the pager and gridpager styles in my standard style sheet reflect these two styles. Configuring the Pager with Code Finally lets look at what it takes to hook up the pager. As mentioned in the highlights the Pager control is completely independent of other controls so if you just want to display a pager on its own it’s as simple as dropping the control and assigning the PageSize, ActivePage and either TotalPages or TotalItems. So for this markup: <ww:Pager runat="server" id="ItemPagerManual" PageSize="5" MaxPagesToDisplay="6" /> I can use code as simple as: ItemPagerManual.PageSize = 3; ItemPagerManual.ActivePage = 4;ItemPagerManual.TotalItems = 20; Note that ActivePage is not required - it will automatically use any Page=x query string value and assign it, although you can override it as I did above. TotalItems can be any value that you retrieve from a result set or manually assign as I did above. A more realistic scenario based on a LINQ to SQL IQueryable result is even easier. In this example, I have a UserControl that contains a ListView control that renders IQueryable data. I use a User Control here because there are different views the user can choose from with each view being a different user control. This incidentally also highlights one of the nice features of the pager: Because the pager is independent of the control I can put the pager on the host page instead of into each of the user controls. IOW, there’s only one Pager control, but there are potentially many user controls/listviews that hold the actual display data. The following code demonstrates how to use the Pager with an IQueryable that loads only the records it displays: protected voidPage_Load(objectsender, EventArgs e) {     Category = Request.Params["Category"] ?? string.Empty;     IQueryable<wws_Item> ItemList = ItemRepository.GetItemsByCategory(Category);     // Update the page and filter the list down     ItemList = ItemPager.FilterIQueryable<wws_Item>(ItemList); // Render user control with a list view Control ulItemList = LoadControl("~/usercontrols/" + App.Configuration.ItemListType + ".ascx"); ((IInventoryItemListControl)ulItemList).InventoryItemList = ItemList; phItemList.Controls.Add(ulItemList); // placeholder } The code uses a business object to retrieve Items by category as an IQueryable which means that the result is only an expression tree that hasn’t execute SQL yet and can be further filtered. I then pass this IQueryable to the FilterIQueryable() helper method of the control which does two main things: Filters the IQueryable to retrieve only the data displayed on the active page Sets the Totaltems property and calculates TotalPages on the Pager and that’s it! When the Pager renders it uses those values, plus the PageSize and ActivePage properties to render the Pager. In addition to IQueryable there are also filter methods for IEnumerable<T> and DataTable, but these versions just filter the data by removing rows/items from the entire already retrieved data. Output Generated and Paging Links The output generated creates pager links as plain href links. Here’s what the output looks like: <div id="ItemPager" class="pagercontainer"> <div class="pager"> <span class="pagertext">Pages: </span><a href="http://localhost/WestWindWebStore/itemlist.aspx?Page=1" class="pagerbutton" />1</a> <a href="http://localhost/WestWindWebStore/itemlist.aspx?Page=2" class="pagerbutton" />2</a> <a href="http://localhost/WestWindWebStore/itemlist.aspx?Page=3" class="pagerbutton" />3</a> <span class="pagerbutton-selected">4</span> <a href="http://localhost/WestWindWebStore/itemlist.aspx?Page=5" class="pagerbutton" />5</a> <a href="http://localhost/WestWindWebStore/itemlist.aspx?Page=6" class="pagerbutton" />6</a> <a href="http://localhost/WestWindWebStore/itemlist.aspx?Page=20" class="pagerbutton pagerbutton-last" />20</a>&nbsp;<a href="http://localhost/WestWindWebStore/itemlist.aspx?Page=3" class="pagerbutton pagerbutton-prev" />Prev</a>&nbsp;<a href="http://localhost/WestWindWebStore/itemlist.aspx?Page=5" class="pagerbutton pagerbutton-next" />Next</a></div> <br clear="all" /> </div> </div> The links point back to the current page and simply append a Page= page link into the page. When the page gets reloaded with the new page number the pager automatically detects the page number and automatically assigns the ActivePage property which results in the appropriate page to be displayed. The code shown in the previous section is all that’s needed to handle paging. Note that HTTP GET based paging is different than the Postback paging ASP.NET uses by default. Postback paging preserves modified page content when clicking on pager buttons, but this control will simply load a new page – no page preservation at this time. The advantage of not using Postback paging is that the URLs generated are plain HTML links that a search engine can follow where __doPostback() links are not. Pager with a Grid The pager also works in combination with grid controls so it’s easy to bypass the grid control’s paging features if desired. In the following example I use a gridView control and binds it to a DataTable result which is also filterable by the Pager control. The very basic plain vanilla ASP.NET grid markup looks like this: <div style="width: 600px; margin: 0 auto;padding: 20px; "> <asp:DataGrid runat="server" AutoGenerateColumns="True" ID="gdItems" CssClass="blackborder" style="width: 600px;"> <AlternatingItemStyle CssClass="gridalternate" /> <HeaderStyle CssClass="gridheader" /> </asp:DataGrid> <ww:Pager runat="server" ID="Pager" CssClass="gridpager" ContainerDivCssClass="gridpagercontainer" PageLinkCssClass="gridpagerbutton" SelectedPageCssClass="gridpagerbutton-selected" PageSize="8" RenderContainerDiv="true" MaxPagesToDisplay="6" /> </div> and looks like this when rendered: using custom set of CSS styles. The code behind for this code is also very simple: protected void Page_Load(object sender, EventArgs e) { string category = Request.Params["category"] ?? ""; busItem itemRep = WebStoreFactory.GetItem(); var items = itemRep.GetItemsByCategory(category) .Select(itm => new {Sku = itm.Sku, Description = itm.Description}); // run query into a DataTable for demonstration DataTable dt = itemRep.Converter.ToDataTable(items,"TItems"); // Remove all items not on the current page dt = Pager.FilterDataTable(dt,0); // bind and display gdItems.DataSource = dt; gdItems.DataBind(); } A little contrived I suppose since the list could already be bound from the list of elements, but this is to demonstrate that you can also bind against a DataTable if your business layer returns those. Unfortunately there’s no way to filter a DataReader as it’s a one way forward only reader and the reader is required by the DataSource to perform the bindings.  However, you can still use a DataReader as long as your business logic filters the data prior to rendering and provides a total item count (most likely as a second query). Control Creation The control itself is a pretty brute force ASP.NET control. Nothing clever about this other than some basic rendering logic and some simple calculations and update routines to determine which buttons need to be shown. You can take a look at the full code from the West Wind Web Toolkit’s Repository (note there are a few dependencies). To give you an idea how the control works here is the Render() method: /// <summary> /// overridden to handle custom pager rendering for runtime and design time /// </summary> /// <param name="writer"></param> protected override void Render(HtmlTextWriter writer) { base.Render(writer); if (TotalPages == 0 && TotalItems > 0) TotalPages = CalculateTotalPagesFromTotalItems(); if (DesignMode) TotalPages = 10; // don't render pager if there's only one page if (TotalPages < 2) return; if (RenderContainerDiv) { if (!string.IsNullOrEmpty(ContainerDivCssClass)) writer.AddAttribute("class", ContainerDivCssClass); writer.RenderBeginTag("div"); } // main pager wrapper writer.WriteBeginTag("div"); writer.AddAttribute("id", this.ClientID); if (!string.IsNullOrEmpty(CssClass)) writer.WriteAttribute("class", this.CssClass); writer.Write(HtmlTextWriter.TagRightChar + "\r\n"); // Pages Text writer.WriteBeginTag("span"); if (!string.IsNullOrEmpty(PagesTextCssClass)) writer.WriteAttribute("class", PagesTextCssClass); writer.Write(HtmlTextWriter.TagRightChar); writer.Write(this.PagesText); writer.WriteEndTag("span"); // if the base url is empty use the current URL FixupBaseUrl(); // set _startPage and _endPage ConfigurePagesToRender(); // write out first page link if (ShowFirstAndLastPageLinks && _startPage != 1) { writer.WriteBeginTag("a"); string pageUrl = StringUtils.SetUrlEncodedKey(BaseUrl, QueryStringPageField, (1).ToString()); writer.WriteAttribute("href", pageUrl); if (!string.IsNullOrEmpty(PageLinkCssClass)) writer.WriteAttribute("class", PageLinkCssClass + " " + PageLinkCssClass + "-first"); writer.Write(HtmlTextWriter.SelfClosingTagEnd); writer.Write("1"); writer.WriteEndTag("a"); writer.Write("&nbsp;"); } // write out all the page links for (int i = _startPage; i < _endPage + 1; i++) { if (i == ActivePage) { writer.WriteBeginTag("span"); if (!string.IsNullOrEmpty(SelectedPageCssClass)) writer.WriteAttribute("class", SelectedPageCssClass); writer.Write(HtmlTextWriter.TagRightChar); writer.Write(i.ToString()); writer.WriteEndTag("span"); } else { writer.WriteBeginTag("a"); string pageUrl = StringUtils.SetUrlEncodedKey(BaseUrl, QueryStringPageField, i.ToString()).TrimEnd('&'); writer.WriteAttribute("href", pageUrl); if (!string.IsNullOrEmpty(PageLinkCssClass)) writer.WriteAttribute("class", PageLinkCssClass); writer.Write(HtmlTextWriter.SelfClosingTagEnd); writer.Write(i.ToString()); writer.WriteEndTag("a"); } writer.Write("\r\n"); } // write out last page link if (ShowFirstAndLastPageLinks && _endPage < TotalPages) { writer.WriteBeginTag("a"); string pageUrl = StringUtils.SetUrlEncodedKey(BaseUrl, QueryStringPageField, TotalPages.ToString()); writer.WriteAttribute("href", pageUrl); if (!string.IsNullOrEmpty(PageLinkCssClass)) writer.WriteAttribute("class", PageLinkCssClass + " " + PageLinkCssClass + "-last"); writer.Write(HtmlTextWriter.SelfClosingTagEnd); writer.Write(TotalPages.ToString()); writer.WriteEndTag("a"); } // Previous link if (ShowPreviousNextLinks && !string.IsNullOrEmpty(PreviousText) && ActivePage > 1) { writer.Write("&nbsp;"); writer.WriteBeginTag("a"); string pageUrl = StringUtils.SetUrlEncodedKey(BaseUrl, QueryStringPageField, (ActivePage - 1).ToString()); writer.WriteAttribute("href", pageUrl); if (!string.IsNullOrEmpty(PageLinkCssClass)) writer.WriteAttribute("class", PageLinkCssClass + " " + PageLinkCssClass + "-prev"); writer.Write(HtmlTextWriter.SelfClosingTagEnd); writer.Write(PreviousText); writer.WriteEndTag("a"); } // Next link if (ShowPreviousNextLinks && !string.IsNullOrEmpty(NextText) && ActivePage < TotalPages) { writer.Write("&nbsp;"); writer.WriteBeginTag("a"); string pageUrl = StringUtils.SetUrlEncodedKey(BaseUrl, QueryStringPageField, (ActivePage + 1).ToString()); writer.WriteAttribute("href", pageUrl); if (!string.IsNullOrEmpty(PageLinkCssClass)) writer.WriteAttribute("class", PageLinkCssClass + " " + PageLinkCssClass + "-next"); writer.Write(HtmlTextWriter.SelfClosingTagEnd); writer.Write(NextText); writer.WriteEndTag("a"); } writer.WriteEndTag("div"); if (RenderContainerDiv) { if (RenderContainerDivBreak) writer.Write("<br clear=\"all\" />\r\n"); writer.WriteEndTag("div"); } } As I said pretty much brute force rendering based on the control’s property settings of which there are quite a few: You can also see the pager in the designer above. unfortunately the VS designer (both 2010 and 2008) fails to render the float: left CSS styles properly and starts wrapping after margins are applied in the special buttons. Not a big deal since VS does at least respect the spacing (the floated elements overlay). Then again I’m not using the designer anyway :-}. Filtering Data What makes the Pager easy to use is the filter methods built into the control. While this functionality is clearly not the most politically correct design choice as it violates separation of concerns, it’s very useful for typical pager operation. While I actually have filter methods that do something similar in my business layer, having it exposed on the control makes the control a lot more useful for typical databinding scenarios. Of course these methods are optional – if you have a business layer that can provide filtered page queries for you can use that instead and assign the TotalItems property manually. There are three filter method types available for IQueryable, IEnumerable and for DataTable which tend to be the most common use cases in my apps old and new. The IQueryable version is pretty simple as it can simply rely on on .Skip() and .Take() with LINQ: /// <summary> /// <summary> /// Queries the database for the ActivePage applied manually /// or from the Request["page"] variable. This routine /// figures out and sets TotalPages, ActivePage and /// returns a filtered subset IQueryable that contains /// only the items from the ActivePage. /// </summary> /// <param name="query"></param> /// <param name="activePage"> /// The page you want to display. Sets the ActivePage property when passed. /// Pass 0 or smaller to use ActivePage setting. /// </param> /// <returns></returns> public IQueryable<T> FilterIQueryable<T>(IQueryable<T> query, int activePage) where T : class, new() { ActivePage = activePage < 1 ? ActivePage : activePage; if (ActivePage < 1) ActivePage = 1; TotalItems = query.Count(); if (TotalItems <= PageSize) { ActivePage = 1; TotalPages = 1; return query; } int skip = ActivePage - 1; if (skip > 0) query = query.Skip(skip * PageSize); _TotalPages = CalculateTotalPagesFromTotalItems(); return query.Take(PageSize); } The IEnumerable<T> version simply  converts the IEnumerable to an IQuerable and calls back into this method for filtering. The DataTable version requires a little more work to manually parse and filter records (I didn’t want to add the Linq DataSetExtensions assembly just for this): /// <summary> /// Filters a data table for an ActivePage. /// /// Note: Modifies the data set permanently by remove DataRows /// </summary> /// <param name="dt">Full result DataTable</param> /// <param name="activePage">Page to display. 0 to use ActivePage property </param> /// <returns></returns> public DataTable FilterDataTable(DataTable dt, int activePage) { ActivePage = activePage < 1 ? ActivePage : activePage; if (ActivePage < 1) ActivePage = 1; TotalItems = dt.Rows.Count; if (TotalItems <= PageSize) { ActivePage = 1; TotalPages = 1; return dt; } int skip = ActivePage - 1; if (skip > 0) { for (int i = 0; i < skip * PageSize; i++ ) dt.Rows.RemoveAt(0); } while(dt.Rows.Count > PageSize) dt.Rows.RemoveAt(PageSize); return dt; } Using the Pager Control The pager as it is is a first cut I built a couple of weeks ago and since then have been tweaking a little as part of an internal project I’m working on. I’ve replaced a bunch of pagers on various older pages with this pager without any issues and have what now feels like a more consistent user interface where paging looks and feels the same across different controls. As a bonus I’m only loading the data from the database that I need to display a single page. With the preset class tags applied too adding a pager is now as easy as dropping the control and adding the style sheet for styling to be consistent – no fuss, no muss. Schweet. Hopefully some of you may find this as useful as I have or at least as a baseline to build ontop of… Resources The Pager is part of the West Wind Web & Ajax Toolkit Pager.cs Source Code (some toolkit dependencies) Westwind.css base stylesheet with .pager and .gridpager styles Pager Example Page © Rick Strahl, West Wind Technologies, 2005-2010Posted in ASP.NET  

    Read the article

  • CreationName for SSIS 2008 and adding components programmatically

    If you are building SSIS 2008 packages programmatically and adding data flow components, you will probably need to know the creation name of the component to add. I can never find a handy reference when I need one, hence this rather mundane post. See also CreationName for SSS 2005. We start with a very simple snippet for adding a component: // Add the Data Flow Task package.Executables.Add("STOCK:PipelineTask"); // Get the task host wrapper, and the Data Flow task TaskHost taskHost = package.Executables[0] as TaskHost; MainPipe dataFlowTask = (MainPipe)taskHost.InnerObject; // Add OLE-DB source component - ** This is where we need the creation name ** IDTSComponentMetaData90 componentSource = dataFlowTask.ComponentMetaDataCollection.New(); componentSource.Name = "OLEDBSource"; componentSource.ComponentClassID = "DTSAdapter.OLEDBSource.2"; So as you can see the creation name for a OLE-DB Source is DTSAdapter.OLEDBSource.2. CreationName Reference  ADO NET Destination Microsoft.SqlServer.Dts.Pipeline.ADONETDestination, Microsoft.SqlServer.ADONETDest, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91 ADO NET Source Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter, Microsoft.SqlServer.ADONETSrc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91 Aggregate DTSTransform.Aggregate.2 Audit DTSTransform.Lineage.2 Cache Transform DTSTransform.Cache.1 Character Map DTSTransform.CharacterMap.2 Checksum Konesans.Dts.Pipeline.ChecksumTransform.ChecksumTransform, Konesans.Dts.Pipeline.ChecksumTransform, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b2ab4a111192992b Conditional Split DTSTransform.ConditionalSplit.2 Copy Column DTSTransform.CopyMap.2 Data Conversion DTSTransform.DataConvert.2 Data Mining Model Training MSMDPP.PXPipelineProcessDM.2 Data Mining Query MSMDPP.PXPipelineDMQuery.2 DataReader Destination Microsoft.SqlServer.Dts.Pipeline.DataReaderDestinationAdapter, Microsoft.SqlServer.DataReaderDest, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91 Derived Column DTSTransform.DerivedColumn.2 Dimension Processing MSMDPP.PXPipelineProcessDimension.2 Excel Destination DTSAdapter.ExcelDestination.2 Excel Source DTSAdapter.ExcelSource.2 Export Column TxFileExtractor.Extractor.2 Flat File Destination DTSAdapter.FlatFileDestination.2 Flat File Source DTSAdapter.FlatFileSource.2 Fuzzy Grouping DTSTransform.GroupDups.2 Fuzzy Lookup DTSTransform.BestMatch.2 Import Column TxFileInserter.Inserter.2 Lookup DTSTransform.Lookup.2 Merge DTSTransform.Merge.2 Merge Join DTSTransform.MergeJoin.2 Multicast DTSTransform.Multicast.2 OLE DB Command DTSTransform.OLEDBCommand.2 OLE DB Destination DTSAdapter.OLEDBDestination.2 OLE DB Source DTSAdapter.OLEDBSource.2 Partition Processing MSMDPP.PXPipelineProcessPartition.2 Percentage Sampling DTSTransform.PctSampling.2 Performance Counters Source DataCollectorTransform.TxPerfCounters.1 Pivot DTSTransform.Pivot.2 Raw File Destination DTSAdapter.RawDestination.2 Raw File Source DTSAdapter.RawSource.2 Recordset Destination DTSAdapter.RecordsetDestination.2 RegexClean Konesans.Dts.Pipeline.RegexClean.RegexClean, Konesans.Dts.Pipeline.RegexClean, Version=2.0.0.0, Culture=neutral, PublicKeyToken=d1abe77e8a21353e Row Count DTSTransform.RowCount.2 Row Count Plus Konesans.Dts.Pipeline.RowCountPlusTransform.RowCountPlusTransform, Konesans.Dts.Pipeline.RowCountPlusTransform, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b2ab4a111192992b Row Number Konesans.Dts.Pipeline.RowNumberTransform.RowNumberTransform, Konesans.Dts.Pipeline.RowNumberTransform, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b2ab4a111192992b Row Sampling DTSTransform.RowSampling.2 Script Component Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost, Microsoft.SqlServer.TxScript, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91 Slowly Changing Dimension DTSTransform.SCD.2 Sort DTSTransform.Sort.2 SQL Server Compact Destination Microsoft.SqlServer.Dts.Pipeline.SqlCEDestinationAdapter, Microsoft.SqlServer.SqlCEDest, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91 SQL Server Destination DTSAdapter.SQLServerDestination.2 Term Extraction DTSTransform.TermExtraction.2 Term Lookup DTSTransform.TermLookup.2 Trash Destination Konesans.Dts.Pipeline.TrashDestination.Trash, Konesans.Dts.Pipeline.TrashDestination, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b8351fe7752642cc TxTopQueries DataCollectorTransform.TxTopQueries.1 Union All DTSTransform.UnionAll.2 Unpivot DTSTransform.UnPivot.2 XML Source Microsoft.SqlServer.Dts.Pipeline.XmlSourceAdapter, Microsoft.SqlServer.XmlSrc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91 Here is a simple console program that can be used to enumerate the pipeline components installed on your machine, and dumps out a list of all components like that above. You will need to add a reference to the Microsoft.SQLServer.ManagedDTS assembly. using System; using System.Diagnostics; using Microsoft.SqlServer.Dts.Runtime; public class Program { static void Main(string[] args) { Application application = new Application(); PipelineComponentInfos componentInfos = application.PipelineComponentInfos; foreach (PipelineComponentInfo componentInfo in componentInfos) { Debug.WriteLine(componentInfo.Name + "\t" + componentInfo.CreationName); } Console.Read(); } }

    Read the article

  • Ajax Autocomplete Extender

    - by Jason Ulloa
    El objetivo de este post es preparar un ejemplo sobre un tema que es planteado muy frecuentemente en los Foros de MSDN, como realizar un Autocomplete contra una base de datos. Qué requerimos? Antes de poder realizar un Autocomplete debemos tener en cuenta los elementos principales que requerimos para poder hacerlo funcionar, descritos de la siguiente manera: 1. Textbox: Nuestro grandioso amigo Textbox, que será donde el usuario ingresará los datos a buscar. 2. Un Webservice: que contendrá el método que se conectara a la base de datos y devolverá una lista con la información encontrada. 3. Ajax Autocomplete Extender: este es por decirlo así, el elemento más importante. Nos servirá como medio de enlace entre el webservice que expone el método y el textbox recuperando y mostrando los datos en forma de lista desplegable. La implementación Si bien parecierá complicado, crear un autocomplete extender es bastante sencillo. Empezaremos creando un nuevo sitio asp.net, en este sitio agregaremos un textbox y dos controles muy importantes de Ajax el ToolkitScriptManager para controlar el rende rizado de los script de ajax y el AutocompleteExtender que, como mencione anteriormente, será el medio de enlace. Antes de mostrar como quedará el código de lo anterior, explicaré algunas propiedades del AutocompleteExtender para que se entienda de mejor manera: 1. El ServicePath: contiene la ruta relativa al webservice que utilizaremos. 2. MinimumPrefixLength: se refiere al número de caracteres que deben ser digitados antes de iniciar la búsqueda. 3. ServiceMethod: el nombre del metodo de nuestro webservice que se encargará de devolver los datos. 4. EnableCaching: para mantener en cache los datos consultados, obteniendo mayor velocidad. 5. TargetControlID: una de las propiedades más importantes, acá se coloca el nombre del textbox al cual se unirá el Autocomplete 6. CompletionInterval: tiempo que debe transcurrir antes de iniciar con el trabajo de los datos. Una vez, explicadas las propiedades básicas, veamos como queda implementada la primer parte de nuestro autocomplete: <form id="form1" runat="server"> <div> <asp:ToolkitScriptManager ID="manager" runat="server" /> <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> <asp:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server" ServicePath="WebService.asmx" MinimumPrefixLength="1" ServiceMethod="PersonasInfo " EnableCaching="true" TargetControlID="TextBox1" UseContextKey="True" CompletionSetCount="10" CompletionInterval="0"> </asp:AutoCompleteExtender> </div> </form>   Ahora que nuestro código html está completo, es hora de trabajar directamente con nuestro webservice, este deberá contener un método que devuelva una lista o arreglo de datos, los cuales por supuesto, serán traídos desde la base de datos. Antes de implementar este método, debemos asegurarnos de que nuestra clase del webservice tiene habilitados los espacios para ser utilizada [System.Web.Script.Services.ScriptService()] [WebService(Namespace = "http://tempuri.org/")] [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)] public class WebService : System.Web.Services.WebService {}   Ahora si, nuestro metodo principal [WebMethod()] [System.Web.Script.Services.ScriptMethod()] public string[] PersonasInfo(string prefixText, int count) { string connstring = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;   using (SqlConnection conn = new SqlConnection(connstring)) { SqlCommand comando = new SqlCommand("select nombre from personas where nombre LIKE '%' + @param + '%' ", conn); comando.Parameters.AddWithValue("@param", prefixText); SqlDataReader dr = default(SqlDataReader); comando.Connection.Open(); dr = comando.ExecuteReader(); List<string> items = new List<string>();   while (dr.Read()) { items.Add(dr["nombre"].ToString()); } comando.Connection.Close(); return items.ToArray(); } }   Del método anterior no explicaré en profundidad, pues es bastante sencillo. Una consulta a la base de datos utilizando un datareader y devolviendo los datos en una lista como arreglo. Lo más importante serían las 2 primeras líneas [WebMethod()] y el [ScriptMethod()] las cuales habilitan nuestro método para poder ser accedido y utilizado. Por último, el código de ejemplo en C# (VB Autcomplete):

    Read the article

  • How to mock an SqlDataReader using Moq - Update

    - by Simon G
    Hi, I'm new to moq and setting up mocks so i could do with a little help. Title says it all really - how do I mock up an SqlDataReader using Moq? Thanks Update After further testing this is what I have so far: private IDataReader MockIDataReader() { var moq = new Mock<IDataReader>(); moq.Setup( x => x.Read() ).Returns( true ); moq.Setup( x => x.Read() ).Returns( false ); moq.SetupGet<object>( x => x["Char"] ).Returns( 'C' ); return moq.Object; } private class TestData { public char ValidChar { get; set; } } private TestData GetTestData() { var testData = new TestData(); using ( var reader = MockIDataReader() ) { while ( reader.Read() ) { testData = new TestData { ValidChar = reader.GetChar( "Char" ).Value }; } } return testData; } The issue you is when I do reader.Read in my GetTestData() method its always empty. I need to know how to do something like reader.Stub( x => x.Read() ).Repeat.Once().Return( true ) as per the rhino mock example: http://stackoverflow.com/questions/1792984/mocking-a-datareader-and-getting-a-rhino-mocks-exceptions-expectationviolationexc

    Read the article

  • IQueryable<> from stored procedure (entity framework)

    - by mmcteam
    I want to get IQueryable<> result when executing stored procedure. Here is peace of code that works fine: IQueryable<SomeEntitiy> someEntities; var globbalyFilteredSomeEntities = from se in m_Entities.SomeEntitiy where se.GlobalFilter == 1234 select se; I can use this to apply global filter, and later use result in such way result = globbalyFilteredSomeEntities .OrderByDescending(se => se.CreationDate) .Skip(500) .Take(10); What I want to do - use some stored procedures in global filter. I tried: Add stored procedure to m_Entities, but it returns IEnumerable<> and executes sp immediately: var globbalyFilteredSomeEntities = from se in m_Entities.SomeEntitiyStoredProcedure(1234); Materialize query using EFExtensions library, but it is IEnumerable<>. If I use AsQueryable() and OrderBy(), Skip(), Take() and after that ToList() to execute that query - I get exception that DataReader is open and I need to close it first(can't paste error - it is in russian). var globbalyFilteredSomeEntities = m_Entities.CreateStoreCommand("exec SomeEntitiyStoredProcedure(1234)") .Materialize<SomeEntitiy>(); //.AsQueryable() //.OrderByDescending(se => se.CreationDate) //.Skip(500) //.Take(10) //.ToList(); Also just skipping .AsQueryable() is not helpful - same exception. When I put ToList() query executes, but it is too expensive to execute query without Skip(), Take().

    Read the article

  • C# Winforms TabControl elements reading as empty until TabPage selected

    - by Geo Ego
    I have Winform app I am writing in C#. On my form, I have a TabControl with seven pages, each full of elements (TextBoxes and DropDownLists, primarily). I pull some information in with a DataReader, populate a DataTable, and use the elements' DataBindings.Add method to fill those elements with the current values. The user is able to enter data into these elements, press "Save", and I then set the parameters of an UPDATE query using the elements' Text fields. For instance: updateCommand.Parameters.Add("@CustomerName", SqlDbType.VarChar, 100).Value = CustomerName.Text; The problem I have is that once I load the form, all of the elements are apparently considered empty until I select each tab manually. Thus, if I press "Save" immediately upon loading the form, all of the fields on the TabPages that I have not yet selected try to UPDATE with empty data (not nice). As I select each TabPage, those elements will now send their data along properly. For the time being, I've worked out a (very) ugly workaround where I programmatically select each TabPage when the data is populated for the first time, but that's an unacceptable long-term solution. My question is, how can I get all of the elements on the TabPages to return their data properly before the user selects that TabPage?

    Read the article

  • Problem: Sorting for GridView/ObjectDataSource changes depending on page

    - by user148298
    I have a GridView tied to an ObjectDataSource using paging. The paging works fine, except that the sort order changes depending on which page of the results is being viewed. This causes items to reappear on subsequent pages among other issues. I traced the problem to my DAL, which reads a page at a time and then sorts it. Obviously the sorting is going to change as the result set size changes. Is there an improvement to this algorithm. I would like to use a datareader if possible: [System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)] public static WordsCollection LoadForCriteria(string sqlCriteria, int maximumRows, int startRowIndex, string sortExpression) { //DEFAULT SORT EXPRESSION if (string.IsNullOrEmpty(sortExpression)) sortExpression = "OrderBy"; //CREATE THE DYNAMIC SQL TO LOAD OBJECT StringBuilder selectQuery = new StringBuilder(); selectQuery.Append("SELECT"); if (maximumRows > 0) selectQuery.Append(" TOP " + (startRowIndex + maximumRows).ToString()); selectQuery.Append(" " + Words.GetColumnNames(string.Empty)); selectQuery.Append(" FROM sw_Words"); string whereClause = string.IsNullOrEmpty(sqlCriteria) ? string.Empty : " WHERE " + sqlCriteria; selectQuery.Append(whereClause); selectQuery.Append(" ORDER BY " + sortExpression); Database database = Token.Instance.Database; DbCommand selectCommand = database.GetSqlStringCommand(selectQuery.ToString()); //EXECUTE THE COMMAND WordsCollection results = new WordsCollection(); int thisIndex = 0; int rowCount = 0; using (IDataReader dr = database.ExecuteReader(selectCommand)) { while (dr.Read() && ((maximumRows < 1) || (rowCount < maximumRows))) { if (thisIndex >= startRowIndex) { Words varWords = new Words(); Words.LoadDataReader(varWords, dr); results.Add(varWords); rowCount++; } thisIndex++; } dr.Close(); } return results; }

    Read the article

  • C# HTML tags inside GridView

    - by Geo Ego
    I have a SharePoint web part that I wrote in C# which is used to display SQL Server data based on user selections. I pull the data with a DataReader, fill a DataSet with it, and set that DataSet as the DataSource in a GridView and add that control to my page. The only problem I'm facing is that line breaks in the data fields are not being rendered at all. I'm simply getting a block of text that ignores the breaks that are present in the database when it's rendered to HTML. I tried a regex: Regex rgx = new Regex("\n"); string inputStr = Convert.ToString(dr[x]); string outputStr = rgx.Replace(inputStr, "<br />"); newRow[ds3.Tables["Bobst Specs 3"].Columns[x]] = outputStr; While that does detect and replace the newlines, I merely get the text "" with no line breaks. I also tried changing my SQL query to something along the lines of: SELECT REPLACE (fldCustomerName, '. ', '.' + @NewLineChar) This apparently renders more newlines. I can see that they are present, because if I also insert the regex they are affected, but do not create line breaks. I'm not sure how to replace these, and what with, to get the lines to actually break.

    Read the article

  • SQL Server 2000 intermittent connection exceptions on production server - specific environment probl

    - by StickyMcGinty
    We've been having intermittent problems causing users to be forcibly logged out of out application. Our set-up is ASP.Net/C# web application on Windows Server 2003 Standard Edition with SQL Server 2000 on the back end. We've recently performed a major product upgrade on our client's VMWare server (we have a guest instance dedicated to us) and whereas we had none of these issues with the previous release the added complexity that the new upgrade brings to the product has caused a lot of issues. We are also running SQL Server 2000 (build 8.00.2039, or SP4) and the IIS/ASP.NET (.Net v2.0.50727) application on the same box and connecting to each other via a TCP/IP connection. Primarily, the exceptions being thrown are: System.IndexOutOfRangeException: Cannot find table 0. System.ArgumentException: Column 'password' does not belong to table Table. [This exception occurs in the log in script, even though there is clearly a password column available] System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first. [This one is occurring very regularly] System.InvalidOperationException: This SqlTransaction has completed; it is no longer usable. System.ApplicationException: ExecuteReader requires an open and available Connection. The connection's current state is connecting. System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. And just today, for the first time: System.Web.UI.ViewStateException: Invalid viewstate. We have load tested the app using the same number of concurrent users as the production server and cannot reproduce these errors. They are very intermittent and occur even when there are only 8/9/10 user connections. My gut is telling me its ASP.NET - SQL Server 2000 connection issues.. We've pretty much ruled out code-level Data Access Layer errors at this stage (we've a development team of 15 experienced developers working on this) so we think its a specific production server environment issue.

    Read the article

  • How to get IQueryable<> from stored procedure (entity framework)

    - by mmcteam
    I want to get IQueryable<> result when executing stored procedure. Here is peace of code that works fine: IQueryable<SomeEntitiy> someEntities; var globbalyFilteredSomeEntities = from se in m_Entities.SomeEntitiy where se.GlobalFilter == 1234 select se; I can use this to apply global filter, and later use result in such way result = globbalyFilteredSomeEntities .OrderByDescending(se => se.CreationDate) .Skip(500) .Take(10); What I want to do - use some stored procedures in global filter. I tried: Add stored procedure to m_Entities, but it returns IEnumerable<> and executes sp immediately: var globbalyFilteredSomeEntities = from se in m_Entities.SomeEntitiyStoredProcedure(1234); Materialize query using EFExtensions library, but it is IEnumerable<>. If I use AsQueryable() and OrderBy(), Skip(), Take() and after that ToList() to execute that query - I get exception that DataReader is open and I need to close it first(can't paste error - it is in russian). var globbalyFilteredSomeEntities = m_Entities.CreateStoreCommand("exec SomeEntitiyStoredProcedure(1234)") .Materialize<SomeEntitiy>(); //.AsQueryable() //.OrderByDescending(se => se.CreationDate) //.Skip(500) //.Take(10) //.ToList(); Also just skipping .AsQueryable() is not helpful - same exception.

    Read the article

  • Is My DataTable Snippet Written Correctly?

    - by user311509
    public static DataTable GetDataTable(SqlCommand sqlCmd) { DataTable tblMyTable = new DataTable(); DataSet myDataSet = new DataSet(); try { //1. Create connection mSqlConnection = new SqlConnection(mStrConnection); //2. Open connection mSqlConnection.Open(); mSqlCommand = new SqlCommand(); mSqlCommand = sqlCmd; //3. Assign Connection mSqlCommand.Connection = mSqlConnection; //4. Create/Set DataAdapter mSqlDataAdapter = new SqlDataAdapter(); mSqlDataAdapter.SelectCommand = mSqlCommand; //5. Populate DataSet mSqlDataAdapter.Fill(myDataSet, "DataSet"); tblMyTable = myDataSet.Tables[0]; } catch (Exception ex) { } finally { //6. Clear objects if ((mSqlDataAdapter != null)) { mSqlDataAdapter.Dispose(); } if ((mSqlCommand != null)) { mSqlCommand.Dispose(); } if ((mSqlConnection != null)) { mSqlConnection.Close(); mSqlConnection.Dispose(); } } //7. Return DataSet return tblMyTable; } I use the above code to return records from database. The above snippet would run in web application which expected to have around 5000 visitors daily. The records returned reach 20,000 or over. The returned records are viewed (read-only) in paged GridView. Would it be better to use DataReader instead of DataTable? NOTE: two columns in the GridView are hyperlinked.

    Read the article

  • Using SMO to call Database.ExecuteNonQuery() concurrently?

    - by JimDaniel
    I have been banging my head against the wall trying to figure out how I can run update scripts concurrently against multiple databases in a single SQL Server instance using SMO. Our environments have an ever-increasing number of databases which need updating, and iterating through one at a time is becoming a problem (too slow). From what I understand SMO does not support concurrent operations, and my tests have bore that out. There seems to be shared memory at the Server object level, for things like DataReader context, keeps throwing exceptions such as "reader is already open." I apologize for not having the exact exceptions I am getting. I will try to get them and update this post. I am no expert on SMO and just feeling my way through to be honest. Not really sure I am approaching it the right way, but it's something that has to be done, or our productivity will slow to a crawl. So how would you guys do something like this? Am I using the wrong technology with SMO? All I am wanting to do is execute sql scripts against databases in a single sql server instance in parallel. Thanks for any help you can give, Daniel

    Read the article

  • Accessing both stored procedure output parameters AND the result set in Entity Framework?

    - by MS.
    Is there any way of accessing both a result set and output parameters from a stored procedure added in as a function import in an Entity Framework model? I am finding that if I set the return type to "None" such that the designer generated code ends up calling base.ExecuteFunction(...) that I can access the output parameters fine after calling the function (but of course not the result set). Conversely if I set the return type in the designer to a collection of complex types then the designer generated code calls base.ExecuteFunction<T>(...) and the result set is returned as ObjectResult<T> but then the value property for the ObjectParameter instances is NULL rather than containing the proper value that I can see being passed back in Profiler. I speculate the second method is perhaps calling a DataReader and not closing it. Is this a known issue? Any work arounds or alternative approaches? Edit My code currently looks like public IEnumerable<FooBar> GetFooBars( int? param1, string param2, DateTime from, DateTime to, out DateTime? createdDate, out DateTime? deletedDate) { var createdDateParam = new ObjectParameter("CreatedDate", typeof(DateTime)); var deletedDateParam = new ObjectParameter("DeletedDate", typeof(DateTime)); var fooBars = MyContext.GetFooBars(param1, param2, from, to, createdDateParam, deletedDateParam); createdDate = (DateTime?)(createdDateParam.Value == DBNull.Value ? null : createdDateParam.Value); deletedDate = (DateTime?)(deletedDateParam.Value == DBNull.Value ? null : deletedDateParam.Value); return fooBars; }

    Read the article

  • Using Stored Procedures in SSIS

    - by dataintegration
    The SSIS Data Flow components: the source task and the destination task are the easiest way to transfer data in SSIS. Some data transactions do not fit this model, they are procedural tasks modeled as stored procedures. In this article we show how you can call stored procedures available in RSSBus ADO.NET Providers from SSIS. In this article we will use the CreateJob and the CreateBatch stored procedures available in RSSBus ADO.NET Provider for Salesforce, but the same steps can be used to call a stored procedure in any of our data providers. Step 1: Open Visual Studio and create a new Integration Services Project. Step 2: Add a new Data Flow Task to the Control Flow window. Step 3: Open the Data Flow Task and add a Script Component to the data flow pane. A dialog box will pop-up allowing you to select the Script Component Type: pick the source type as we will be outputting columns from our stored procedure. Step 4: Double click the Script Component to open the editor. Step 5: In the "Inputs and Outputs" settings, enter all the columns you want to output to the data flow. Ensure the correct data type has been set for each output. You can check the data type by selecting the output and then changing the "DataType" property from the property editor. In our example, we'll add the column JobID of type String. Step 6: Select the "Script" option in the left-hand pane and click the "Edit Script" button. This will open a new Visual Studio window with some boiler plate code in it. Step 7: In the CreateOutputRows() function you can add code that executes the stored procedures included with the Salesforce Component. In this example we will be using the CreateJob and CreateBatch stored procedures. You can find a list of the available stored procedures along with their inputs and outputs in the product help. //Configure the connection string to your credentials String connectionString = "Offline=False;user=myusername;password=mypassword;access token=mytoken;"; using (SalesforceConnection conn = new SalesforceConnection(connectionString)) { //Create the command to call the stored procedure CreateJob SalesforceCommand cmd = new SalesforceCommand("CreateJob", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SalesforceParameter("ObjectName", "Contact")); cmd.Parameters.Add(new SalesforceParameter("Action", "insert")); //Execute CreateJob //CreateBatch requires JobID as input so we store this value for later SalesforceDataReader rdr = cmd.ExecuteReader(); String JobID = ""; while (rdr.Read()) { JobID = (String)rdr["JobID"]; } //Create the command for CreateBatch, for this example we are adding two new rows SalesforceCommand batCmd = new SalesforceCommand("CreateBatch", conn); batCmd.CommandType = CommandType.StoredProcedure; batCmd.Parameters.Add(new SalesforceParameter("JobID", JobID)); batCmd.Parameters.Add(new SalesforceParameter("Aggregate", "<Contact><Row><FirstName>Bill</FirstName>" + "<LastName>White</LastName></Row><Row><FirstName>Bob</FirstName><LastName>Black</LastName></Row></Contact>")); //Execute CreateBatch SalesforceDataReader batRdr = batCmd.ExecuteReader(); } Step 7b: If you had specified output columns earlier, you can now add data into them using the UserComponent Output0Buffer. For example, we had set an output column called JobID of type String so now we can set a value for it. We will modify the DataReader that contains the output of CreateJob like so:. while (rdr.Read()) { Output0Buffer.AddRow(); JobID = (String)rdr["JobID"]; Output0Buffer.JobID = JobID; } Step 8: Note: You will need to modify the connection string to include your credentials. Also ensure that the System.Data.RSSBus.Salesforce assembly is referenced and include the following using statements to the top of the class: using System.Data; using System.Data.RSSBus.Salesforce; Step 9: Once you are done editing your script, save it, and close the window. Click OK in the Script Transformation window to go back to the main pane. Step 10: If had any outputs from the Script Component you can use them in your data flow. For example we will use a Flat File Destination. Configure the Flat File Destination to output the results to a file, and you should see the JobId in the file. Step 11: Your project should be ready to run.

    Read the article

  • Forms Authentication logs out very quickly , locally works fine !!!

    - by user319075
    Hello to all, There's a problem that i am facing with my hosting company, I use a project that uses FormsAuthentication and the problem is that though it successfully logs in, it logs out VERY QUICKLY, and i don't know what could be the cause of that, so in my web.config file i added those lines: <authentication mode="Forms" > <forms name="Nadim" loginUrl="Login.aspx" defaultUrl="Default.aspx" protection="All" path="/" requireSSL="false"/> </authentication> <authorization> <deny users ="?" /> </authorization> <sessionState mode="StateServer" stateConnectionString="tcpip=localhost:42424" cookieless="false" timeout="1440"> </sessionState> and this is the code i use in my custom login page : protected void PasswordCustomValidator_ServerValidate(object source, ServerValidateEventArgs args) { try { UsersSqlDataSource.SelectParameters.Clear(); UsersSqlDataSource.SelectCommand = "Select * From Admins Where AdminID='" + IDTextBox.Text + "' and Password='" + PassTextBox.Text + "'"; UsersSqlDataSource.SelectCommandType = SqlDataSourceCommandType.Text; UsersSqlDataSource.DataSourceMode = SqlDataSourceMode.DataReader; reader = (SqlDataReader)UsersSqlDataSource.Select(DataSourceSelectArguments.Empty); if (reader.HasRows) { reader.Read(); if (RememberCheckBox.Checked == true) Page.Response.Cookies["Admin"].Expires = DateTime.Now.AddDays(5); args.IsValid = true; string userData = "ApplicationSpecific data for this user."; FormsAuthenticationTicket ticket1 = new FormsAuthenticationTicket(1, IDTextBox.Text, System.DateTime.Now, System.DateTime.Now.AddMinutes(30), true, userData, FormsAuthentication.FormsCookiePath); string encTicket = FormsAuthentication.Encrypt(ticket1); Response.Cookies.Add(new HttpCookie(FormsAuthentication.FormsCookieName, encTicket)); Response.Redirect(FormsAuthentication.GetRedirectUrl(IDTextBox.Text, RememberCheckBox.Checked)); //FormsAuthentication.RedirectFromLoginPage(IDTextBox.Text, RememberCheckBox.Checked); } else args.IsValid = false; } catch (SqlException ex) { ErrorLabel.Text = ex.Message; } catch (InvalidOperationException) { args.IsValid = false; } catch (Exception ex) { ErrorLabel.Text = ex.Message; } Also you will find that line of code: FormsAuthentication.RedirectFromLoginPage(IDTextBox.Text, RememberCheckBox.Checked); is commented because i thought there might be something wrong with the ticket when i log in , so i created it manually , every thing i know i tried but nothing worked, so does anyone have any idea what is the problem ? Thanks in advance, Baher.

    Read the article

< Previous Page | 1 2 3 4 5 6  | Next Page >