C#-Windows-SQL-DB.md

Using SQL Database from .NET (C#)

Requirements

.NET Framework including System.Data and System.Data.SqlClient.

Install the required modules

.NET Framework is pre-installed with Windows. For Linux and Mac OS X you can download .NET Framework from the Mono Project.

Create a database and retrieve your connection string

See the getting started page to learn how to create a sample database and retrieve your connection string.

Connect to your SQL Database

The System.Data.SqlClient.SqlConnection class is used to connect to SQL Database.

using System.Data.SqlClient;
 
class Sample
{
  static void Main()
  {
                  using(var conn = new SqlConnection("Server=tcp:yourserver.database.windows.net,1433;Database=yourdatabase;User ID=yourlogin@yourserver;Password={your_password_here};Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"))
                  {
                                  conn.Open();  
                  }
  }
}             

Execute a query and retrieve the result set

The System.Data.SqlClient.SqlCommand and SqlDataReader classes can be used to retrieve a result set from a query against SQL Database. Note that System.Data.SqlClient also supports retrieving data into an offline System.Data.DataSet.

using System;
using System.Data.SqlClient;
 
class Sample
{
                static void Main()
                {
                  using(var conn = new SqlConnection("Server=tcp:yourserver.database.windows.net,1433;Database=yourdatabase;User ID=yourlogin@yourserver;Password={your_password_here};Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"))
                                {
                                                var cmd = conn.CreateCommand();
                                                cmd.CommandText = @"
                                                                                SELECT
                                                                                                c.CustomerID
                                                                                                ,c.CompanyName
                                                                                                ,COUNT(soh.SalesOrderID) AS OrderCount
                                                                                FROM SalesLT.Customer AS c
                                                                                LEFT OUTER JOIN SalesLT.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID
                                                                                GROUP BY c.CustomerID, c.CompanyName
                                                                                ORDER BY OrderCount DESC;";
 
                                                conn.Open();    
                               
                                                using(var reader = cmd.ExecuteReader())
                                                {
                                                                while(reader.Read())
                                                                {
                                                                                Console.WriteLine("ID: {0} Name: {1} Order Count: {2}", reader.GetInt32(0), reader.GetString(1), reader.GetInt32(2));
                                                                }
                                                }                                                                             
                                }
                }
}
 

Inserting a row, passing parameters, and retrieving the generated primary key value

In SQL Database the IDENTITY property and the SEQUENECE object can be used to auto-generate primary key values. In this example you will see how to execute an insert-statement, safely pass parameters which protects from SQL injection, and retrieve the auto-generated primary key value.

The ExecuteScalar method in the System.Data.SqlClient.SqlCommand class can be used to execute a statement and retrieve the first column and row returned by this statement. The OUTPUT clause of the INSERT statement can be used to return the inserted values as a result set to the calling application. Note that OUTPUT is also supported by the UPDATE, DELETE and MERGE statements. If more than one row is inserted you should use the ExecuteReader method to retrieve the inserted values for all rows.

class Sample
{
    static void Main()
    {
                                using(var conn = new SqlConnection("Server=tcp:yourserver.database.windows.net,1433;Database=yourdatabase;User ID=yourlogin@yourserver;Password={your_password_here};Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"))
        {
            var cmd = conn.CreateCommand();
            cmd.CommandText = @"
                INSERT SalesLT.Product (Name, ProductNumber, StandardCost, ListPrice, SellStartDate)
                OUTPUT INSERTED.ProductID
                VALUES (@Name, @Number, @Cost, @Price, CURRENT_TIMESTAMP)";
 
            cmd.Parameters.AddWithValue("@Name", "SQL Server Express");
            cmd.Parameters.AddWithValue("@Number", "SQLEXPRESS1");
            cmd.Parameters.AddWithValue("@Cost", 0);
            cmd.Parameters.AddWithValue("@Price", 0);
 
            conn.Open();
 
            int insertedProductId = (int)cmd.ExecuteScalar();
 
            Console.WriteLine("Product ID {0} inserted.", insertedProductId);
        }
    }
}

Transactions

            cursor.execute("BEGIN TRANSACTION")
            cursor.execute("DELETE FROM test WHERE value = 10;")
            cnxn.rollback()

Executing Stored Procedures

            with pymssql.connect("yourserver", "yourusername", "yourpassword", "yourdatabase") as conn:
with conn.cursor(as_dict=True) as cursor:
    cursor.execute("""
    CREATE PROCEDURE FindName
        @name VARCHAR(100)
    AS BEGIN
        SELECT * FROM test WHERE name = @name
    END
    """)
    cursor.callproc('FindPerson', ('NodeJS',))
    for row in cursor:
        print("Name=%s, Votes=%d" % (row['name'], row['value']))