session not using in windows forms

Windows Form app are stateful application, therefore maintaince of state is not required. If you want to share information across forms then you can have a static class/ variables/ for the same.

What is Abstract Class?

Abstract class is a class that can not be instantiated, it exists extensively for inheritance and it must be inherited. There are scenarios in which it is useful to define classes that is not intended to instantiate; because such classes normally are used as base-classes in inheritance hierarchies, we call such classes abstract classes. 

Abstract classes cannot be used to instantiate objects; because abstract classes are incomplete, it may contain only definition of the properties or methods and derived classes that inherit this implements it's properties or methods. 

Static, Value Types & interface doesn't support abstract modifiers. Static members cannot be abstract. Classes with abstract member must also be abstract. 

Abstract classes are classes that contain one or more abstract methods (methods without implementation). An abstract method is a method that is declared, but doesn't contain implementation (like method declaration in the interface). Abstract classes can't be instantiated, and require subclasses to provide implementations for the abstract methods. This class must be inhertied. This class is mostly used as a base class.

Questions on Abstract class are very frequently asked in interviews:) Apart from interviews Abstract class is also very important to know when you are designing or working on a real time applications that needs proper design. I am not expert in this however trying to explain what I know out of my limited knowledge. This article tries to cover Abstract class, Abstract method, Abstract property and difference between abstract method and virtual method.

Abstract class is a class that can not be instantiated. To use it you need to inherit it. This class can be used as a base class where you can define certain method that must be implemented in derived class (Class that is going to inherit it) along with that you can also define certain methods that is frequently used and can be directly used by Derived class or overriden in the derived class if needed.
In the abstract class, you can define following:

1. Normal property - this property is similar to any other property we define in a normal class
2. Abstract property - this will have only get and set accessor but no implementation.
3. Normal method - this method is similar to any other method that you define in a normal class
4. Abstract method - this will not have any implementation
5. Virtual method - this will have implementation but can also be overridden in the derived class to provide additional logic or completely replace its logic

Abstract Classes: Classes which cannot be instantiated. This means one cannot make a object of this class or in other way cannot create object by saying ClassAbs abs = new ClassAbs(); where ClassAbs is abstract class. 
Abstract classes contains have one or more abstarct methods, ie method body only no implementation. 
Interfaces: These are same as abstract classes only difference is we can only define method definition and no implementation. 
When to use wot depends on various reasons. One being design choice. 
One reason for using abstarct classes is we can code common 
functionality and force our developer to use it. I can have a complete 
class but I can still mark the class as abstract. 
Developing by interface helps in object based communication.

Abstract Class

In order to explain Abstract class, I am going to take a simple example of ParentClass class that has all methods and properties explained above and it looks like below
ParentClass.cs

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

/// <summary>

/// Summary description for ParentClass

/// </summary>

public abstract class ParentClass

{

public ParentClass()

{



}

private int age = 0;

// Normal property

public int Age_Normal

{


get

{return age;

}

set

{age = value;

}
}

// Abstract property

public abstract string Address_Abstract

{


get;

set;
}



// Normal Methods

public string GetName(string firstName, string lastName)

{


return "My Name is : " + GetName_Virtual(firstName, lastName);
}

public int Divide_NotAbstract(int a, int b)

{


return a / b;
}

// Abstract Methods

public abstract int Add_Abstract(int a, int b);

public abstract int Subtract_Abstract(int a, int b);



// Virtual method

public virtual string GetName_Virtual(string firstName, string lastName)

{


return firstName + " " + lastName;
} 
}

Get solutions of .NET problems with video explanations, .pdf and source code in .NET How to's.
As you can see, the first property I have above is Age_Normal, this is a normal property similar to other property that we define in the class that has its implementation as well and it can be simply accessed by the instance of the dervied class.
Next we have an Abstract property Address_Abstract that has only get and set accessor and no implementation.
Next we have normal method that is similar to any other method we define in a normal class.
Next we have Abstract methods that contains abstract keyword in its definition, this doesn't have any implementation. (Defining abstract methods and properties are similar to defining properties and methods in an interface). One thing to note is that an abstract methods or properties can only be defined in Abstract class, you can't define them in a normal class.
Next we have a virtual method that let us use its logic directly or also allow us to completely override its logic.

Derived Class

Below is my derived class (that is inheriting above abstract class), its name is DerivedClass (Some of the method or property names may look strange, I have just kept this for easy understanding, please bear with me).
DerivedClass.cs

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

/// <summary>

/// Summary description for Calculate

/// </summary>

public class DerivedClass : ParentClass

{

private string address = string.Empty;

public DerivedClass()

{

}

// override the abstract property

public override string Address_Abstract

{


get

{return address;

}

set

{address = value;

}
}

// override the abstract method

public override int Add_Abstract(int a, int b)

{


return a + b;
}

public override int Subtract_Abstract(int a, int b)

{


return a - b;
}

// override virtual method

public override string GetName_Virtual(string firstName, string lastName)

{


return "(Overriden) Name: " + firstName + " " + lastName;
}

// hide normal method of the abstract class

public new string Divide_NotAbstract(int a, int b)

{


int d = a/b;

return "The division is: " + d.ToString();
}

// use abstract property to retrieve its value

public string GetAddress()

{


return Address_Abstract;
}
}

