HOME  QUICKSTART   DOWNLOADS  CONTACT  

QUICKSTART
 
This small tutorial aims to explain how to use Denki-SQL.
Any other documentation being for the moment non-existent (beta), this tutorial is the only explanation… But the use of Denki-SQL is not very complicated…
This tutorial is divided into two parts, but the second part show more advanced features (overlaps)…
To start it is of course necessary to have a minimum of software and knowledge, below the list:
 
 
REQUIRED
Before starting, you should have installed:
  • Microsoft .NET Framework 2 or higher...
  • Visual Studio 2005
  • SQL Server 2000 or 2005 (client+server)
  • Denki-SQL
And it is required to know:
  • How to create a new database
  • Some knowledge with T-SQL language
  • Some knowledge with a .NET language
 
 
Next, to create our sample database:
  • Open your SQL Server manager (client)
  • Create a new database named MyBookStore
  • Execute this script (tables + constraints + datas)
Result diagram below:
 
 
I'm sorry for the poor quality of my sample base, I will make it better later;)
 
Ready? Choose "CASE STUDY 1" to start.
 
 
CASE STUDY 1
  • Create a new "Denki-SQL" scenario
  • Save a "Denki-SQL" scenario
  • Generate with "Denki-SQL", and set up codes in your project
  • "Simple SELECT"
  • "Simple INSERT"
  • "Simple UPDATE"
  • "Simple DELETE"
Estimated time: 10 min.
 
Go
 
 
CASE STUDY 2
  • Open a existing "Denki-SQL" scenario
  • Add a ascent and a filter
  • Generate with "Denki-SQL", and set up codes in your project
  • "Imbricated SELECT"
  • "Imbricated EXTAND SELECT"
  • "Imbricated UPDATE"
  • "Imbricated DELETE"
Estimated time: 10 min.
 
Go
 
 
 

CASE STUDY 1
 
In this first situation, we will explore the basic tasks of Denki-SQL.
1) Create a new "Denki-SQL" scenario
  • Start Denki-SQL.
  • Choose "New" in the "File" menu.
  • Set up your SQL connection and click on "Connect".
  • Choose the "MyBookStore" database and click on "Analyse".
  • Click on "OK" to continue.
 
You can populate the treeview to visit, but it's not use in this case.
2) Save a "Denki-SQL" scenario
A important characteristics of "Denki-SQL" is that you can save your scenario (using XML format), to be able to change it later…
  • Choose "Save as..." in the "File" menu.
  • Choose a directory, a name for the scenario, then click on "Save".
 
3) Generate with "Denki-SQL", and set up codes in your project
All is ready to generate code!
  • Choose "Generate code" in the "File" menu.
  • Choose a directory then click on "Generate" (a sub-folder will be create).
Two sub-folders appear: "SQL" and "VB.NET" (or "C#")
  • The "SQL" folder contains T-SQL script, execute-it on your sample database to create all stored procedures.
  • The "VB.NET" (or "C#") folder containts a sub-folder "MyDataClass", this folder contains .NET files).
Now, we are going to create a winform project to test this...
  • Start Visual Studio 2005 and create a new "Windows Application" project.
  • Do a copy/past of the "MyDataClass" folder.
  • Maximize the form (ref. "Form1.vb" or "Form1.cs") and drop a "DataGridView" component and also a "button".
 
4) "Simple SELECT"
  • Double-Click on the button "button1" to handle the "click" event.
  • Copy and paste this code (replace the connection string by yours):
 
VB.NET C#
 
 
' Your connection string.
Dim MyConnString As String = "Initial Catalog=MyBookStore; Data Source={yourServerName}; Integrated Security=False; User ID={yourLogin}; Password={yourPassword}"  
 
' Instantiate a new select "Books" class.
Dim FdCls As New MyDataClass.Books.spS(MyConnString)

' A filter to find 12 dollars books.
FdCls.Filters.book_price.Value = 12
' We could do...
' FdCls.Filters.book_price.IncludedFrom = 12
' to find all 12 or more dollars books...
' We could use many other filters here...

' Executing the stored procedure.
FdCls.Execute()

' Closing connection.
FdCls.Disconnect()

' Binding the grid.
DataGridView1.DataSource = FdCls.Results.Table
  
  • Generate your application.
  • Click on the button.
  • If all is ok, you should see the only 12 dollars book.
3 lignes to execute a stored procedure!
 
