Ahmed Moosa

Transact-SQL Course Presentation (Power Point File )

ahmed moosa — Wed, 19 Oct 2011 21:32:00 GMT

Hi ,

I am working as .Net Trainer , and Recently ,I was teaching a course in Transact-SQL , and I prepared a Power Point Presentation for this Course , I'd like to share this Presentation with you.

this course talking about :

What is SQL ?
Data Types
Constraints
Database Relationships
Normalization
SQL Queries
Transact- SQL Commands | DDL , DCL ,DML
Retrieving Data
Customizing Data
Grouping Data
SQL Operators
Joining Data
Inserting , Updating and Deleting Data
Working With Tables
Working with Views
Working With Constraints
Generating Scripts
Transaction
Working with Stored Procedures
Working with Functions
Working With Triggers

You Can Download this Presentation From HERE

Hope you find something USEFUL :)

Ahmed

Bind Gridview using Jquery

ahmed moosa — Sat, 30 Oct 2010 17:10:00 GMT

What about working with database using client-side? How can I bind Grid View using client-side? How can I retrieve specific row or column using client-side? OK.

full Source Code :Click Here

In fact there is no Data Source property existing in Jquery, but keep in mind that grid view render as Html Table control, and this is the trick! we will append values returned from database as rows but we faced a problem , since the grid view doesn't has a DataSource or DataSourceID property we can't get it in Html , in other words if grid view doesn't have records [data] , it will not be rendered , therefore we can't find it in html source when page loaded. So we need to show empty grid view to get it using Jquery selector .

another problem we have is how can we retrieve the data? What is the return type?

Actually jquery can't understand DataTable Object, DataSet or even DataReader .but already understand what array is ?,OK we get now that we will return an array but which data type "string , Integer , ......"?. well We did it! Let us start coding.

First step : Creating Data base which will be our data source ,we do it using building the following data table :

Datastore.cs

using System;
using System.Data;

public class DataStore
{
public DataStore()
{
//
   // TODO: Add constructor logic here
//
}
public static DataTable GetDataTable()
{
   DataTable dt = new DataTable("Names");
   DataColumn dc1 = new DataColumn("Name");
   DataColumn dc2 = new DataColumn("Age");
dt.Columns.AddRange(new DataColumn[] { dc1, dc2 });
   DataRow dr1 = dt.NewRow();
   dr1[0] = "Ahmed";
   dr1[1] = "27";
   DataRow dr2 = dt.NewRow();
dr2[0] = "Peter";
dr2[1] = "30";
DataRow dr3 = dt.NewRow();
dr3[0] = "John";
dr3[1] = "20";
DataRow dr4 = dt.NewRow();
   dr4[0] = "Ali";
   dr4[1] = "30";
   dt.Rows.Add(dr1);
   dt.Rows.Add(dr2);
dt.Rows.Add(dr3);
dt.Rows.Add(dr4);

   return dt;
}
}

Next step:

Creating Class file "Names" which will contain two properties (Data base columns) for "FirstName" and "Age". Like this :

Names.cs

public class Names
{
public Names()
{
   //
   // TODO: Add constructor logic here
//
}
private string _firstName;
private string _age;
public string FirstName
{
   set { _firstName = value; }
get { return _firstName; }
}
public string Age
{
   set { _age = value; }
get { return _age; }
}
}

Next Step:

Creating default.aspx page which will display and call the data.
Create GridView Control in your page :
<div>
<center>
    <br />
    <asp:GridView ID="NamesGridView" runat="server" ShowHeaderWhenEmpty="True" >
    </asp:GridView>
    <br />
</center>
</div>

Notice that we set ShowHeaderWhenEmpty property to true to display columns names when empty.

In page load event we will write some code which will display the gridview even it empty :

protected void Page_Load(object sender, EventArgs e)
{
   DataTable dt = new DataTable();
   dt.Columns.AddRange(new DataColumn[] { new DataColumn("Name"), new DataColumn("Age") });
  NamesGridView.DataSource = dt;
NamesGridView.DataBind();
}

Notice that we created two columns Names as "Name" and "Age" .Now you can run the page the GridView should be displayed (try it!).

In code behind we will create a method which will connect to datastore and retrieve the data values. As the following :

[WebMethod]
public static Names[] GetNames()
{
  List<Names> list = new List<Names>();
DataTable dt = DataStore.GetDataTable();
foreach (DataRow row in dt.Rows)
   {
      Names _names = new Names();
      _names.FirstName = row["Name"].ToString();
      _names.Age = row["age"].ToString();

      list.Add(_names);
   }
return list.ToArray();
}

Here we fill data values in the two properties (FirstName,Age) then we add it to List which accept Names Class Object as you saw above. And at the end we need to convert it to an array .

