Generate C# Class from SQL Server Table Without Store Procedure

How do you generate classes in C# from a SQL Server Schema?

Instead of Data Table and Data sets you can use your own objects to represent data in your applications and to do so you can use some persistence frameworks and OR mappers (object relational mappers).For example you can use "Linq to Sql","Microsoft Entity framework" or NHibernate.

There are some code generation tools that let you generate code for these frameworks.

MyGeneration and CodeSmith as two of them that I know.

How can I parameterize an SQL table without vulnerability to SQL injection

An arbitrary table name still has to exist, so you can check first that it does:

IF EXISTS (SELECT 1 FROM sys.objects WHERE name = @TableName)
BEGIN
  ... do your thing ...
END

And further, if the list of tables you want to allow the user to select from is known and finite, or matches a specific naming convention (like dbo.Sales%), or belongs to a specific schema (like Reporting), you could add additional predicates to check for those.

This requires you to pass the table name in as a proper parameter, not concatenate or token-replace. (And please don't use AddWithValue() for anything, ever.)

Once your check that the object is real and valid has passed, then you will still have to build your SQL query dynamically, because you still won't be able to parameterize the table name. You still should apply QUOTENAME(), though, as I explain in these posts:

• Protecting Yourself from SQL Injection in SQL Server - Part 1
• Protecting Yourself from SQL Injection in SQL Server - Part 2

So the final code would be something like:

CREATE PROCEDURE dbo.SelectFromAnywhere
  @TableName sysname 
AS
BEGIN
  IF EXISTS (SELECT 1 FROM sys.objects
      WHERE name = @TableName)
  BEGIN
    DECLARE @sql nvarchar(max) = N'SELECT * 
      FROM ' + QUOTENAME(@TableName) + N';';
    EXEC sys.sp_executesql @sql;
  END
  ELSE
  BEGIN
    PRINT 'Nice try, robot.';
  END
END
GO

If you also want it to be in some defined list you can add

AND @TableName IN (N't1', N't2', …)

Or LIKE <some pattern> or join to sys.schemas or what have you.

Provided nobody has the rights to then modify the procedure to change the checks, there is no value you can pass to @TableName that will allow you to do anything malicious, other than maybe selecting from another table you didn’t expect because someone with too much access was able to create before calling the code. Replacing characters like -- or ; does not make this any safer.

Script is not generated for a stored procedure in Entity Framework

ObjectContext.CreateDatabaseScript() is an old code path as database creation now uses the Migrations pipeline to create schema. For this reason, CreateDatabaseScript doesn't know how to process stored procedures.
Use this code :

var configuration = new DbMigrationsConfiguration
{
    AutomaticMigrationsEnabled = true,
    ContextType = typeof(MyContext),
    MigrationsAssembly = typeof(MyContext).Assembly
};

var migrator = new DbMigrator(configuration);

var scriptor = new MigratorScriptingDecorator(migrator);
string script = scriptor.ScriptUpdate(sourceMigration: null, targetMigration: null);

Console.WriteLine(script);

C# / SQL Server : error when creating stored procedure from C# code

You cannot use GO in a SQL batch with SqlCommand. It is not valid T-SQL, it's just a batch separator used by sqlcmd and SSMS.

Instead you need to split batches by using a new command.

However in this particular instance, you don't actually need two commands. Firstly because you can combine them using CREATE OR ALTER PROCEDURE. Secondly even if you did need to, since you are just doing this via dynamic SQL you can just conditionally build a dynamic SQL script to pass through.

You can also remove the StringBuilder and use a verbatim @"" string instead. This allows you to insert newlines and make good use of whitespace.

Another issue with your current script is that you are not escaping ' for the dynamic SQL. Passing it in as a parameter as I have done avoids this, but if you want to do it all in one big batch you need to make sure to do so.

public void createSP()
{
    const string sqlExec = @"
IF NOT EXISTS (SELECT 1
    FROM sys.procedures
    WHERE name = 'etradeCore')
  exec(@sql);
";

    const string sbSP = @"
CREATE OR ALTER PROCEDURE [dbo].[etradeCore]
  @pazaryeri VARCHAR(50),
  @magaza VARCHAR(50),
  @stkID int,
  @komisyon decimal(9, 4),
  @fiyat_baslangic decimal(9, 4),
  @fiyat_bitis decimal(9, 4),
  @eklenecekfiyat decimal(9, 4)
AS

SET NOCOUNT OFF;

DECLARE @bBilgiID tinyint =
  CASE @pazaryeri
  WHEN 'Trendyol'
    THEN
      CASE @magaza
      WHEN 'Kozmeti'
        THEN 58
      WHEN 'Golden Rose'
        THEN 52
      WHEN 'Ziaja'
        THEN 60
      END
  WHEN 'Hepsiburada'
    THEN
      CASE @magaza
      WHEN 'Kozmeti'
        THEN 43
      WHEN 'Golden Rose'
        THEN 44
      WHEN 'Ziaja'
        THEN 43
      END
  END;

IF NOT EXISTS (SELECT 1
    FROM urnBilgi
    WHERE bVeriID = @stkID
      AND bBilgiID = @bBilgiID
      AND bDeger >= '0')
BEGIN
    INSERT INTO urnBilgi (bVeriID, bBilgiID, bDeger)
    VALUES (@stkID, @bBilgiID, '0');
END
ELSE
BEGIN
    UPDATE b
    SET bDeger = CAST(
      CONVERT(decimal(9, 4),
        (
          u.fiyatS + @eklenecekfiyat
        ) * CONVERT(decimal(9, 4), '1.' + REPLACE(@komisyon, '.', ''))
      ) AS varchar(30))
    FROM urnBilgi b
    INNER JOIN urn u ON u.stkID = b.bVeriID
    WHERE b.bVeriID = @stkID
      AND b.bBilgiID = @bBilgiID
      AND (u.fiyatS BETWEEN @fiyat_baslangic AND @fiyat_bitis);
END
"; 

    using (SqlConnection connection = new SqlConnection(constring))
    using (SqlCommand cmd = new SqlCommand(sqlExec, connection))
    {
        cmd.Parameters.Add("@sql", SqlDbType.NVarChar, -1).Value = sbSP;
        connection.Open();
        cmd.ExecuteNonQuery();
    }
}

Notes:

• SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON should be part of the connection string settings, not part of the batch
• Use sys.procedures instead of sys.objects
• Use EXISTS (SELECT 1 instead of EXISTS (SELECT *
• It's unclear why you are passing @komisyon as a decimal only to remove the . perhaps you should pass an int instead, or perhaps some other way.
• Always declare varchar with a length.
• Why is bDeger a varchar anyway? Should it not be decimal?
• Unclear why you need the inner SELECT fiyatS... subquery in the UPDATE and not just u.fiyatS.
• CommandType.Text is the default.
• There is no need to call Close() if you have a using block (as you should).
• Why is SET NOCOUNT OFF? Normally it's better to leave it on.
• This whole "upsert" procedure should probably be in a transaction, with HOLDLOCK hints, and should probably use @@ROWCOUNT to check.

---

Related Topics