In the above class I am doing following:
First, I am overriding the Address_Abstract property, as I had declared it as Abstract property. Note that to implement the abstract property or method, you need to use override keyword.
Next, I am overriding two methods Add_Abstract and Sub_Abstract as these methods are declared as Abstract method.
Next I had declared GetName_Virtual method as virtual method so I have freedom to either override it or use it as it is. If we want to use the original method, we can choose not to override it but if we want to modify its logic then we will have to override it. I preferred to override so I have redefined my logic here.
Next I have made Divide_NoAbstract method of Parent class as hidden by specifying new keyword in the definition of derived class. Even if I have made the original parent class method as hidden if at some place we want to use the orginial abstract class method, we can use that, I will show you how to use that later.

How to use Derived and Abstract class methods

Below is the code that shows how to use above abstract class and derived class methods or properties.

DerivedClass c = new DerivedClass();

Response.Write("<b>Abstract method - Sum: </b>" + c.Add_Abstract(50, 30).ToString() + "<br />");

Response.Write("<b>Abstract method - Subtract: </b>" + c.Subtract_Abstract(50, 30).ToString() + "<br />");

Response.Write("<b>Virtual Method - GetName_Virtual: </b>" + c.GetName_Virtual("SHEO", "NARAYAN") + "<br />");

Response.Write("<b>Normal Public Method - GetName: </b>" + c.GetName("SHEO", "NARAYAN") + "<br />");

Response.Write("<b>Normal Public Method being hidden using new keyword - Divide_NotAbstract: </b>" + c.Divide_NotAbstract(50, 30).ToString() + "<br />");

ParentClass p = new DerivedClass();

Response.Write("<b>Normal Public Method from Abstract Class - Divide_NotAbstract: </b>" + p.Divide_NotAbstract(50, 30).ToString() + "<br />");

c.Address_Abstract = "Sheo Narayan, Hitec City, Hyderabad.";

Response.Write("<b>Normal Public method accessing <br />overriden Abstract Property - GetAddress: </b>" + c.GetAddress() + "<br />");

Above code snippet will give following results

---

Abstract method - Sum: 80
Abstract method - Subtract: 20
Virtual Method - GetName_Virtual: (Overriden) Name: SHEO NARAYAN
Normal Public Method - GetName: My Name is : (Overriden) Name: SHEO NARAYAN
Normal Public Method being hidden using new keyword - Divide_NotAbstract: The division is: 1
Normal Public Method from Abstract Class - Divide_NotAbstract: 1
Normal Public method accessing
overriden Abstract Property - GetAddress: Sheo Narayan, Hitec City, Hyderabad.

---

In the above code snippet, first I instantiated the DerivedClass method and start calling the both Abstract methods Add_Abstract and Subtract_Abstractthat were implemented in the Derived class.
Next I have called the Virtual method of the Abstract class that was overriden in the Derived class. You can see the implementation of GetName_Virtualmethod in the Derived class that is prefixing "(Overriden) Name" with the result.
Next line is calling a normal method GetName that was defined in the Parent abstract class.
Next line is calling the method Divide_NoAbstract that is hiding the main normal method Divide_NoAbstract of Parent class by specifying the new keyword in its definition in the derived class.
Now lets suppose, you already have made the Parent class method (as explained in the above line) hidden but still in a certain scenario, you want to call the Parent class method. To do that, we need to instantiate the Derived Class by specifying it as the ParentClass by writing it as ParentClass p = new DerivedClass();. This is giving me the reference of Parent class and when called Divide_NoAbstract method, this will call the Parent classDivide_NoAbstract method not Derived class method.
The very next line is setting the property of the Derived class that is nothing but the implementation of the ParentClass Address_Abstract property and calling the Derived class GetAddress method that is simply returning this property value.

Conclusion

In this article I tried to show a practical example of how to use Abstract class and what are differnt things that we can define into it. I also tried to show how to work with methods and properties of Abstract class in different scenarios. Hope this article will be useful for the readers. Please subscribefor the subsequent articles alert directly in your email. NOTE: This article was written in 3 breaks, please let me know if somewhere the continuation is broken. Thanks
http://www.dotnetfunda.com/interview/showcatquestion.aspx?category=42

D/f sql functions and stored procedures

we cant use DML operations in functions where as it is possible in sp.

Procedure can return zero or n values whereas function can return one value which is mandatory.

error handling can be done in sp, but not possible in function.

functions can be called from select statements, where clause and case but not possible in sp.

Procedures can have input,output parameters for it whereas functions can have only input parameters

Functions can be called from procedure whereas procedures cannot be called from function.

We can go for transaction management in procedure whereas we can't go in function.