Notice that the method decorated with [Web Method] attribute which will allow us to get this method using client side code , and the method need to be declared as public and static also to get reached outside the Container class .

Next Step :

- Calling Server side method that will get the data values and bind the gridview using Jquery like this :

We can retrieve one column like "FirstName" or "Age" only, in the above code we create a loop to go through result data and using index of array element to get current row ,it is working like a datareader or dataset, then you get specific value "data.d[n].Age".

now ,we can call this functon on body load Like this :

<body onload="BindGridView();">
<form id="form1" runat="server">
   <div>
     <center>
       <br />
         <asp:GridView ID="NamesGridView" runat="server" ShowHeaderWhenEmpty="True">
         </asp:GridView>
       <br />
     </center>
</div>
</form>
</body>

When we run this page it should Display GridView like this :

Happy programming

Ahmed Moosa

Embedded Code and Inline Server Tags

ahmed moosa — Thu, 07 Oct 2010 06:31:00 GMT

Introductions

Hi, this my first article here and I hope it will be more useful , so we will talk about something we need to use every day in coding ,we will talk about "Embedded Code " using "In line Server Tags " . Briefly it used to write server side code somewhere where it not allowed directly . more developers use it in data binding controls .but you can use it in many Places Like (Java Script , CSS , ASPX ) , and you can use it with Routing ,ConnectionStrings , XMLQuery , AppSettings , Recourses Files or just writing server side code mixed with html. my be you had seen it in the top of .aspx page .OK ! Let us begin .

Download sample Code from Here

What In line Server Code is ?

inline server code enables you to embedded server side code mixed with client script like javascript or mixed with aspx or with css as we mentioned before.

What Is In line Server Tags ?

Look at this :

<% %>

these tags what we talk about. It comes from ASP classic style and it used in PHP and JSP too for the same reason "Writing Server Side Code ".

Note: you must know that we can't use it in code behind (Page.aspx.cs ) .

inline Code and Page Life cycle :

When the page call its Render Method after the PreRenderComplete Event completed the compiler will Execute this Code and embedded it into Render method which accept HtmlTextWriter as a single parameter like the following :

protected override void Render(HtmlTextWriter writer)
{
base.Render(writer);
}

Let us see the forms you may have seen:

Page Directive

<%@ Page Language="C#" %>

Rendering Code

<% Response.Write("Hello World!"); %>

<%= SayHello("Ahmed") %>

<%: DateTime.Now.ToString() %>

Expression Syntax

<%$ ConnectionStrings:ConnStrFromWebConfig %>

<%$ AppSettings:ValueFromWebConfig %>

<%$ Resources:Resource, Arabic %>

<%$ RouteValue:year %>

<%$ YourExpressionPrefix : Any %>

Data Binding Syntax

<%# Eval("Name") %>

<%# Bind("Name") %>

<%# XPath ("Name") %>

Comment Server

<%-- <asp:Label runat="server" Text="Label"></asp:Label>-- %>

Let us do it in details :

Directive

We use directive to specify settings used by page and user control Compiler . we use it to say hi complier this file is ...... .each directive can have number of attributes as you need.

each directive start with @ . ASP.Net introduced number of Directive as following :

@Page : used on .aspx files only and you can use only one directive inside a file .

<%@ Page Language="C#" %>

<%@ Page Language="C#" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

Note that we are using Codefile to pass the file name of code behind and inherits to pass class Name.

@Control : used only in .ascx files (user control)

<%@ Control Language="C#" ClassName="WebUserControl" %>

@Import : used for name space declaration and only when we write code inside script tags,I mean that if we use code behind we don't need it because we will use "Using Directive" (C#) or " Imports " (VB.net)

<%@ Import Namespace="System.Data" %>

@Assembly : used to Links an Assembly(.DLL or .CS and .VB ) to that page and user control .

DLL files like this

<%@ Assembly Name="CustomFile" %>

(without .dll extension )

Class files like this :

<%@ Assembly src="Csharp.cs" %>

<%@ Assembly src="Csharp.vb" %>

Note: we are using Name attribute with dll files and src with class files.

@Register : used to register Controls (User Controls and Custom Server Controls) to page and user control.

<%@ Register assembly="AjaxControlToolkit" namespace="AjaxControlToolkit" tagprefix="asp" %>

<%@ Register src="WebUserControl.ascx" tagname="WebUserControl" tagprefix="uc1" %>

Note :we are using Name attribute when using assembly and using src when user control.

@Implements : used when we want to implement an Interface . and this Directive takes only one attribute "Interface" .

<%@ Implements Interface="IWebParts" %>

@Reference : used to indicate that the source file of the page or user control should dynamically compiled and linked to the current page .

<%@ Reference Control ="~/WebUserControl.ascx" %>

<%@ Reference Page="~/Default2.aspx" %>

