CODEWITHSUNDEEP
c#sqldatabase
Kiwimoisi
Kiwimoisi

Reputation: 4182

c# database retrieving informations

I would like to know something .

I try to retrieve 2 files .

One is register for a group 2 , and one for a group 10 .

So the field is Files.Group .

One user is register to the group 1 and the group 10.

This is the query I use to retrieve files .

SELECT Files.Id, Files.Name, Files.Date, Files.Path, Files.[Group] FROM Files WHERE Files.[Group] = " + param + "ORDER BY Files.Id DESC"

Param is a cookie who get the group, creating a chain like this 2|10 .

This doesn't work actually.. And i don't know how can I pass in the query the two groups. Should I separate them by a coma ? like Files.Group = 2,10 ?

Or is it something else ? To pass 2 parameters ?

Upvotes: 1

Views: 237

Answers (4)

Jonathan Dickinson
Jonathan Dickinson

Reputation: 9218

Baseline Structure

I don't have your entire structure so I have created the following simplified version of it:

CREATE TABLE [dbo].[Files]
(
  [ID] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  [Name] NVARCHAR(64) NOT NULL,
  [Group] INT NOT NULL -- Probably have a non-unique index over this.
);
GO

INSERT INTO [dbo].[Files] ([Name], [Group]) VALUES (N'My File 1', 1);
INSERT INTO [dbo].[Files] ([Name], [Group]) VALUES (N'My File 2', 2);
INSERT INTO [dbo].[Files] ([Name], [Group]) VALUES (N'My File 3', 3);
INSERT INTO [dbo].[Files] ([Name], [Group]) VALUES (N'My File 4', 2);
INSERT INTO [dbo].[Files] ([Name], [Group]) VALUES (N'My File 5', 3);
INSERT INTO [dbo].[Files] ([Name], [Group]) VALUES (N'My File 6', 5);

Temp Table

You can insert the split values into a temp table and use a WHERE EXISTS against it - probably yielding decent performance.

-- This would be passed in from C#.
DECLARE @GroupsParam NVARCHAR(64) = N'2|3';

-- This is your SQL command, possibly a SPROC.
DECLARE @GroupsXML XML = N'<split><s>' + REPLACE(@GroupsParam, N'|', N'</s><s>') + '</s></split>';

-- Create an in-memory temp table to hold the temp data.
DECLARE @Groups TABLE
(
    [ID] INT PRIMARY KEY
);

-- Insert the records into the temp table.
INSERT INTO @Groups ([ID])
    SELECT x.value('.', 'INT')
    FROM @GroupsXML.nodes('/split/s') as records(x);

-- Use a WHERE EXISTS; which should have extremely good performance.
SELECT [F].[Name], [F].[Group] FROM [dbo].[Files] AS [F]
    WHERE EXISTS (SELECT 1 FROM @Groups AS [G] WHERE [G].[ID] = [F].[Group]);

Table-Values Parameters (SQL 2008+ Only)

SQL 2008 has a neat feature where you can send tables as parameters to the database. Clearly this will only work if you are using SqlCommands correctly (Executing Parameterized SQL Statements), unlike your example (appending user-created values to a SQL string is extremely bad practice - learn how to use parameters) - as you need to pass in a DataTable which you can't do with a simple string value.

In order to use this you first need to create the value type:

CREATE TYPE [dbo].[IntList] AS TABLE
    ([Value] INT);
GO

Next we will do things properly and used a stored procedure - as this is a static query and there are some performance implications of using a sproc (query plan caching).

CREATE PROCEDURE [dbo].[GetFiles]
    @Groups [dbo].[IntList] READONLY
AS BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    SELECT [F].[Name], [F].[Group] FROM [dbo].[Files] AS [F]
        WHERE EXISTS (SELECT 1 FROM @Groups AS [G] WHERE [G].[Value] = [F].[Group]);
END
GO

Next we need to hit this from C#, which is pretty straight-forward as we can create a table to do the call.

public static void GetFilesByGroups(string groupsQuery)
{
    GetFilesByGroups(groupsQuery.Split('|').Select(x => int.Parse(x)));
}

public static void GetFilesByGroups(params int[] groups)
{
    GetFilesByGroups((IEnumerable<int>)groups);
}

public static void GetFilesByGroups(IEnumerable<int> groups)
{
    // Create the DataTable that will contain our groups values.
    var table = new DataTable();
    table.Columns.Add("Value", typeof(int));
    foreach (var group in groups)
        table.Rows.Add(group);

    using (var connection = CreateConnection())
    using (var command = connection.CreateCommand())
    {
        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = "[dbo].[GetFiles]";

        // Add the table like any other parameter.
        command.Parameters.AddWithValue("@Groups", table);

        using (var reader = command.ExecuteReader())
        {
            // ...
        }
    }
}

Remember: Table-Valued Parameters are only supported on SQL 2008 and later.

Edit: I would like to point out that there is likely a cross-over point in terms of performance between dknaack's answer and the temp table approach. His will likely be faster for a small set of search-groups; where the temp table approach would probably be faster for a large set of search-groups. There is a possibility that table-valued parameters would nearly always be faster. This is all just theory based on what I know about how the SQL query engine works: temp table might do a merge or hash join where the TVP would hopefully do a nested loop. I haven't done any profiling (and haven't received enough upvotes to motivate me to do so) so I can't say for certain.

Upvotes: 5

dknaack
dknaack

Reputation: 60438

Description

You should use SqlParameter to prevent Sql injections. Use the IN Statetment to pass in a comma seperated list of you group ids.

Sample

// value from cookie
string groups = "2,10,99";

// Build where clause and params
List<string> where = new List<string>();
List<SqlParameter> param = new List<SqlParameter>();
foreach(string group in groups.Split(','))
{
    int groupId = Int32.Parse(group);
    string paramName = string.Format("@Group{0}", groupId);
    where.Add(paramName);
    param.Add(new SqlParameter(paramName, groupId));
}

// create command
SqlConnection myConnection = new SqlConnection("My ConnectionString");
SqlCommand command = new SqlCommand("SELECT Files.Id, Files.Name, Files.Date, " +
                            "Files.Path, Files.[Group] " +
                            "FROM Files " +
                            "WHERE Files.[Group] in (" + string.Join(",", param) + ")" +
                            "ORDER BY Files.Id DESC", myConnection);
command.Parameters.AddRange(param.ToArray());

More Information

Upvotes: 3

aF.
aF.

Reputation: 66687

You need to set Param in cookie to create a chain like 2,10.

Then, instead of using = you need to use in () like this:

SELECT Files.Id, Files.Name, Files.Date, Files.Path, Files.[Group] FROM Files WHERE Files.[Group] in (" + param + ") ORDER BY Files.Id DESC"

Another thing that you got wrong was missing a space in param + "ORDER part.

Upvotes: 1

David M
David M

Reputation: 72840

You're probably (depending on your database) looking at using this:

IN (2, 10)

rather than an = operator.

Note that constructing SQL using string concatenation like this can expose your code to SQL injection vulnerabilities, and using a properly parameterised SQL query is generally better practice. However, in your case, where you have an indeterminate number of parameters, it is harder to achieve in practice.

Upvotes: 1

Related Questions