1. Functions are compiled and executed at run time.
Stored procedures are stored in parsed and compiled format in the database.

2. Functions cannot affect the state of the database which means we cannot perform insert,delete,update and create operations on the database.
Stored Procedures can affect the state of the database by using insert,delete,update and create operations.

3 Functions are basically used to compute values. We passes some parameters to functions as input and then it performs some operations on the parameter and return output.
Stored procedures are basically used to process the task.

4.Function can not change server environment and our operating system environment.
Stored procedures can change server environment and our operating system environment.

5.Functions can not be invoked from SQL Statements. Execute. SELECT
operating system can be invoked from SQL Statements. Execute. SELECT

6.Functions can run an executable file from SQL SELECT or an action query.
operating system use Execute or Exec to run 

Common Table Expressions(CTE)

If you think only .NET languages are the only thing that is getting evolved day by day while SQL Syntaxes remains constant, you might have to think it again. SQL Server is gradually coming with evolution day by day with some alternative syntaxes of writing SQL Statements to make more complex SQL statements easier to understand and maintainable in form of CTE or Common Table Expressions. In this post I will try to cover some of the benefits that we get when working with CTE.

When dealing with Sub-queries, it is often required that you need to select a part of the data from a sub query or even join data from a query with some other tables. In that case either you have option to name your sub-queries with an alias or to use it directly. Gradually your requirement is getting more and more complex and your query would look unmaintainable at any time. CTE allows you to define the subquery at once, name it using an alias and later call the same data using the alias just like what you do with a normal table. CTE is standard ansi sql standard.

Say for instance you have a query like this :

SELECT * FROM  (
 SELECT A.Address, E.Name, E.Age From Address A
 Inner join Emp E on E.EID = A.EID) T
WHERE T.Age > 50
ORDER BY T.NAME

The query looks really a mess. Even if I need to write something that wraps around the entire query, it would gradually become unreadable. CTE allows you to generate Tables beforehand and use it later when we actually bind the data into the output.

Rewriting the query using CTE expressions would look like :

With T(Address, Name, Age)  --Column names for Temporary table
AS
(
SELECT A.Address, E.Name, E.Age from Address A
INNER JOIN EMP E ON E.EID = A.EID
)
SELECT * FROM T  --SELECT or USE CTE temporary Table
WHERE T.Age > 50
ORDER BY T.NAME

Yes as you can see, the second query is much more readable using CTE. You can specify as many query expressions as you want and the final query which will output the data to the external environment will eventually gets reference to all of them. For instance :

With T1(Address, Name, Age)  --Column names for Temporary table
AS
(
SELECT A.Address, E.Name, E.Age from Address A
INNER JOIN EMP E ON E.EID = A.EID
),
T2(Name, Desig)
AS
(
SELECT NAME, DESIG FROM Designation)
SELECT T1.*, T2.Desig FROM T1  --SELECT or USE CTE temporary Table
WHERE T1.Age > 50 AND T1.Name = T2.Name
ORDER BY T1.NAME

So the queries are separated using commas. So basically you can pass as many query as you want and these queries will act as a subqueries, getting you the data and name it as a temporary table in the query.
According to the syntax, the CTE starts with a With clause. You can specify the column names in braces, but it is not mandatory.

Lets create one cool CTE for you finally :

WITH ShowMessage(STATEMENT, LENGTH)
AS
(
SELECT STATEMENT = CAST('I Love ' AS VARCHAR(150)), LEN('I Love ')
UNION ALL
SELECT
 CAST(STATEMENT + 'DAILY DOTNET TIPS! ' AS VARCHAR(150))
 , LEN(STATEMENT) FROM ShowMessage
WHERE LENGTH < 100
)
SELECT STATEMENT, LENGTH FROM ShowMessage

So this will produce something like this :

Please Note: Always consider starting your CTE with a semicolon when you have more than one query in a batch to ensure the with statement is not considered as a part of the previous query.

Writing a Stored Procedure

Virtually all ASP.NET applications of interest work with database data at some level, and one of the most common databases used in ASP.NET applications is Microsoft's ownSQL Server database. With relational databases like SQL, commands are issued through the SQL syntax, which includes SELECT, INSERT, UPDATE, and DELETE statements, among others. One way to issue a command to a database from an ASP.NET application is to craft the SQL query in the application itself. Such queries are often called ad-hoc queries. The primary downside of ad-hoc queries is that they are hard to maintain - if you need to change your query you need to edit the string in your application, recompile, and redeploy.
A better approach, in my opinion, is to use stored procedures. Stored procedures are pre-compiled functions that reside on the database server that can be invoked by name. This is similar to compartmentalizing programmatic functionality into methods. Stored procedures are not only more updateable than their ad-hoc counterpart, but also can be utilized by other applications. For example, you might have both an ASP.NET application and a Web services application that is driven on data from the same database. If you hard code your SQL queries in your source code, any changes will now require modifications in two places (as well as two places that now require recompilation and redeployment). However, by using stored procedures there's a single point that needs modification. (The debate between stored procedures and ad-hoc queries has been done in much greater detail in other venues; see Rob Howard's blog entry Don't use stored procedures yet? Must be suffering from NIHS (Not Invented Here Syndrome) for a pro-stored procedures slant, and Frans Bouma's entry Stored Procedures are Bad, M'Kay? for a look at why stored procedures aren't the end-all answer.)
Stored procedures typically return resultsets, such as the results of a SELECT query. However, there are times when you may be getting back just scalar data from a stored procedure. For example, you might have a stored procedure that returns just the account balance for a particular customer, or one that returns the average age of all users in your database. When calling a stored procedure that INSERTs a new record into a table with an IDENTITY field, you may want to get back the ID for the newly inserted row.
There are a couple of ways to get back scalar data from a SQL Server stored procedure. In this article we'll look at these various techniques along with how to fetch back the returned data in your ASP.NET code. Read on to learn more!

