Manish Pansiniya's Blog

.NET, C#, Javascript, ASP.NET and lots more…:)

Posts Tagged ‘Tutorial

SubSonic Tutorial ( Learn basics )

with 10 comments

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

Advertisements

Written by Manish

November 7, 2009 at 10:46 pm

Posted in .NET, Time Saver

Tagged with , , ,

Simple Yet Powerful SQL Server Tutorial

leave a comment »

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

http://www.functionx.com/sqlserver/

Written by Manish

October 29, 2009 at 6:21 pm

Posted in .NET

Tagged with ,

Learning jQuery is Fun : jQuery Quick Tutorial – Part 2-Attributes

with one comment

We have seen Selectors in first part of the tutorial which is referenced at Learning jQuery is Fun – jQuery Quick Tutorial – Part 1 – Selectors. Now we are moving on with second part and that is Attributes.

Attributes section consist of functions which are used to do actions on attributes of the HTML Tag. for example, if you wanted to get the href of the link tag which has ‘Manish’ as id. You can do it like $(“a[id=’Manish’]”).attr("href") and you get href of the anchor tag. Let’s look at Attribute in bit/byte detail.

Attributes Related Functions

There are around 5 function ( in next version if it increases, don’t write comment about that as i already has low disk-space in wordpress account :)). All function somehow do interact with element’s attributes.

  • attr(name) returns the attribute passed to the function for first element selected by selectors. That means var title = $("em").attr("title"); returns title of first matched em tag into the title variable. all parameters are in string value in jQuery so please please don’t forget quotes unless specified. I have said this in first part also. Isn’t it ? Ahhh… Memento effect :).
  • attr(key,value) sets the value to the attribute passed by key. So $("button:gt(1)").attr("disabled","disabled"); will disable buttons which are greater than 1.
  • Sometimes we don’t have direct value to set in some attributes. And we need to use functions to set the value. Wow!!! you are genius. Let me tell jQuery guys. And yes, they have implemented within the second. They added function attr(key, fn) for the same as you thought of. Thanks for your contribution :P. This is used to set function value to set specific key’s attribute. Following example will clear it out. $("div").attr("id", function (arr) { return "div-id" + arr; }). Here function always get the element position in the jQuery object. So if page has 3 div, above line will result their id into div-id0,div-id1,div-id2.
  • One more complex but useful function is attr(properties).  It will set the propertie(s) passed into the function to selector’s elements. e.g. $("img").attr({ src: "/images/hat.gif", title: "jQuery", alt: "jQuery Logo" }); This will set src,title and alt tag for each img tag in the page. Power of one line.
  • Last is simple one and that doesn’t need any description removeAttr(name)

CSS Class Releated Functions

There are simple yet helpful routines for working with class attribute of the page elements. Following are 5 functions and their description. They are really easy to understand 🙂

addClass (class) add provided class to selected elements. $("p:last").addClass("selected"); last paragraph will be assigned selected style.
hasClass( class) Whether selected element has the class or not. return bool value. $("p:first").hasClass("selected").toString() Return ‘true’ or ‘false’ according to first para contain selected class or not.
removeClass(class) remove class from the selected elements Don’t wait everytime for example. Use your mind too sometimes
toggleClass(class) & toggleClass(class,switch) toggle the class into selected elements. That means if present then remove it and if it’s not there then add it to the element http://docs.jquery.com/Attributes/toggleClass
see this for example. I am not explaining it as it’s bit late and i need to get dinner fast man!!!.

 

HTML/Text/Value Releated Functions

Now, last functions are related to working with HTML or Text or value of particular element. The working of all are almost similar.

html()
text()
val()
Get the html (innerHTML), text, value respectively. The point to remember with text is that it will return combined text of all selected elements. While html and val only returns data for first selected elements. var htmlStr = $(this).html();
var str = $("p:
first").text();
var singleValues = $("#single").
val();
html(val)
text(val)
val(val)
Set passed value to the innerthml, text or val as per called function. Here once again with text the difference is it escapes the ‘<’ and ‘>’.  So if you pass ‘<b>manish</b>’ as text, it wont show bold manish. Instead of it shows the same passed text. $("div").html("<span class=’red’>Hello <b>Again</b></span>");
$("p").
text("<b>Some</b> new text.");
$("input").val(‘Icangohomenow’);

 

Huh!!!. Attribute finished. We have learned here ( me too has learned :P. seriously ) how to work with attributes with selectors. It was great experience to work with such a library.

$(“Person[ name= ‘Manish’]).val(‘Go to home’).attr(‘timepass’,’Counter-strike game’).

Bye guys!!!

Written by Manish

March 21, 2009 at 3:59 pm