SubSonic Tutorial ( Learn basics )
Following code uses version 2.2 and Northwind database as an example to illustrate the basics. You can use Linq queries also which is introduced from 2.1 pakala release. The same tutorial is in the following two files in case it is hard to read the following tutorial.
Insert the Record
//Fill the object to save. Please make sure that you fill all must (not null) object
Supplier sup = new Supplier();
sup.Address = "Test Address";
sup.City = "Ahmedabad";
sup.CompanyName = "IntelliPro";
sup.ContactName = "Test Contact";
sup.ContactTitle = "Manager";
sup.Country = "India";
sup.Fax = "01234567";
sup.HomePage = "http://www.xyz.com";
sup.Phone = "01293202";
sup.PostalCode = "380051";
sup.Region = "Test Region";
// call save
sup.Save();
Update the Record
//Fetch the object which is required to save. 30 is id of record
Supplier sup = new Supplier(30);
// Fill the property which needs to be saved.
sup.PostalCode = "380052";
sup.Region = "TestRegion2";
// call save
sup.Save();
Delete the Record
//If there are IsDelete flag then this function make true in this column. Else it delete the record from the database.
Supplier.Delete(30);
//In case you have IsDelete flag and you want to delete the function, use following code.
Supplier.Destroy(30);
Select all Record
// You can select all the record 2 ways.
// First using supplier object
SupplierCollection colSup = new SupplierCollection();
colSup.LoadAndCloseReader(Supplier.FetchAll());
// now colSup has all the record
// Second is just us collection’s Load method
SupplierCollection colSup = new SupplierCollection();
colSup.Load();
Select record by primary key
// 30 is primary key and in new object just pass it to the constructor
Supplier sup = new Supplier(30);
// if object successfully fetched from the database then following property will be true. So you can check to verify the record sometime whether record is loaded or not.
sup.IsLoaded
Select record by parameter other than primary key (but only one parameter)
SupplierCollection colSup = new SupplierCollection();
// It defaults to equal operation in FetchByParameter function
colSup.LoadAndCloseReader(Supplier.FetchByParameter (Supplier.Columns.Country,"India") );
// IF you want to use another comparision operator, you can use
colSup.LoadAndCloseReader(Supplier.FetchByParameter (Supplier.Columns.Country, SubSonic.Comparison.NotEquals,"India") );
// You can also apply order by at the end. To generate order by, you must use its static function Asc or Desc
// e.g. OrderBy.Asc or OrderBy.Desc and supply column name into the same.
colSup.LoadAndCloseReader(Supplier.FetchByParameter (Supplier.Columns.Country, SubSonic.Comparison.NotEquals,"India", OrderBy.Asc(Supplier.Columns.City) ) );
Following are the list of comparisions which we can use into comparing the parameter.
public enum Comparison
{
Equals = 0,
NotEquals = 1,
Like = 2,
NotLike = 3,
GreaterThan = 4,
GreaterOrEquals = 5,
LessThan = 6,
LessOrEquals = 7,
Blank = 8,
Is = 9,
IsNot = 10,
In = 11,
NotIn = 12,
OpenParentheses = 13,
CloseParentheses = 14,
BetweenAnd = 15,
}
Select record by parameter other than primary key (but more than one parameter)
// There are two methods for doing this. You can use any.
// First one is following
SupplierCollection colSup = new SupplierCollection();
colSup.Where(Supplier.Columns.City,"Ahmedabad");
colSup.Where(Supplier.Columns.CompanyName, Comparison.Like ,"Intelli");
// you can also use order by before calling Load function to order the result.
colSup.OrderByAsc(Supplier.Columns.PostalCode);
// Following statement load the records with above condition. Here one thing to note that
// It only uses where and that also with AND default. you cannot do OR here. For that
// you need to use second option
colSup.Load();
// Here is second method for doing the similar kind of thing
// Here you need to pass tablename or schema in the query.
// If Tables structure is available then you can pass Tables.Supplier else you can pass
// TableName.schema object
Query qry = new Query(Supplier.Schema);
// Query has many function and you can use multiple function for your query
qry.AddWhere(Supplier.Columns.City,"Ahmedabad");
qry.OR(Supplier.Columns.CompanyName, Comparison.Like ,"Intelli");
qry.OrderBy = OrderBy.Asc(Supplier.Columns.City);
SupplierCollection colSup = new SupplierCollection();
// It defaults to equal operation in FetchByParameter function
colSup.LoadAndCloseReader(qry.ExecuteReader());
Other Methods of the Query Objects
// Set order by. Its property. You can use order_by function as well.
qry.OrderBy
// Make In and Non In query
qry.IN
qry.NOT_IN
// And and OR condition
qry.AND
qry.OR
// Where condition
qry.AddWhere
// Find record between values
qry.AddBetweenValues
// Find record between dates
qry.AddBetweenAnd
You have other methods of query to execute other than execute reader.
You can use, Execute for update query, ExecuteDataSet for getting dataset and ExecuteScaler for scalar values.
Select from more than one table
There is no method available in 2.0.3 for the join. So we will use only View right now. But there are other method in latest version of subsonic for directly making Joins.
Suppose you wanted Invoices from the 5 tables joined, you can create the view Invoice and then you can use that object same way like tables and also you can query view to filter the data like tables.
Calling Stored Procedures
When you generate classes, the class is generated automatically for each stored procedure and views. For stored procedure SPs class is generated and all stored procedures are as static function so you can directly call without creating object as I have done below. You can find stored procedure in Northwind database.
StoredProcedure spSales = SPs.SalesByCategory("Test","2009");
DataSet ds = spSales.GetDataSet();
// Generally we use object’s collection to take the result in. So we can create one view like
// SalesByCategoryResult and take this result into it by calling funcitons like that
SalesByCategoryResultCollection colSup = new SalesByCategoryResultCollection();
colSup.LoadAndCloseReader(spSales.GetReader());
Customized Query (Which do not fit in any of above point)
There are two ways. Either you can make stored procedure and generate the SP function and use it or you can make the query as below.
// Make sql statement. But make sure that you do not hardcode any values. Below query can be made with subsonic
// but it is used just only for example.
string query = "SELECT * FROM " + Supplier.Schema.TableName + " WHERE " + Supplier.Columns.Address + " LIKE %abc%";
// Now create querycommand object with above query
QueryCommand qc = new QueryCommand(query);
// DataService class is used to run query command object. QueryCommand object is generally used for
// making parameterized queries which might not be fitted using query object. And you can take those
// Values in Collection
SupplierCollection colSup = new SupplierCollection();
colSup.LoadAndCloseReader(DataService.GetReader(qc));
Aggregate Functions
If you want to use aggregate function on some table, you can use it following way.
Query qry = new Query(Supplier.Schema);
// following returns Maximum supplierid in the table
int SupplierId = qry.GetMax(Supplier.Columns.SupplierID);
//There are other functions available for aggregate in query
qry.GetAverage
qry.GetCount
qry.GetMin
qry.GetSum
Simple Yet Powerful SQL Server Tutorial
W3Schools is known for their simple tutorial for basic things. They have SQL tutorial which is very simple yet cover all the basic functionalities.
http://www.w3schools.com/SQl/default.asp
For some detail tutorial, refer
How to query different type database object like table, view, trigger using sysobject
following is the query for selecting all object from all database
|
SELECT name, id, xtype, uid, info, status, base_schema_ver, replinfo, parent_obj, crdate, ftcatid, schema_ver, stats_schema_ver, type, userstat, sysstat, indexdel, refdate,version, deltrig, instrig, updtrig, seltrig, category, cache
FROM sys.sysobjects
|
There are various parameter which you can use as where to specify criteria.
If you want to search certain object type then
XTYPE =
| ‘U’ | For table |
| ‘PK’ | For Primary Keys |
| ‘F’ | For Foreight Keys |
| ‘V’ | For views |
| ‘P’ | For stored procedures |
| ‘TR’ | For triggers |
| ‘S’ | For system objects |
| ‘FN’ | Function |
You can also add like query on name to give specific resultset.
Debugging Web Page (Performance, Errors, DOM etc.)
If you are web developer, you should following tools for the debugging as well as performance improvement and other functionality. First of all install FireFox browser. I am not going into details of each tool as you are very smart to understand it 
Here is quick list:-
FireBug
YSlow
Fiddler
If you know any other useful stuff for web debugging, you are most welcome to add that to the list.
ASP.NET Error: Unable to evaluate expression because the code is optimized or a native frame is on top of the call stack.
I was working with the page and one silly mistake has taken my mind 
. Could not figure out at first sight but after debug, it would be found out.
I was doing Following:
try
{
SaveShoppingInformation();
RedirectToHome();
}
catch (Exception ex)
{
throw new Exception("User Information: " + SessionHelper.UserInformation + ex.Message,ex);
}
Now the issue is, When you cover the exception on Response.Redirect, it gives the same error. Following is the solution.
try
{
SaveShoppingInformation();
}
catch (Exception ex)
{
throw new Exception("User Information: " + SessionHelper.UserInformation + ex.Message,ex);
}
RedirectToHome();
It resolves my issue
Finished Reading 100 Quotation/Principle of all time : Lovely Leadership Quotes
This book is really nice and simple. It contains great quotations of the qualities that leader has. It is categorized well. I think everybody should go through the list once. Each one is jewel in itself.
Some of my favorites are as below:
Many people have ideas on how others should change; few people have ideas on how they should change. = Leo Tolstoy
The first method for estimating the intelligence of a ruler is to look at the men he has around him. = Niccolo Machiavelli
There’s nothing more demoralizing than a leader who can’t clearly articulate why we’re doing what we’re doing. = James Kouzes and Barry Posner
I cannot trust a man to control others who cannot control himself. = Robert E. Lee
A true leader has to have a genuine open-door policy so that his people are not afraid to approach him for any reason. = Harold Geneen
Delegating work works, provided the one delegating works too. = Robert Half
You do not lead by hitting people over the head—that’s assault, not leadership. = Dwight D. Eisenhower
Leadership is solving problems. The day soldiers stop bringing you their problems is the day you have stopped leading them. They have either lost confidence that you can help or concluded you do not care. Either case is a failure of leadership. = Karl Popper
Lead and inspire people. Don’t try to manage and manipulate people. Inventories can be managed but people must be led. = Ross Perot
In order to make a fire burn, you fan the live coals. In order to keep your organization fired up, it’s imperative that you find and motivate the leaders or potential leaders in your organization regardless of how far down the line they might be. = Dexter Yager
Have patience. All things are difficult before they become easy. = Saadi Shirazi
And Many More… Beautiful book…worth reading. 10/10.
Send SMS through Bluetooth or Serial Port
Just came across one requirement of sending SMS through Bluetooth or serial port. Actually when you connect any bluetooth USB to the pc, that program should have installed the model related to Bluetooth. I got that as follow:
Now, Download the microsoft SMS Sender application from following URL:
Run and Install it. When you run it, it will show the following screen:
Whatever model devices are there, it will be available for the selection. Once it is selected, write destination number and body and send the SMS. If you device ( Phone) is connected to the bluetooth, SMS will be delivered from your phone. Currently I believe only Nokia and Sony is supported by this application. I haven’t tested on other Phone.
The main thing is that, this application also support COMMAND LINE INTERFACE. So that you can use this application in your code to send the SMS through some port or bluetooth. wow!!!. Following are the command line option to send the SMS.
Free DNS Report
The following site is providing free useful report for the DNS.
You can provide the domain name WITHOUT www to get the exact DNS report as well as MX records. Test using Yahoo.com and you will get long list of status of NS and MX of DNS