Returning Data with a SELECT Statement

Typically data is returned from a stored procedure using a SELECT statement, and typically the data returned is a resultset, consisting of multiple fields and records. For example, a stored procedure might be created to get all products in inventory, which might be accessed through the SQL query:

CREATE PROCEDURE store_GetInventory AS SELECT InventoryID, ProductName, Price, UnitsOnStock FROM store_Inventory

However, there's no reason why you can't return a simple scalar value. For example, if you were interested in the average price of all items in inventory - i.e., just a simple number, like $11.92 - you could return this scalar data using a SELECT statement:

CREATE PROCEDURE store_GetAverageInventoryPrice AS SELECT AVG(Price) AS AveragePrice FROM store_Inventory

Similarly, in stored procedures that insert a new record into a table that has an IDENTITY field, you can get the ID value of the newly inserted record through theSCOPE_IDENTITY() function. So, after INSERTing the new record you can simply return the value like so:

CREATE PROCEDURE store_AddNewInventoryItem ( @ProductName nvarchar(50), @Price money ) AS -- INSERT the new record INSERT INTO store_Inventory(ProductName, Price) VALUES(@ProductName, @Price) -- Now return the InventoryID of the newly inserted record SELECT SCOPE_IDENTITY()

When returning scalar through a SELECT statement you can retrieve the data using the exact same technique used to retrieve a resultset. That is, you can, if you want, use a DataReader, DataTable, or DataSet. The only thing to keep in mind is that you're results will contain only one row with only one field. The following code would call thestore_GetAverageInventoryPrice and grab back the scalar result:

Dim myConnection as New SqlConnection(connection string) myConnection.Open() Dim myCommand as New SqlCommand("store_GetAverageInventoryPrice", myConnection) myCommand.CommandType = CommandType.StoredProcedure Dim reader as SqlDataReader = myCommand.ExecuteReader() 'Read in the first record and grab the first column Dim avgPrice as Decimal If reader.Read() Then avgPrice = Convert.ToDouble(reader("AveragePrice")) End If

This is a bit of overkill, though, thanks to the DataCommand's ExecuteScalar() method. The ExecuteScalar() method can be used in place of the ExecuteReader(), the difference being ExecuteScalar() returns a single Object instance as opposed to a DataReader. Using ExecuteScalar() the code would be simplified to:

Dim myConnection as New SqlConnection(connection string) myConnection.Open() Dim myCommand as New SqlCommand("store_GetAverageInventoryPrice", myConnection) myCommand.CommandType = CommandType.StoredProcedure Dim avgPriceObject as Decimal = Convert.ToDecimal(myCommand.ExecuteScalar())

(The above omits a check to see if the result is NULL. If there were no rows in store_Inventory or no rows with a non-NULL Price, the returned Object would be equal toDBNull.Value. Ideally you would either add such a check to the above code or edit the stored procedure to use ISNULL to convert any NULL result into a number (i.e., SELECT ISNULL(AVG(Price), 0.0) ...).)
While the SELECT method just discussed provides an easy way to return a scalar value from a stored procedure it only works if the scalar value is the sole piece of data you want to return from the stored procedure. There are times, however, where you want to return a full resultset from the stored procedure along with some scalar value. The remaining two approaches we'll be looking at in this article address how to accomplish this feat.
Using Output Parameters One way to retrieve scalar data in addition to a standard resultset from a stored procedure is to use one or more output parameters. An output parameter is a parameter that is passed into the SQL stored procedure, but whose value can be set in the stored procedure. This assigned parameter, then, is readable back from the application that called the stored procedure.
To use an output parameter you need to indicate that the parameter is intended for output via the OUTPUT keyword. The following snippet shows a stored procedure that returns the set of inventory items through a SELECT statement and uses an output parameter to return the average price:

CREATE PROCEDURE store_GetInventoryWithAveragePrice ( @AveragePrice money OUTPUT ) AS SET @AveragePrice = (SELECT AVG(Price) FROM store_Inventory) SELECT InventoryID, ProductName, Price, UnitsOnStock FROM store_Inventory

