sql

Querying results of a stored procedure

I have a stored procedure that returns a large number of results, and would like a better way to debug/ parse the results than copy/pasting into excel or whatever - is there a way to pass the results of the procedure into a query? e.g., if the procedure call was something like: exec database..proc 'arg1','arg2','arg3' my thought was ...

Multiple queries to avoid joins?

I've noticed as soon as I added joins to some of my queries the time it took to execute these was more than just completing multiple queries. Times include page load and averaged over 20 page loads. 7-9 queries with no joins 159ms 3 queries with 2 joins 235ms Should I just go ahead with multiple queries instead of the joins conside...

MySQL statement combining a join and a count?

I've got a table of 'folders'. I want to return all the records with the userId of 16. SELECT * FROM `folders` WHERE userId = 16; I've got a table of 'files'. For each 'folder' returned above, I want to return a count of 'files' within that 'folder'. SELECT COUNT(*) as "Files" FROM files WHERE Folder = n; How do I combine these? I'...

How to retrieve all inserted rows during a given month using T-Sql

I'm trying to retrieve all rows inserted during a specific month. SELECT dbo.Post.UserId, dbo.Post.Tags, dbo.Post.CommentCount, dbo.Post.Status, dbo.Post.PostDate, dbo.Post.[Content], dbo.Post.Title, dbo.Post.PostId, dbo.[User].DisplayName FROM dbo.Post INNER JOIN dbo.[User] ON db...

Entity framework getting around unsupported methods

ADO.NET EF does not support things like Math.Pow and Math.Log so I was wondering how I can get around this. I need to be able to use an ORDER BY on a calculated value using ADO.NET EF. ...

Is there a function in MySQL that will compress the results returned from a query ?

I would like to compress the results if it reached a certain number of rows. The client program would then uncompress the result and process. This a desktop with a client/server architecture, some clients connects through vpn. When a client connects, it will send data to the server. After the server has done processing, the client wil...

Should I use foreign keys?

Duplicate of What’s wrong with foreign keys? I use MS Sql Server with a large database about 4 GB data. I search around the web why I should use foreign keys. by now I only indexed the keys used to join tables. Performance is all fine, dataintegrety is no problem. Should I use foreign keys? Will I get even more performance with fo...

How do you perform an AND with a join?

I have the following data structure and data: CREATE TABLE `parent` ( `id` int(11) NOT NULL auto_increment, `name` varchar(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `parent` VALUES(1, 'parent 1'); INSERT INTO `parent` VALUES(2, 'parent 2'); CREATE TABLE `other` ( `id` int(11) NOT NUL...

Parent-child relationship with LINQ2SQL and POCO objects

I just started learning LINQ2SQL and one of the first things I wanted to try is a simple parent-child hierarchy, but I can't seem to find a good way to do it. I saw some examples here on SO and i've googled, but I couldn't apply them directly, so I'll explain exactly what i'm trying to accomplish. Lets use the common example with tags. ...

SQL Server: How do you return the column names from a table?

Hi, Please can someone show me how I would return the column names of a table using SQL server 2008? i.e. a table contains the columns id, name, address, country and I want to return these as data? Thank you ...

Most complete ORM with LINQ support?

I'm looking for an ORM that offers complete or near-complete LINQ support. LINQ to SQL - Supports about everything inside of LINQ (.Contains, Math.Log, etc) - Cannot eager load relationship properties without creating a new datacontext ADO.NET Entity Framework - Terrible LINQ support (lots of missing features). - Great mapping features...

SQL Writing Library

Is anyone aware of a such a thing as a SQL writing library for .Net? I'm thinking it would be wrapper for generating queries in an object oriented style. Maybe something like the following: class SelectQuery : BaseQuery { List<Column> SelectedColumns { get; set; } Table MainTable { get; set; } List<Join> Joins { get; set; } ...

How to convert a SQL subquery to a join

I have two tables with a 1:n relationship: "content" and "versioned-content-data" (for example, an article entity and all the versions created of that article). I would like to create a view that displays the top version of each "content". Currently I use this query (with a simple subquery): SELECT t1.id, t1.title, t1.con...

Find a combination of two elements that have not been viewed together (LINQ, SQL or C#)

Hi, I have a page that displays two objects and then the user picks one of these. I record the preference and the combination in a MSSQL database and end up storing data like this: UserId=1, BetterObjectId=1, WorseObjectId=2 Now I would like to avoid showing that combination of objects (1,2 / 2,1) ever again. So how do I generate ra...

Should the data access layer contain business logic?

I've seen a trend to move business logic out of the data access layer (stored procedures, LINQ, etc.) and into a business logic component layer (like C# objects). Is this considered the "right" way to do things these days? If so, does this mean that some database developer positions may be eliminated in favor of more middle-tier codi...

SQL: How to display records for only 30 days

I tried to use SELECT * from Results WHERE DATEDIFF(d,Date,getdate())<30 But it seem having error with this. For each record submitted will only display 30 days. May I know if my syntax is correct? Greatly Appreciated, Stan ...

What is SQL injection?

Can someone explain SQL injection? How does it cause vulnerabilities? Where exactly is the point where SQL is injected? Duplicate http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain Too many to list (Google link) ...

Is it possible to supersede the ORDER BY clause in mysql?

By that I mean... if I have a column named "special" and rows that are marked 1 in that column would appear at the top of the results, even if the ORDER BY clause would list them elsewhere. For example, I display items by date, in descending order. Items that are marked special=1 would appear at the top of the results, regardless of thei...

Perform INSERT with SELECT to insert multiple records.

In the diagram below there is a 1:1 relationship between 'DodgyOldTable' and 'MainTable'. Table 'Option' contains records with 'OptionVal1', 'OptionVal2' and 'OptionVal3' in the 'OptionDesc' field. I need to do an insert into MainTable_Option with a select from DodgyOldTable. Something like this: INSERT MainTable_Option ([MainTableID],[...

"The wrong number of parameters does not match number of values for stored procdure" System.Data.Common.DbCommand nuance.

This could be an sql server database setup issue, but I am not to sure where to start looking. I have a stored procedure : CREATE PROCEDURE aStoredProcedure @dteSince DATETIME = null AS ... The c# code to call the stored procedure is :- using (IDataReader dr = database.ExecuteReader("aStoredProcedure")) { ... The c# code works fin...