<%@ Reference VirtualPath ="anyfile.any" %>

@OutPutCache : used when we need to put a page or user control in the cache.

<%@ OutputCache Duration ="900" VaryByParam ="none" %>

Note: both attributes ( Duration and VarybyParam ) are required .

@Master : used to decalre page as Master page.

<%@ Master Language="C#" %>

@MasterType : used to reference Master Page when we need to access master page through Master Property .

<%@ MasterType VirtualPath ="~/MasterPage.master" %>

@PreviousPage : used when need to access previous page through PreviousPage Property .

<%@ PreviousPageType VirtualPath ="~/Default4.aspx" %>

@Application : used to declare the global application file .(global.asax)

<%@ Application Language="C#" %>

@WebService : used to declare the web service file .(.asmx)

<%@ WebService Language="C#" CodeBehind="~/App_Code/WebService.cs" Class="WebService" %>

@WebHandler: used to declare the generic handler file (.ashx)

<%@ WebHandler Language="C#" Class="Handler" %>

@ServiceHost : used by WCF (Windows Communication Foundation) .

<%@ ServiceHost Language="C#" Debug="true" Service="Service" CodeBehind="~/App_Code/Service.cs" %>

Rendering Code

Render code tags like Response.write Method. Here an example : using for Looping

<div>

<% for(int i = 0 ; i <5; i++){ %>

Hello World ! <br />

<% } %>

</div>

output the following :

Hello World !

you can use it to execute a method but this time we use " = " to get out of Response.write to an Expression and = is used to resolve an expression and return its value to the block

<%@ Page Language="C#" %>

string message = "I Can Do it ";

    string SayHello(string name)
    {
        return "Hello " + name;
    }

</script>

</head>

<body>

it will output string say "Hello Ahmed " and also gets the value of message variable .

Note : this code blocks render nothing (no html controls ) . try it yourself . browse this code in the browser and Right click to View source .

Html Encoding output in ASP.Net 4

It provides a concise way to automatically HTML encode content and then render it as output

<%:DateTime.Now.ToString () %>

You can learn more here :

we can also use it to embedded Server Side code in Client Side Code (Java Script)

what about calling Server side Method from Client Side Method . Let us do it :

take a look at this code

<%@ Page Language="C#"   %>

<script runat="server">
    string SayHello(string name)
    {
        return "Hello " + name;
    }
</script>