To access the value of an output parameter from your ASP.NET application you need to create a parameter object whose Direction property is set to Output. After you call the stored procedure the output parameter's value is accessible through the Value property, as the following code illustrates:

Dim myConnection as New SqlConnection(connection string) myConnection.Open() Dim myCommand as New SqlCommand("store_GetInventoryWithAveragePrice", myConnection) myCommand.CommandType = CommandType.StoredProcedure 'Create a SqlParameter object to hold the output parameter value Dim avgPriceParam as New SqlParameter("@AveragePrice", SqlDbType.Money) 'IMPORTANT - must set Direction as Output avgPriceParam.Direction = ParameterDirection.Output 'Finally, add the parameter to the Command's Parameters collection myCommand.Parameters.Add(avgPriceParam) 'Call the sproc... Dim reader as SqlDataReader = myCommand.ExecuteReader() 'Now you can grab the output parameter's value... Dim avgPrice as Decimal = Convert.ToDecimal(avgPriceParam.Value)

(The same issue regarding NULLs applies here as in the previous example...)
You are not limited to a single output parameter; additionally, you can have stored procedures with both input and output parameters.

Using a Return Value

The final technique I want to talk about for returning scalar values from a stored procedure is using return values. Whenever a stored procedure finishes executing, it always returns a return value. This return value is, by default, 0. You can use the RETURN statement yourself, however, to return a scalar integer value. For example, let's revisit thestore_AddNewInventoryItem, but modify it to return the ID of the newly inserted row as a return value.

CREATE PROCEDURE store_AddNewInventoryItem ( @ProductName nvarchar(50), @Price money ) AS -- INSERT the new record INSERT INTO store_Inventory(ProductName, Price) VALUES(@ProductName, @Price) -- Now return the InventoryID of the newly inserted record RETURN SCOPE_IDENTITY()

Note that the SCOPE_IDENTITY() value is being return via a RETURN statement now, whereas in the earlier example we used a SELECT.
To retrieve the return value from a stored procedure use the same technique as with output parameters, the only difference being that you should use a Direction value ofReturnValue, as the following code snippet illustrates:

Dim myConnection as New SqlConnection(connection string) myConnection.Open() Dim myCommand as New SqlCommand("store_GetInventoryWithAveragePrice", myConnection) myCommand.CommandType = CommandType.StoredProcedure 'Create a SqlParameter object to hold the output parameter value Dim retValParam as New SqlParameter("@RETURN_VALUE", SqlDbType.Int) 'IMPORTANT - must set Direction as ReturnValue retValParam.Direction = ParameterDirection.ReturnValue 'Finally, add the parameter to the Command's Parameters collection myCommand.Parameters.Add(retValParam) 'Call the sproc... Dim reader as SqlDataReader = myCommand.ExecuteReader() 'Now you can grab the output parameter's value... Dim retValParam as Integer = Convert.ToInt32(retValParam.Value)

That's all there is to it! As I mentioned earlier, you can only return integer values through the stored procedure's return type.

Conclusion

In this article we examined three ways to pass back scalar data from a stored procedure, along with the necessary code to process the returned value. You can use a SELECTstatement, output parameter, or return value (assuming you want to pass back an integer value). When returning a scalar value via a SELECT statement you can read the resulting value using the ExecuteScalar() method. For output parameters and return values you need to create a parameter object with the proper Direction property value. Then, after you call the stored procedure, you can access the retrieved value through the parameter's Value property.

SQL Basics

Last update: 18/01/2003This page contains the essential commands to manage databases using SQL (Structured Query Language). I use it as a refference for myself. I don't pretend to be an SQL-expert. Others may use this reference freely. If you have any suggestions or comments please e-mail me!
Some commands depend on the type of server used. I experiment with four servers, Microsoft SQL Server, Borland Interbase Server, MySQL Server en Sybase SQL Anywhere. If a command is specific to one server I indicate this with an icon.

1.  indicates that the command works with MS SQL Server.
2.  indicates the command works with Borland InterBase Server.
3. indicates that the command works with MySQL Server.
4. indicates that the command works with Sybase SQL Anywhere.

The commands are written in bold, italicised text (databasenames, fieldnames, values, ...) is up to you to fill in. Note also that I speak of fields and records instaid off rows and coloms. In MS SQL Server manuals I see they use rows and coloms, but I prefer to use fields and records, probably because that was the way in dBase and MS Access, which I used for years.

---

1. List all the databases on the server:

sp_databases 
show databases 

---

2. Select a database to use:

use databasename  

---

3. List tables in the database:

show tables 
sp_help There is another, more complicated, command to retrieve the names of the tables in the database, but where you get a list of ALL tables, including system tables using sp_help, you can specify to view only user defined tables
select * from SYSOBJECTS 
where TYPE = 'U'
order by NAME If you want to see the tables of an InterBase database use the menu "Metadata", "Show..." and select "Tables" in the "View Information on"-dropdown. Click "OK" and you see the tables of the active database. Using ISQL you can use show tables
 To see the tables in Sybase press functionkey F7.