5) "Simple INSERT"
  • Double-Click on the button "button1" to handle the "click" event.
  • Copy and paste this code (replace the connection string by yours):
 
VB.NET C#
 
 
' Your connection string.
Dim MyConnString As String = "Initial Catalog=MyBookStore; Data Source={yourServerName}; Integrated Security=False; User ID={yourLogin}; Password={yourPassword}"
 
' Instantiate a new insert "Books" class.
Dim FdCls As New MyDataClass.Books.spI(MyConnString)

' Prepare a new row.
FdCls.Values.book_price.Value = 14
FdCls.Values.book_title.Value = "My Book"
 
' Executing the stored procedure.
FdCls.Execute()

' Closing connection.
FdCls.Disconnect()
  
  • Generate your application.
  • Click on the button.
  • If all is ok, you have just inserted a 14 dollars new book named "My Book".
 
6) "Simple UPDATE"
  • Enter your Visual Studio project.
  • Double-Click on the button "button1" to handle the "click" event.
  • Copy and paste this code (replace the connection string by yours):
 
VB.NET C#
 
 
' Your connection string.
string MyConnString = "Initial Catalog=MyBookStore; Data Source={yourServerName}; Integrated Security=False; User ID={yourLogin}; Password={yourPassword}";
 
' Instantiate a new update "Books" class.
Dim FdCls As New MyDataClass.Books.spU(MyConnString)

' Looking for a book named "My Book"
FdCls.Filters.book_title.Value = "My Book"

' Set up new price, 15 dollars
FdCls.Values.book_price.Value = 15

 
' Executing the stored procedure.
FdCls.Execute()

' Closing connection.
FdCls.Disconnect()
  
  • Generate your application.
  • Click on the button.
  • If all is ok, you have just updated the price of the book named "My Book" to 15 dollars.
 
7) "Simple DELETE"
  • Enter your Visual Studio project.
  • Double-Click on the button "button1" to handle the "click" event.
  • Copy and paste this code (replace the connection string by yours):
 
VB.NET C#
 
 
' Your connection string.
Dim MyConnString As String = "Initial Catalog=MyBookStore; Data Source={yourServerName}; Integrated Security=False; User ID={yourLogin}; Password={yourPassword}"
 
' Instantiate a new delete "Books" class.
Dim FdCls As New MyDataClass.Books.spD(MyConnString)

' Looking for a book named "My Book"
FdCls.Filters.book_title.Value = "My Book"
 
' Executing the stored procedure.
FdCls.Execute()

' Closing connection.
FdCls.Disconnect()
  
  • Generate your application.
  • Click on the button.
  • If all is ok, you have just deleted the book named "My Book".
 

CASE STUDY 2
 
In this second part, we will explore the most elaborate tasks of Denki-SQL, those based with overlaps.
1) Open a existing "Denki-SQL" scenario
  • Start Denki-SQL.
  • Choose "Open" in the "File" menu.
  • Select your XML scenario file(saved in STUDY CASE 1) then click on "Open".
 
Now we will place a filter on an ascending table linked to the table "Books"...
2) Add a ascent and a filter
  • Populate node named "Books".
  • Populate the child node named "Programmability".
  • Right click on the node named "Ascents" and choose "Add an ascent".
  • Lease the constraint named "FK_Books_Authors" selected (only one constraint in this sample) then click on "Create".
  • Populate the child node named "Authors".
  • Populate the child node named "Programmability".
  • Right click on the node named "Filtres" and choose "Add a filter".
  • Choose the column named "author_name" then click on "Add".
 
3) Generate with "Denki-SQL", and set up codes in your project
All is ready to generate code!
  • Choose "Generate code" in the "File" menu.
  • Choose a directory then click on "Generate" (a sub-folder will be create).
Two sub-folders appear: "SQL" and "VB.NET" (or "C#")
  • The "SQL" folder contains T-SQL script, execute-it on your sample database to create all stored procedures.
  • The "VB.NET" (or "C#") folder containts a sub-folder "MyDataClass", this folder contains .NET files).
Now, we are going to create a winform project to test this...
  • Start Visual Studio 2005 and create a new "Windows Application" project.
  • Do a copy/past of the "MyDataClass" folder.
  • Maximize the form (ref. "Form1.vb" or "Form1.cs") and drop a "DataGridView" component and also a "button".
 