<head runat="server">
    <title>Test Page </title>
    <script type="text/javascript" >
        function SayAny() {
            alert(' <%= SayHello("ahmed") %>');
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
         <br />
        <input type="submit" onclick="SayAny()" value="Say Hello" />
        <br />
    </div>
    </form>
</body>
</html>

it do what you expect : showing alert message .

Sometimes we have a problem when passig control id to client side code like this

var txt = document.getElementById("TextBox1");

and this happened when we work with container like master page or when we want to find control inside Data contorls Like GridView ,DataList ,Repeater ,and so on. this happens because the control has been changed to something like "container_ControlId". and to solve this issue we can use server side blocks like the following :

var txt = document.getElementById("<%=TextBox1.ClientID %>");

you can use alert message to display the value of this TextBox like this :

alert(txt.value);

Expression Syntax :

Get Connection String Section :

<%$ ConnectionStrings:ConnStrFromWebConfig %>

after we set connection strings in web.config file then we need to use data source controls ,Like the following :

Example

<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT * FROM [Categories]">
</asp:SqlDataSource>

Notice: connectionstring is the name of connection string section inside Web.Config Like following :

Web.config :
<connectionStrings>
<add name="ConnectionString"
            connectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\NORTHWND.MDF;
                                        Integrated Security=True;User Instance=True"
            providerName = "System.Data.SqlClient" />
</connectionStrings>

Get AppSettings Section

<%$ AppSettings:ValueFromWebConfig %>

after we set AppSettings section in web.config file you can call it using key name to return its value .

Web.config

<appSettings>
<add key ="KeyName" value ="This Value Is Ahmed"/>
</appSettings>

Default.aspx

<asp:Label Text="<%$ AppSettings :Keyname %>" runat="server" />

Get a value from Global Resource File (.resx )

<%$ Resources:Resource, Arabic %>

Let us take an example switching between two Language (Arabic and English ).

Create two files (Resource.resx , Resource.ar-eg.resx ) and put them in App_GlobalResources folder.
Define a key with name "Greeting" in both resource files.

Note : the "greeting" key will have two different value for Arabic and English .

Create test page.aspx and drag a label control and drop down list control. so ,your aspx code will be like this :

<div>
    <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True">
       <asp:ListItem Value="ar-eg">Arabic</asp:ListItem>
       <asp:ListItem Value="en-us">English</asp:ListItem>
    </asp:DropDownList>
    <br />
    <br />
    <asp:Label ID="Label1" runat="server" Text="<%$ Resources:Resource, Greeting %>"> </asp:Label>
</div>

Note :

- Resources >> Say " I am working with resource files ".

- Resource >> say "I am the resource file name without extension".

- Greeting >> say " I am the Key which my value will be displayed"

now we have two values for "Arabic" and "English" we can switch between them. what will happen if we select English or Arabic ? ok, we need some code to change Language and Current Culture .let us go to next step:

We need to override IntializeCulture Method as following :

protected override void InitializeCulture()
    {
        if (Request["DropDownlist1"] != null)
       {
            string lang = Request["DropDownlist1"];
            Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo(lang );
            Thread.CurrentThread.CurrentUICulture = System.Globalization.CultureInfo.CreateSpecificCulture(lang );
        }
    }

Now you can select your Language from drop down list control and see that the related content will be displayed.

Routing

ASP.Net Routing is something about mapping URLs, Routing is available in ASP.Net web forms and ASP.Net MVC.Since .net framework 3.5. to use routing you need to create static method that take a parameter of RouteCollection data type inside global.asax file, then call these method in Application_Start event .

May you need more in Routing so here you can take a look

http://msdn.microsoft.com/en-us/library/dd329551.aspx

Ok , what we want to say is we can use Expression Syntax to set URLs Like this :

<%$ RouteUrl:action=Edit , CategoryName=Cars%>

<%$ RouteValue:year %>

You need to understand what action and categoryName means? To do this refer to The above Link .

Exrpression Builder

<%$ YourExpressionPrefix : Any %>

yes , you can create your Expression like mine :
<div>
<asp:Label Text="<%$ myCode:DateTime.Now %>" runat="server" />
</div>

to learn more about Expression Builder Click Here

Data Binding Syntax

<%# Eval("Name") %>

<%# Bind("Name")%>

<%# XPath ("Name") %>

We use embedded code in Data binding to set or get or in Other words to read and write to a data source .we have Two methods for working with Data base (Eval and Bind ) and one for working with XML (XPath that stand of xml path ) .so , what is the different between Eval and bind ? Eval method is One way that means it is for reading and Bind method is Two way that means it is for Reading and writing .

Example : using eval method in grid view to display Images .

<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1">

<asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />

<asp:TemplateField>

<asp:Image ID="Image2" runat="server" ImageUrl='<%# Eval("Path") %>' Width="70" />

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

In the above example we use eval method to get path field from data base and pass It to ImageUrl Property.

try to use bind method in editing data.

XPath method:

Use it when working with Xml, we can use it to select element or attribute and get its value and it comes in many forms Like this :

Look at this file.xml. we use it to simplifies the following :

<?xml version="1.0" encoding="utf-8" ?>
<Links>
    <Link>
       <name>Microsoft</name>
       <Site>http://www.microsoft.com</Site>
   </Link>
<Link>
      <name id="10" >Asp.net</name>
     <Site>http://www.asp.net</Site>
   </Link>
   <Link>
        <name id="20" >MSDN</name>
        <Site>http://www.msdn.com</Site>
   </Link>
</Links>

<%# XPath ("Name") %>

get the value of the Name element .

output here will be as following : Microsot ASP.Net MSDN

<%# XPath("name[@id]") %>

Get the value of the name element which take an attribute with name "Id"

output here will be as following : ASP.Net MSDN

<%# XPath ("name[@id=\"10\"]") %>

Get the value of name element which take an attribute with name id that its value equal 10 .

output here will be as following : ASP.Net

<%# XPath("name/@id") %>

Get the value of Every attribute which its name is 'id' that was defined in "name" element .

output here will be as following : 10 20

Yes, it is give xml more funs .

Note :

When using your custom method as Binding Expression Remember to call Databind Method . this question I meet it in Asp.Net forums and that is my answer "call Databind method to that control "

Example : Get Text from the result of custom method .

<div>

<asp:Button ID="SomethingBtn" Text="<%# ServerSideMethod() %>" runat="server" />

</div>

So when we try to view it in browser the button wouldn't display any text . because we need to call Data bind method for that button Like this :

protected void Page_Load(object sender, EventArgs e)

{

Somethingbtn.DataBind();

}

And finally I want to say that you can use all or one of them Like this :

<%# Eval("Id","~/default.aspx?id={0}") %>

<%# "Your Name Is :" + Eval("Name") %>

<%# SayHello( Eval("Name").ToString () ) %>

And More ...........

Last thing is Comment Server

We can use it to put a comment where the comment is not allowed .

Example :

<div>

<%-- <asp:Button ID="Button2" runat="server" Text="Button" />--%>

</div>

That complete this topic , hope you enjoy and Happy Programming

Ahmed Moosa