---

4. List fields in a table:

describe tabelname 
sp_help tablename  To see the fields of an InterBase database use the menu "Metadata", "Show..." and select "Tables" in the "View Information on"-dropdown. Type the name of the database in the "Object name"-textfield. Click "OK" and you see the fields of the specified table. Using ISQL you can use show table tablename
 To see the fields in a Sybase table press functionkey F7, select the table and click "Columns".

---

5. Create a database:

create database databasename 
on
(name='databasename_dat',
filename='c:\mssql7\data\databasename_dat.mdf',
size=2mb,
filegrowth=1mb)
log on
(name='databasename_log',
filename='c\mssql7\data\databasename_log.ldf',
size=2mb,
filegrowth=1mb)

---

6. Create a table in the database en store values in it:

create table tablename    
(fieldname1 datatype1, fieldname2 datatype2, ...)
insert into tablename
values(fieldvalue1, fieldvalue2, ...)

---

7. Constraints:

Primary key: constraint constraintname primary key (non)clustered (fieldname1, fieldname2, ...)

Foreign key: constraint constraintname foreign key (fieldname) references tablename(fieldname)

No dulicates: constraint constraintname unique nonclustered (fieldname)

Check: Add values to the fieldnamelist in the 'create table'-command:
(fieldname1 datatype1 check (fieldname1 in ('value1', 'value2', ...)), fieldname2 datatype2, ...)

Default value: Add default value to the fieldlist in the 'create table'-command:
(fieldname1 datatype1, fieldname2 datatype2 default 'value', fieldname3 datatype3, ...)

---

8. Select all records from table:

select * from tablename    
order by fieldnameNote that this command could take some time and put a lot of stress on the processor and memory of the server if you try to view a large database this way. On my server (Pentium MMX 233 Mhz - 64 Mb memory) it took 1:25 minutes to retrieve 25000 records from a database and it gave warnigs of shortage in virtual memory, so please don't try this on a production database (;-). Usualy it is better to select fields and conditions to limit the amount of records and the stress on the server.

---

9. Select set of records from table:

select fieldname1, fieldname2, ... from tablename    
where fieldname = "condition"
order by fieldname
Comparison Operators that can be used in the condition are:

=, !=, <>, <, >, <=, >= and LIKE.

With LIKE you can specify e.g. all the names beginning with S as 'name LIKE "s%"'.
You can also use boolean operators to specify more then one condition (OR, AND, NOT, BETWEEN, IN). With 'BETWEEN' you give a minimum and maximum value to the condition, with 'IN' you can give a list of values.

Example:

select Firstname, Lastname, Department from Employees
where Lastname LIKE "Van%" AND
Badge BETWEEN 121990 and 141990 AND
Department IN ("Sales", "Logistics")
order by Lastname
This statement retrieves all the employees where Lastname begins with "Van", whose badgenumbers are between 121990 and 141990, who work at the Sales- or Logisticsdepartment, and displays their Lastname, Firstname and Dapartment ordered by Lastname.

Note theat the 'ORDER BY' statemant can have more then one fieldname and can also take 'asc' or 'desc' as last argument (for ascending or descnding order).

---

10. Retrieve unique values

select distinct fieldname from table    

---

11. Add records to a table:

insert into tablename (fieldname1, fieldname2, ...)    
values ('value1', 'value2', ...)
If you leave out the fieldnames then the values must match the number of fields in the table. If you only want to add a value to some fields you have to specify them and the values must mach the number of specified fields. The unspecified fields are filled with NULL or the default constraint defined in the table. You could concider to specify defaults like "unknown" or "N/A" to avoid a NULL-value in the field.

---

12. Updating records in a table;

update tablename    
set fieldname1='value1', fieldname2='value2', ...
where fieldname3 = condition
If you ommit the 'where'-statement the update will be performed on ALL the records in the table!

---

13. Deleting a record from a table

delete from tablename    
where fieldname=condition
Be arefull in using this command!! Ommitting the 'where'-statement will erae ALL the records in the table and leave you with an empty table!!!

---

14. Adding a field to the records in a table:

alter table tablename    
add fieldname datatype
The new field is filled with NULL and can be filled using the update-command after adding the field.

---

15. Changing the width of a field

alter table tablename 
alter column fieldname newdatatypealter table tablename   
modify fieldname newdatatype

---

16. Removing field from the records

alter table tablename 
drop column fieldnamealter table tablename   
drop fieldname

---

17. Combine two querries:

select fieldname from tablename
union
select fieldname2 from tablename2
order by fieldname
This union will remove all duplicates. To retain duplicates you have to use 'union all'

---

18. Basic SQL-functions:

select avg(fieldname) from tablename
Returns the arithmeticaverage of the fields.

select count(*) from tablename
where fieldname=condition
Returns the number of records that match the condition.

select max(fieldname) from tablename
Returns the largest value of fieldname.

select min(fieldname) from tablename
Returns the smallest value of fieldname.