4) "Imbricated SELECT"
  • Double-Click on the button "button1" to handle the "click" event.
  • Copy and paste this code (replace the connection string by yours):
 
VB.NET C#
 
 
' Your connection string.
Dim MyConnString As String = "Initial Catalog=MyBookStore; Data Source={yourServerName}; Integrated Security=False; User ID={yourLogin}; Password={yourPassword}"
 
' Instantiate a new select "Books" class.
Dim FdCls As New MyDataClass.Books.spS(MyConnString)

' Search all books written by the "Erwan Riso"
FdCls.Ascents.Authors.Filters.author_name.Value = "Erwan Riso"
' We could use many other filters here...

' Executing the stored procedure.
FdCls.Execute()

' Closing connection.
FdCls.Disconnect()

' Binding the grid.
DataGridView1.DataSource = FdCls.Results.Table
  
  • Generate your application.
  • Click on the button.
  • If all is ok, you should see all Erwan Riso's books.
 
5) "Imbricated EXTAND SELECT"
  • Enter your Visual Studio project.
  • Double-Click on the button "button1" to handle the "click" event.
  • Copy and paste this code (replace the connection string by yours):
 
VB.NET C#
 
 
' Your connection string.
Dim MyConnString As String = "Initial Catalog=MyBookStore; Data Source={yourServerName}; Integrated Security=False; User ID={yourLogin}; Password={yourPassword}"
 
' Instantiate a new select "Books" class.
Dim FdCls As New MyDataClass.Books.spSE(MyConnString)

' Search all books written by the "Erwan Riso"
FdCls.Ascents.Authors.Filters.author_name.Value = "Erwan Riso"
' We could use many other filters here...

' Executing the stored procedure.
FdCls.Execute()

' Closing connection.
FdCls.Disconnect()

' Binding the grid
DataGridView1.DataSource = FdCls.Results.Table
  
  • Generate your application.
  • Click on the button.
  • If all is ok, you should see all Erwan Riso's books plus all Erwan Riso's information (two tables merged).
The difference between the SELECT (spS) method and the EXTAND SELECT (spSE) method is the result datatable. EXTAND SELECT method returns targeted table columns but also parent tables columns.
 
6) "Imbricated UPDATE"
  • Enter your Visual Studio project.
  • Double-Click on the button "button1" to handle the "click" event.
  • Copy and paste this code (replace the connection string by yours):
 
VB.NET C#
 
 
' Your connection string.
Dim MyConnString As String = "Initial Catalog=MyBookStore; Data Source={yourServerName}; Integrated Security=False; User ID={yourLogin}; Password={yourPassword}"
 
' Instantiate a new update "Books" class.
Dim FdCls As New MyDataClass.Books.spU(MyConnString)

' Search all books written by the "Erwan Riso"
FdCls.Ascents.Authors.Filters.author_name.Value = "Erwan Riso"


' Set up all books prices to 16 dollars
FdCls.Values.book_price.Value = 16

 
' Executing the stored procedure.
FdCls.Execute()

' Closing connection.
FdCls.Disconnect()
  
  • Generate your application.
  • Click on the button.
  • If all is ok, you have just upadated all "Erwan Riso" books to 16 dollars.
 
7) "Imbricated DELETE"
  • Enter your Visual Studio project.
  • Double-Click on the button "button1" to handle the "click" event.
  • Copy and paste this code (replace the connection string by yours):
 
VB.NET C#
 
 
' Your connection string.
Dim MyConnString As String = "Initial Catalog=MyBookStore; Data Source={yourServerName}; Integrated Security=False; User ID={yourLogin}; Password={yourPassword}"
 
' Instantiate a new delete "Books" class.
Dim FdCls As New MyDataClass.Books.spD(MyConnString)

' Search all books written by the "Erwan Riso"
FdCls.Ascents.Authors.Filters.author_name.Value = "Erwan Riso"


' Executing the stored procedure.
FdCls.Execute()

' Closing connection.
FdCls.Disconnect()
  
  • Generate your application.
  • Click on the button.
  • Si tout se passe bien, vous venez de supprimer tous les livres de l'auteur "Erwan Riso"
  • If all is ok, you have just deleted all "Erwan Riso"'s books.

SPONSORS
{smartassembly}
  Codes SourceS  Programmers Heaven  AspAlliance.com  Check this page HTML  Check this page CSS 
 
Denki-SQL ©2007 Grégory Dufour (DrTissot) - DrTissot's Blog