Quick Start SQL Server and MySQL

This is a quick start provided by one of our customers for connecting to Microsoft SQL Server, Microsoft LocalDB and MySQL.

The sample database here is AdventureWorksLT2012 downloaded from Microsoft. (Save AdventureWorksLT2012_Data.mdf to the data folder of the SQL Server, and attach it to the server)

Let us start with the Microsoft SQL Server.

1. We use a C# console project. Add references to KellermanSoftware-Common.dll and KellermanSoftware.NET-Data-Access-Layer.dll, as usual.

2. Add App.config to the project, and supply a connection string, for example, as follows:

<?xml version="1.0" encoding="utf-8" ?>

<add name="ApplicationServices"

connectionString="Data Source=MYPC\SQLEXPRESS;Initial Catalog=AdventureWorksLT2012;Integrated Security=True;" />

</connectionStrings>

</appSettings>

</configuration>

3. Create a class from the data table. Here we choose the Customer table in the database, and create the Customer.cs in the project folder.

IDataHelper db = DataHelper.SessionFactory();

db.UserName = "Your Name 123456";

db.LicenseKey = "Your Lic Key";

db.OpenConnection();

db.CreateClassFromTable("AWLT", "Customer", @"..\..\Customer.cs");

db.CloseConnection();

The source file, Customer.cs, contains the Customer class defined as:

[TableMapping("Customer")]

public class Customer

{

. . . .

}

Change the table name of the mapping from “Customer” to the real table name in the database “SalesLT.Customer”.

[TableMapping("SalesLT.Customer")]

public class Customer

{

. . . .

}

Add this file to the project.

4. Let’s get those with the first names starting with “J” by using LINQ in the friendly format.

IDataHelper db = DataHelper.SessionFactory();

db.UserName = "Your Name 123456";

db.LicenseKey = "Your Lic Key";

db.OpenConnection();

var query = from x in db.CreateQuery<Customer>()

where x.FirstName.StartsWith("J")

select x;

foreach (Customer x in query)

Console.WriteLine(x.FirstName + " " + x.LastName);

db.CloseConnection();

5. In case you may prefer not to use App.config, you can make the connection by hand.

SqlServerProvider provider = new SqlServerProvider();

provider.ConnectionString

= "Data Source=MYPC\\SQLEXPRESS;”

+”Initial Catalog=AdventureWorksLT2012;”

+”Integrated Security=True;";

DataHelper db = new DataHelper(provider);

6. If you have LocalDB, a new version of SQL CE, you can use it in the same manner by changing the server name to “Data Source=(localdb)\v11.0”. Use the tool “Import and Export Data” to move the sample database from MS SQL to LocalDB.

7. In case of MySQL, App.config may be like this:

<?xml version="1.0" encoding="utf-8" ?>

<add name="ApplicationServices"

connectionString="server=yourmysqlserver;User Id=UserName;password=P@ssw0rd;Persist Security Info=True;database=YourSQL;" />

</connectionStrings>

</appSettings>

</configuration>

Here is the alternative by hand.

MySqlProvider provider_MySQL = new MySqlProvider();

provider_MySQL.ConnectionString

= "server=yourmysqlserver;User Id=UserName;”

+ “password=P@ssw0rd;”

+ ”Persist Security Info=True;database=YourSQL;";

DataHelper db_MySQL = new DataHelper(provider_MySQL);