select sum(fieldname) from tablename
Returns the summation value of fieldname.

select convert(newdatatype, fieldname) from tablename
converts one datatype into another.

---

19. String functions

ASCII returns ASCII code of leftmost character of a characterexpression.

CHAR converts an ASCII code to a character.

SOUNDEX returns four-digit code used to compare two strings with DIFFERENCE.

DIFFERENCE returns difference between values of two character expressions returned by SOUNDEX.

LEFT returns part of character string, starting at number of character from the left specified in the argument.

LOWER converts uppercase strings to lowercase.

UPPER converts lowercase strings to uppercase.

LTRIM removes leading spaces from a string.

RTRIM removes trailing spaces from a string.

CHARINDEX returns starting position of specified character expression in a string.

PATINDEX rerurns starting position of first occurence of a substring in a string.

REPLICATE returns multiple sets of characters specified in the first argument. The second argument specifies number of sets. (eg. select replicate ('a', 5) returns 'aaaa')

REVERSE returns reverse order of a string of characters.

RIGHT returns part of character string, starting at number of character from the right specified in the argument.

SPACE returns a string of spaces, length specified in argument.

STR converts numeric data to character data.

STUFF inserts a string into another string.

SUBSTRING returns a part of a string (arguments are startpoint and length).

+ (concatenetion) concatenates two or more strings.

---

20. Arithmetic functions:

ACOS, ASIN, ATAN, ATAN2, COS, COT, SIN, TAN, DEGREES, RADIANS, CEILING, FLOOR, EXP, LOG, LOG10, PI(), POWER, ABS, RAND, ROUND, SIGN, SQRT.

---

21. TEXT and IMAGE functions:

SET TEXTSIZE specifies number of bytes displayed for data stored as TEXT or as IMAGE.

TEXTPTR returns pointer to first database page of stoed text.

READTEXT extracts substring from data stored as TEXT or as IMAGE.

TEXTVALID check validity of textpointer.

---

22. Date functions:

DATENAME returns specified part of date as characterstring.

DATEPART returns specified part of date as integer value.

GETDATE returns current date and time.

DATEADD returns value of the date with an additional date interval added.

DATEDIFF returns difference between parts of two specified dates.

---

23. Views:

Note that a view under SQL is the same as a query you create with Access.

23.1 Create views:

create view viewname as
select fieldname1, fieldname2, ... from tablename1, tablename2, ...
where fieldname = conditionYou can't edit a view using SQL. You can use the Enterprise Manager of MS SQL to edit a view or you can delete a view and recreate it with the same name.
You can use alternative names for the columns in a view:
create view viewname (col1, col2, col3, ...)as
select fieldname1, fieldname2, ... from tablename1, tablename2, ...
where fieldname = condition

23.2 Display definition of a view:

sp_helptext viewnameTo prevent the possibility to view the definition of a view you can use encryption:
create view viewname with encryption as
select fieldname1, fieldname2, ... from tablename1, tablename2, ...
where fieldname = condition

23.3 Display associations of a view:

sp_depends viewname

23.4 Delete a view from the database:

drop view viewname1, viewname2, ...

23.5 Insert records through a view:

insert into viewname
values ('value1', 'value2', ...)You can insert rows through the view that DON'T match the WHERE-statement inthe view definition, but then you can't retrieve the new row with the view. If you want to prevent this you can use the check option:
create view viewname as
select fieldname1, fieldname2, ... from tablename1, tablename2, ...
where fieldname = condition with check option

23.6 Delete records through a view:

delete from viewname where fieldname = conditionYou can't delete records through a view that don't match the WHERE-statement in the view definition.

23.7 Updating records through a view:

update viewname set fieldname='value' where fieldname=conditionYou can update a record through a view so that it doesn't match the WHERE-statement anymore. You can't update a view if the updated columns belong to different tables.

---

24. Indexes:

24.1 Simple index:

create index index_name
on table_name (fieldname)

24.2 Unique index:

create unique index index_name
on table_name (fieldname)This kind of index enforces integrity of the table by disallowing duplicete values in the indexed field.

24.3 Clustered index:

create clustered index index_name
on table_name (fieldname)A clustered index forces SQL Server to phisicaly store the table data in the exact order of of the index. This improves the performance of the table. You can only have obne clustered index on a table, the selected fieldname should be choosen carefully and every table should have an clustered index.

24.4 Display index info:

sp_helpindex tablename

24.5 Deleting an index:

drop index table_name.index_name

24.6 Primary and foreign keys:

You can specify a primary key on a table while creating it (see constraint), or you can add a primary key to an existing table by altering it. alter table table_name add constraint constraint_name primary key

24.7 Display primary and foreign keys info:

sp_helpconstraint table_name

24.6 Deleting primary and foreign keys:

alter table table_name drop constraint constraint_name

---

25. Transaction:

A transaction is a series of SQL-statements performed at once, with the ability to undo the changes if something goes wrong during the processing of the statements. A transaction is enclosed in "begin tran" and "commit tran".Example:

begin tran
 update tablename
 set fieldname = newvalue
 where fieldname = condition

 update tablename2
 set fieldname =  newvalue
 where fieldname = condition

 if @@error != 0
 begin
  rollback tran
  print 'Error occured, no rows updated'
  return
 end
commit tran

You can nest one transaction ino another by using named transactions. Be shure however to include a 'commit tran' for every 'begin tran' to avoid leaving a transaction open.
Example:

begin tran tran1_name
 update tablename
 set fieldname = newvalue
 where fieldname = condition

  begin tran tran2_name
   update tablename
   set fieldnname = newvalue
   where fieldname = condition
   if @@error != 0
   begin
    rollback tran tran2_name
    print 'Failed to update tablename'
    return
   end
  commit tran tran2_name
commit tran tran1_name

---

26. Rules:

A rule controls the values that can be entered into a field of a table. To apply a rule to a field you have to take two steps: create the rule and bind the rule to the field.

26.1 Create a rule:

create rule rule_name
as @fieldname in ('value1','value2','value3')

26.2 Bind a rule:

sp_bindrule 'rule_name', 'table_name.field_name'

26.3 Unbind a rule:

sp_unbindrule table_name.field_name

26.4 Drop a rule:

drop rule rule_name
!!You first have to unbind the rule before you can drop it!!To display rule bindings you can use 'sp_help tablename', to display the rules you can use 'sp_helptext rule_name' and to rename a rule you can use 'sp_rename rule_name, new_name'.

---

27. Defaults:

A default is create to provide a value for a field if the user doesn't fill in one. Just like creating rules you have to make to steps to apply ad efault: create it and bind it to a field in a table.

27.1 Create a default:

create default default_name as value

27.2 Bind a default:

sp_bindefault default_name, 'tablename.fieldname'

27.3 Unbind a default:

sp_unbindefault 'tablename.fieldname'

27.4 Drop a default:

drop default default_name
!!You first have to unbind the default before you can drop it!!To display default bindings you can use 'sp_help tablename', to display the default you can use 'sp_helptext default_name' and to rename a default you can use 'sp_rename default_name, new_name'.

---

28. Stored procedures:

Stored procedures are routines or series of SQL-commands that run on the server side. The benefits are performance, because the server typically is the most powerfull computer on the network, and security, because you can control add/change/delete/list operations.

28.1 Create a stored procedure:

create procedure procedure_name
as procedure_commandsTo execute the procedure you use 'exec procedure_name'. You can use parameters in procedures to pass values to the procedure:
create procedure procedure_name (@p1 type, @p2 type, ...)
as insert into table_name
values (@p1, @p2, ...)
Now you can execute this procedure by passing values to it:
exec procedure_name (value1, value2, ...)
You can also use variables in a procedure. You first have to declare them (declare @var_name var_type) and then you can assign a value to it (select @var_name = expression).

28.2 Display a stored procedure:

sp_helptext procedure_name

28.3 Delete a stored procedure:

drop procedure procedure_nameIf you want to alter a procedure, you first have to drop it and then recreate it under the same name.

28.4 Procedure Auto Execution:

You can automaticaly run a procedure every time the server is started by using:sp_makestartup procedure_name
sp_unmakestartup procedure_name removes the procedure from the startupprocedures
sp_helpstartup displays the procedures currently running at startup

28.5 Flow-control statements:

The following statements can be used in stored procedures to control the flow of the procedure:

• if ... else ...
• begin ... end
• while ...
• break
• continue
• print
• goto
• return
• raiserror
• waitfor
• case ... when

---

29. Datatypes:

This is an overview of the different datatypes used by the different servers.

MySQL	Borland InterBase Server	MS SQL Server	Sybase SQL
tinyint	-	tinyint	tinyint
smallint	smallint	smallint	smallint
mediumint	-	-	-
int	int	int	int
bigint	-	-	-
-	numeric	numeric	numeric
decimal	decimal	decimal	decimal
-	-	real	real
float	float	float	float
double	double precision	-	double
date	date	-	date
-	-	smalldatetime	-
datetime	-	datetime	-
timestamp	-	timestamp	timestamp
time	-	-	time
year	-	-	-
char(n)	char(n)	char(n)	char(n)
varchar(n)	varchar(n)	varchar(n)	varchar(n)
-	-	-	long varchar
tinytext	-	-	-
text	-	text	-
-	-	ntext	-
mediumtext	-	-	-
longtext	-	-	-
-	nchar(n)	nchar(n)	-
-	nchar varying(n)	nvarchar(n)	-
-	-	binary(n)	binary(n)
-	-	varbinary(n)	-
-	-	-	long binary
tinyblob	-	-	-
-	-	image	-
blob	blob	-	-
mediumblob	-	-	-
longblob	-	-	-
enum	-	-	-
set	-	-	-
-	-	bit	-
-	-	uniqueidentifier	-
-	-	money	-
-	-	smallmoney	-

---