C# - LinqToSQL Design Query?
Oct 28, 2011
I wonder if somebody could point me in the right direction. I've recently started playing with LinqToSQL and love the strongly typed data objects etc.I'm just struggling to understand the impact on database performance etc. For example, say I was developing a simple user profile page. The page shows basic information about the user, some information on their recent activity, and a list of unread notifications.
If I was developing a stored procedure for this page, I could create a single SP which returns multiple datatables covering all of the required information - resulting in a single db call.
However, using LinqToSQL, this could results in many calls - one for user info, atleast one for activity, atleast one for notifications, if I then want further info on notifications this may result in further calls - multiple db calls.
Should I be worried about the number of db calls happenning as a result of using this design pattern? Ie, are the multiple db handshakes etc going to degrade my db etc?
View 2 Replies
ADVERTISEMENT
Aug 16, 2010
I've created an SQL Query using the query builder. In the query builder, you can test the query by clicking the button "Execute Query", and entering the desired values in the parameter fields.
My query returns the correct results when I do this, but when I seemingly pass the same values at runtime, I get 0 records returned.The query is designed such that you can pass nulls if you don't wish to filter data by that particular parameter.
SQL Query:
SELECT ID, UPC, Quantity, Manufacturer, Style, Color, Size, Category, DateReceived, Cost, OriginalRetail, Retail, LocationID
FROM Inventory
WHERE (UPC = @UPC OR
[code]....
In the query builder "Execute Query" dialog, I enter a value I know that the db contains in the "Size" field, "1/1/1900" & "1/1/2199" in Dates A & B, respectively, and nulls for all other fields. This returns 1 record, which is just as I expected. It works correctly.But when I pass the same values into the method created by the designer, I get 0 records returned. I can't figure out where the problem lies. Even if I enter nulls for all fields other than the dates, it returns 0. This tells me that the dates are causing the problem, but I can't figure out why, because I'm passing strings equivalent to what I entered in the query builder.
View 6 Replies
Apr 29, 2009
I am designing a shared add-in using VB.Net 2008 and VSTO that has to fire up when any MS Office App is started. It has to work for both 2002, 2003 and 2007 applications. I have managed to put together a working add-in that pops up a test message stating the version of Office and the Application name and tested this across all the apps ok.
The next part of the solution is to code the application specific code that needs to run for document auditing and corporate protective marking as well as, for example; in Excel checking that all the Add-Ins are linked to the Network Add-Ins folder.
My approach from now was going to be to late bind against the application object and set one of my typed (e.g. Excel) application object references which exist in the code to this object.
View 1 Replies
Feb 23, 2012
I'm going to use an existing query on an SQLCE database on Windows Phone. From what I understand, the only way to access a local db is via Linq2Sql.So, your challenge, if you choose to accept it, is to convert this fairly complicated query to Linq. I have doubts whether the linq driver is smart enough to use the exists functions.The inserts are for context only, to give you an idea of the db structure
DECLARE @OrderProducts TABLE(OrderID INT, ProductID INT);
DECLARE @Products TABLE(ProductID INT, ProductName VARCHAR(32));
INSERT @Products VALUES
[code]....
View 1 Replies
Sep 14, 2010
I'm using LinqToSql to talk to my database. It's working great but I would like to know if there are pending changes. I know how to endedit() and submitchanges() on the underlying context. But is there a property that will tell me if changes have been made? I've looked through the properties and methods of both the bindingsource object and the datacontext but nothing is popping out at me.
View 3 Replies
Nov 2, 2009
I have a DataGridView.datasource bound to LinkToSql. I fill the data based on a sequence number. If the sequence number is not part of the the data, I will need to add a record. This should be easy and make sence, but it would appear, at least to me to be a bit more difficult.The grid will not add a new record if the LinqToSql query is empty
View 6 Replies
Jan 21, 2010
lately I've been experiencing LinqToSql + Sql Server Express 2005, and it was a great journey (muddy) anyway .... I'm at the point that I want to make a Backup/Restore for the database backup for SSE is as easy as a simple copy paste of the mdf file, but the problem that I can't copy the file while the server is using it, server doesn't let the file go untill the app is closed or after about 4 - 6 minutes of idle time,
[Code]...
View 2 Replies
Aug 19, 2010
I have a linqtosql dbml where I dropped a stored procedure into the designer interface.
Stored procedure name:
GetUser(@userid int)
Select * from users_tbl where userid=@userid
[code].....
View 1 Replies
Feb 15, 2011
I'm using LinqToSql classes in an ASP.NET website and let's say I have an entity named Testimonials. Two pages utilize this type - the first being a list of all existing Testimonial records and the second being an add / editor with which to manage the records. [Code] This allows population a DataGrid with the returned items, however on the add / editor page we alter the query ever so slightly: [Code] This query dies, prior to even trying to populate anything, with the following message: [Code]
I have pretty much identical methods for other data objects which have been and still are executing just fine. One immediate difference I can think of is that this table has been recently updated in the database, removed from the dbml designer and re-added to the dbml designer. None of the other tables have undergone this process for some time. [Code] Why is this exception occurring and how can I configure my project appropriately to correct the issue?
View 1 Replies
Sep 8, 2009
I have a smalldatetime column in SQL that can have a null value, but when I try to insert a null value using SqlTypes.SqlDateTime.Null and LINQtoSQL it puts in 1/1/1900 instead of NULL.
What is the best method to insert a null, or am I doing it at the moment. If I am using the correct method at the moment, what should be done to prevent the 1/1/1900 being displayed on my fields in a WPF application.
View 2 Replies
Sep 16, 2009
Dim query = (From p in Parent _
select _
p.ID, _
Tags = String.Join("|", p.Child.Select(Function(c) c.Tag.TagName).ToArray)).Take(100)
In the above query, when using Take to limit the rows returned, a separate SQL query is executed for each row to return the 'Tags' field. If I remove Take(100), a single query to sent to Sql Server.So, how do I limit the number of rows returned, while preventing a new sub query being executed for each row?
View 1 Replies
Nov 26, 2010
I have created my webpage in asp.net in 1024*768 resolution, my problem was that when i change my monitor resolution then the controls in my webpage will be displayed in unmanaged manner .
How to arrange items in my webpage which support multiple resolution ...
Whatever the resolution of my monitor the controls in my webpage will display as it is as managed in 1024*768 reolution !
View 1 Replies
Jun 27, 2011
I seem to have reached a limit on the size of the form. I cannot make the form longer and I need to add more fields. Is there a limit on how many fields can be included in a form?
View 1 Replies
May 25, 2011
I searched codeplex and google. I have found so many such as tustena but unfortunately they are not domain driven based and in these solutions I could not find a good modelling documents or references. i am a newbie in CRM but I am sensetive to design it with solid object-oriented fundamentals. Any reference or open source solution especifically for CRM design and implementaion in .NET? Cheers
View 1 Replies
Aug 23, 2011
On a client i have an anonymous list containing a multi-column primary key previously selected from the DB.Then i need to select all the records from the DB that equals the primary key list i have stored in the memory.[code]
View 1 Replies
Aug 19, 2010
Here is the code:
Function getData(ByVal id As String)
Dim reader As SqlClient.SqlDataReader
Dim statement As String
[code].....
View 1 Replies
Feb 29, 2012
When Creating a query using the sear Criteria Builder . I keep getting the error : "The schema returned by the new query differs from the base query" what does that mean and how do i avoid this problem in future
View 2 Replies
Apr 28, 2010
I am connecting to an Access database and using a parameter query with the LIKE operator to return all rows that match query. The string to search for is taken from a Textbox
sql =
"Select * FROM Allview WHERE Info Like" &
"*" &
CStr(TextBox1.Text) &
"*"
The query does not return any data in vb, but when run from access with same string, there is data returned.The connection to the database is done correctly, as I am able to return data with various other queries.
Partial code :
Dim
con As
New OleDb.OleDbConnection[code]....
View 8 Replies
Apr 7, 2012
I am trying to run a query an Access db from VB. The general query which I want to run is
SELECT * FROM Patient WHERE Patient.PatientID = ?
or SELECT * FROM Patient WHERE Patient.PatientLname = ?
I tried using an input box which captured the user input and pass that variable to the query, but that failed. Then I read about writing a function and using that, however, I keep getting an error which says Function not defined, but when stepping through the code, it seems to work. Here is my function: [Code]
View 1 Replies
Aug 27, 2010
The database:
"ID (Primary key)" | "Title"
0 | "title1"
[code].....
OK, before adding values to database, we should check if a row exists with this values :)TO do this, creating a Stored Procedure is a best way to deal with the database fastly.So... The problem now is, at the runtime, Miss OleDB throw this error:Microsoft Jet database engine cannot find the input table or query 'IF
View 2 Replies
May 29, 2009
i should say hi experts :D . Help me with this pretty code :)
The database:
"ID (Primary key)" | "Title"
0 | "title1"
[code].....
OK, before adding values to database, we should check if a row exists with this values :)TO do this, creating a Stored Procedure is a best way to deal with the database fastly.So... The problem now is, at the runtime, Miss OleDB throw this error:Microsoft Jet database engine cannot find the input table or query 'IF
View 11 Replies
Sep 14, 2010
I have to join two main tables, and I need to filter the results by elements in an ASP.NET web form. These filters are created on the fly so I have to use a lot of where extensions to filter the query. I want to execute the query with as optimized SQL as possible.
I am first doing a simple join between TW_Sites and TW_Investigators. Then there are two sub-tables that are involved. TW_InvestigatorToArea and TW_InvestigatorToDisease. While most of the where clauses are working fine, I have found a performance issue that won't be an issue right now, but will be an issue as the table gets bigger.
The arrays DiseaseCategories and DiseaseAreas would be the results of a CheckBoxList result.
Protected Sub LoadResults()
'Get Dictionary of Filters
Dim FilterDictionary As OrderedDictionary = Session.Item("InvestigatorFilterDictionary")
' Initialize LinqToSql
[code]....
View 2 Replies
Oct 15, 2011
I created a RDCL in the designer. I would like the User to Determine what is shown in the Report. How can I change the query at runtime? (ex. changing the customer Name)
This is the query that I have in the properties:
SELECT [Date], Address, Customer, Orders
FROM Report_qry
WHERE (Customer= 'JohnDoe')
View 5 Replies
Mar 11, 2010
how to take a query that returns a single row of data and load that data into textbox controls.I know about ExecuteScalar but it is only good for a single column of data from the query.
View 2 Replies
Jul 11, 2010
I've got confused at the moment.I've got one database to developed for MLM (Multi level marketing) company.so, there's one Members database.. each member have unique ID called membercode.. and all members can members under them.. can make 2 members down to 1 member.. all members are added in same table named "tbMembers" and for each member there's a "parentID" where I add member's membercode under whome that member is?.. right?
Now what's the matter is .. I want a SQL Query which can go under and under.. like 'John Doe' having two members under him named 'Suzan' and 'Ellie' and 'Suzan' is also having 2 members under her.. and same for Ellie. and for example.. 'John Doe' having about 300 members in his down line? All members are making transactions.. like they purchase product and Invoices are being generated (invoices tables have 'memberID'). Now the query is how to generate query which can calculate John Doe's Downline's TurnOver from invoices table's 'totalAmount' field's SUM?
I'm using Asp.net 2.0 (VB.net) and use SQL Server 2005 Express.
View 2 Replies
Oct 22, 2009
I have created a parameterized query in my tableadapter. Here is the SQL:
SELECT CalcinerID, Comments, CorrectiveActions, CrucibleOxideWt, CrucibleWeight, DCPressure, DCTemperature,
Date, DischargePressure, FeedPressure, FiredCrucibleOxideWt, GmSampleWt, LIMSNumber, LotNumber, MaterialType,
OperatorID, QualLabOperator, QualLabTimeStamp, RecordID, ToteBinNumber [code]....
When I use the Execute Query button in the Tableadapter Query Builder, the query works fine; I get all the rows I expect. However, when I run this in my code, the query behaves like an equality statement (i.e. Lotnumber = @Lotnumber) rather than a LIKE statement (Lotnumber LIKE '%' + @Lotnumber + '%').Here is the code that is not behaving correctly. Note that I have a textbox, LotSearchTextbox, where I enter the portion of the Lotnumber that I want to use as the filter. I also have a button, LotSearchButton:
Private Sub LotSearchButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LotSearchButton.Click
Try
Dim SomeNumber As Integer = Me.LotCalcinerO2TableAdapter.FillByLotNumber(Me.CalcinerDBDataSet.LotCalcinerO2,[code].....
why the query works in the Query Builder but not in the code?
View 4 Replies
May 17, 2012
I have a data table that looks like this:
USERNAME ICOUNT IAVERAGE DATE_LOGGED
LAGX01 1245 1245 05-07-2012
LAGX02 2211 1422 05-07-2012
LAGX03 1698 1112 05-07-2012
LAGX04 4598 1555 05-07-2012
LAGX05 2589 3245 05-07-2012
LAGX06 3321 1155 05-07-2012
LAGX07 3698 3458 05-07-2012
LAGX08 2589 4587 05-07-2012
LAGX09 1598 1142 05-07-2012
LAGX10 3156 1987 05-07-2012
LAGX11 5547 2011 05-07-2012
LAGX12 9456 3459 05-07-2012
Now, I want to execute a sql query that will display the top 5 based on average, so i did this:
SELECT DISTINCT USERNAME,IAVERAGE FROM myTable WHERE IAVERAGE > 0 AND DATE_LOGGED='05-07-2012' ORDER BY IAVERAGE LIMIT 0,5 and the result is:
USERNAME ICOUNT IAVERAGE DATE_LOGGED
LAGX03 1698 1112 05-07-2012
LAGX09 1598 1142 05-07-2012
LAGX06 3321 1155 05-07-2012
LAGX01 1245 1245 05-07-2012
LAGX02 2211 1422 05-07-2012
Now, I want to numberize my query result, so it will look like this:
RANK USERNAME ICOUNT IAVERAGE DATE_LOGGED
1 LAGX03 1698 1112 05-07-2012
2 LAGX09 1598 1142 05-07-2012
3 LAGX06 3321 1155 05-07-2012
4 LAGX01 1245 1245 05-07-2012
5 LAGX02 2211 1422 05-07-2012
View 4 Replies
Oct 31, 2011
I have 7 company Divisions that sit in an access database.I then use this list to populate two comboboxes in my windows form.However I get a syntax error in my SQL Query if the Division name is more than one word.For example in my access database I have two columns one is a status column and the other is a division column. If for example the Division is called 'Corporate' the status column is called 'Status Corporate' and the division column is called 'Corporate'.The SQL query works fine in this scenario.However if I have a Division that is more than one word, lets say 'Operations Division' I get an error in my SQL Query that says -
Quote: Syntax error (missing operator) in query expression 'Operations Division'.Here is my code
sqlNewTenderDetails = "SELECT " & Me.DivisionBox.SelectedValue & " From BidRefCodes Where " & "[Status " & Me.DivisionBox.SelectedValue & "='Active'"
daNewTenderDetails = New OleDb.OleDbDataAdapter(sqlNewTenderDetails, NewTenderDetailsTableCon)[code]....
View 3 Replies
Nov 2, 2010
I've recently been trying to figure out a way to get parameterized queries to work and i think i'm almost there but seem to be getting an error when executing my query
Here's the code
[code]...
View 1 Replies
Aug 24, 2009
I want to query an access database with variable data defined by the user. The "Search" criteria would be in a textbox. I made a query like
SELECT askID, Question
FROM chita
WHERE (askID LIKE 'Textbox10.text%')
and referring to this query as
Form1.ChitaTableAdapter.searchrec(Form1.ChitaDataSet1.chita)But id does not take the value of the textbox, instead it takes the Textbox10.text as a value. Is it possible to refer to it like this, or should I Linq?
[Code]...
View 13 Replies
