Write SQL queries in .Net with full compiler support
Download
CSQL is currently in Alpha. It is free for personal and non-commercial use. For commercial use, please contact info@redstormsoftware.com.Getting Started
To get started, you will need to generate C# classes to model your database schema. You can hand-write these classes, or you can use the provided model generator. Currently the model generator only supports reading
from a .dacpac file (Visual Studio database projects produce a .dacpac file when compiled). The model generator is a command line application that takes 6 parameters:
- the source type (dacpac)
- the path to the dacpac file
- the output file (where the classes will be written to)
- the namespace to place the classes in
- the class declaration for the class that will contain the models (e.g. public static class Northwind)
- how to indent (e.g. a tab or two/four spaces)
The model classes for each table and view look like this:
1: public class Products : Schema<Products>
2: {
3: public readonly ColumnExpression ProductID;
4: public readonly ColumnExpression ProductName;
5: public readonly ColumnExpression SupplierID;
6: public readonly ColumnExpression CategoryID;
7: public readonly ColumnExpression QuantityPerUnit;
8: public readonly ColumnExpression UnitPrice;
9: public readonly ColumnExpression UnitsInStock;
10: public readonly ColumnExpression ReorderLevel;
11: public readonly ColumnExpression Discontinued;
12:
13: public Products()
14: {
15: ProductID = new ColumnExpression(this, "ProductID");
16: ProductName = new ColumnExpression(this, "ProductName");
17: SupplierID = new ColumnExpression(this, "SupplierID");
18: CategoryID = new ColumnExpression(this, "CategoryID");
19: QuantityPerUnit = new ColumnExpression(this, "QuantityPerUnit");
20: UnitPrice = new ColumnExpression(this, "UnitPrice");
21: UnitsInStock = new ColumnExpression(this, "UnitsInStock");
22: ReorderLevel = new ColumnExpression(this, "ReorderLevel");
23: Discontinued = new ColumnExpression(this, "Discontinued");
24: }
25: }
There is one static class to store references to each table/view:
1: public static class Northwind
2: {
3: public static readonly TableOrView<Products> Products = new TableOrView<Products>(new Products(), "Products");
4: public static readonly TableOrView<Categories> Categories = new TableOrView<Categories>(new Categories(), "Categories");
5: public static readonly TableOrView<Orders> Orders = new TableOrView<Orders>(new Orders(), "Orders");
6: public static readonly TableOrView<Region> Region = new TableOrView<Region>(new Region(), "Region");
7: }
Once you have generated the models, you can start writing queries. The easiest way is to use the various extension method on IDbConnection. To use these extension methods, add "using Csql.Runtime.Extensions;" to the top of your file. You can then execute your queries as shown in the third and fourth examples below.
Examples
1: // Simple query, without CSQL:
2: var sqlQuery = "select * from Products";
3: // with CSQL:
4: var csqlQuery = Northwind.Products.Select().Star;
1: // More advanced query with parameters.
2: // CSQL will automatically convert literals and variables into SQL parameters
3: // so that there is no risk of SQL injection.
4: var sqlQuery = "select top 5 ProductId, ProductName from Products where ProductName like @productName order by ProductID";
5: var csqlquery = Northwind.Products
6: .Where(p => p.ProductName.Like("Chef%"))
7: .OrderBy(p => p.ProductID)
8: .Select().Top(5, p => new[] { p.ProductID, p.ProductName });
1: // Use standard connection, command and data classes.
2: using (SqlConnection connection = new SqlConnection(@"connection string"))
3: {
4: connection.Open();
5:
6: using (IDbCommand command = connection.CreateCsqlCommand(Northwind.Products.Select().Star))
7: {
8: using (IDataReader reader = command.ExecuteReader())
9: {
10: while (reader.Read())
11: {
12: Console.WriteLine(reader.GetString(0));
13: }
14: }
15: }
16: }
1: // Or execute a parameterized query in a single line.
2: using (SqlConnection connection = new SqlConnection(@"connection string"))
3: {
4: connection.Open();
5:
6: string name = "Robert'); DROP TABLE students;--";
7: var result = connection.ExecuteScalar(Northwind.Products.Where(p => p.ProductName == name).Select(p => p.ProductID));
8: }
1: // Join.
2: var csqlquery = Northwind.Products
3: .InnerJoin(Northwind.Categories).On((p, c) => c.CategoryID == p.CategoryID)
4: .Where((p, c) => p.UnitPrice == 10.0M)
5: .Select((p, c) => new[] { p.ProductName, c.CategoryName, p.UnitPrice });
1: // Insert.
2: var csqlquery = Op.Insert.Top(5).Percent.Into(Northwind.Products).With(Hint.TabLock)
3: .Columns(p => p.ProductName)
4: .Output(p => p.ProductID)
5: ._(Northwind.Products.Select(p => p.ProductName));
1: // Update.
2: var csqlquery = Op.Update().Top(5).Percent(Northwind.Products).With(Hint.ForceScan)
3: .Set(p => new[]
4: {
5: p.Discontinued.Set(Op.Default),
6: p.ProductName.Set("ABC"),
7: p.ProductName.Write("abcd", 4, 5),
8: p.QuantityPerUnit.PlusEquals(2)
9: })
10: .Output((deleted, inserted) => new[] { deleted.QuantityPerUnit, inserted.QuantityPerUnit })
11: .Where(p => p.QuantityPerUnit > 5).Option(Hint.KeepPlan);
1: // Delete.
2: var csqlquery = Northwind.Products.Delete.Top(10).Percent;
1: // Cursor.
2: LocalVariableReference @cursorName;
3: using (SqlConnection connection = new SqlConnection(@"connection string"))
4: using (IDbCommand command = connection.CreateCsqlCommand(
5: Op.Declare("@cursorName", DataTypes.Cursor, out @cursorName),
6: Op.Set(@cursorName, Op.Cursor.Local.Static.For(Northwind.Products.Select().Star)),
7: Op.Open(@cursorName),
8: Op.While(AtAt.FetchStatus == 0,
9: Op.Begin(
10: Op.Fetch.Next.From(@cursorName)
11: ).End),
12: Op.Close(@cursorName),
13: Op.Deallocate(@cursorName)))
14: {
15: }
1: // Common table expression.
2: var csqlquery = Op.With
3: (
4: Op.Cte(new { ProductName = Op.Column(), CategoryName = Op.Column(), UnitPrice = Op.Column() })
5: .As
6: (
7: ProductAndCategoryNamesOverTenDollars => Northwind.Products
8: .InnerJoin(Northwind.Categories).On((p, c) => c.CategoryID == p.CategoryID)
9: .Where((p, c) => p.UnitPrice > 10.0M)
10: .Select((p, c) => new[] { p.ProductName, c.CategoryName, p.UnitPrice })
11: ),
12: pc =>
13: pc.Select().Star
14: );
Convert Your Own Queries
You can convert your own SQL queries into CSQL using this converter. Although CSQL supports almost all SQL syntax, currently this converter only supports basic SELECT, INSERT, UPDATE and DELETE statements. For best results, the capitalization of all table, column, view, etc. names should match the underlying SQL schema and all column names should be fully qualified.