| |
This method is very similar to the " USING SERVER SIDE VARIABLES WITH CLASS" article except using client side cookies. For example, In order to maintain Order ID in shopping cart, Response.Cookies("ORDERID").Value method can be used but it is truly unwise to reuse the same variable name again and again every time when you need to get the Order ID value.
What if you need to change the "ORDERID" variable name to "$_ORDERID_$", you will have to find and replace all the same variable in the files.
What if you need to apply encryption/decryption method to the sensitive data storing in Session variables, again you will have to find and apply the method to all the same variable in the files.
In order to avoid such a hassle method looking for the same variable in every places, this is how I always practice using and referencing the server side variables with class.
Create a new class, OrderID.cs, file in your project.
The file structure will be looked like this.
Inside the OrderID.cs, copy and place these codes.
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;
namespace MyCookies
{
public class OrderID
{
//Assign the Order ID value into to current HTTP Cookie variable OrderID
public static string Set
{
set { HttpContext.Current.Response.Cookies["OrderID"].Value = value; }
}
//Get the Order ID value from current HTTP Cookie variable OrderID
public static string Get
{
get { return GetString(HttpContext.Current.Request.Cookies["OrderID"].Value); }
}
//Delete Cookie variable OrderID from current HTTP
public static void Clear()
{
HttpContext.Current.Response.Cookies["OrderID"].Expires = DateTime.Now;
}
//Get the Order ID value from current HTTP Cookie variable OrderID
public static DateTime SetExpiration
{
set { HttpContext.Current.Request.Cookies["OrderID"].Expires = value; }
}
//////////////////////////////////////////////////////////////////////////////////////////////////////
//This GetString Method should not be in this class,
//This method should put into seperate class which hold a collection of getting standard data type
//////////////////////////////////////////////////////////////////////////////////////////////////////
public static string GetString(object value)
{
try
{
return Convert.ToString(value);
}
catch
{
return String.Empty;
}
}
/////////////////////////////////////////////////////////////////////////////////////////////////////
}
}
Now, you are ready to start using Cookie variable through the class.
To assign your "OrderID" to the session variable, just simple assign as below
MyCookies.OrderID.Set = "123";
To get your "OrderID" from session variable,
############## = MyCookies.OrderID.Get;
To clear the "OrderID" from session variable when it is not longer used.
MyCookies.OrderID.Clear();
To set the cookie expiration for 24 hours.
MyCookies.OrderID.SetExpiration = DateTime.Now.AddHours(24);
I really hate seeing the same session variable and cookie variable everywhere scattering through out the whole entire project. For example, In order to maintain Order ID in shopping cart, Session["ORDERID"] or Response.Cookies("ORDERID").Value method can be used but it is truly unwise to reuse the same variable name again and again every time when you need to get the Order ID value.
What if you need to change the "ORDERID" variable name to "$_ORDERID_$", you will have to find and replace all the same variable in the files.
What if you need to apply encryption/decryption method to the sensitive data storing in Session variables, again you will have to find and apply the method to all the same variable in the files.
In order to avoid such a hassle method looking for the same variable in every places, this is how I always practice using and referencing the server side variables with class.
Create a new class, OrderID.cs, file in your project.
The file structure will be looked like this.
Inside the OrderID.cs, copy and place these codes.
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;
namespace MySession { public class OrderID { //Assign the Order ID value into to current HTTP session variable $_ORDER_ID_$ public static string Set { set { HttpContext.Current.Session["$_ORDERID_$"] = value; } }
//Get the Order ID value from current HTTP session variable $_ORDER_ID_$ public static string Get { get { return GetString(HttpContext.Current.Session["$_ORDERID_$"]); } }
//Delete session variable $_ORDER_ID_$ from current HTTP public static void Clear() { HttpContext.Current.Session.Remove("$_ORDER_ID_$"); }
////////////////////////////////////////////////////////////////////////////////////////////////////// //This GetString Method should not be in this class, //This method should put into seperate class which hold a collection of getting standard data type ////////////////////////////////////////////////////////////////////////////////////////////////////// public static string GetString(object value) { try { return Convert.ToString(value); } catch { return String.Empty; } } ///////////////////////////////////////////////////////////////////////////////////////////////////// }
Now, you are ready to start using Session variable through the class.
To assign your "OrderID" to the session variable, just simple assign as below
MySession.OrderID.Set = "12345";
To get your "OrderID" from session variable,
############## = MySession.OrderID.Get;
To clear the "OrderID" from session variable when it is not longer used.
MySession.OrderID.Clear();
I had come across the best database standard and practice while I worked with $$$$ Logistic Company. The n tire C# Framework was the best user oriented framework that I had ever experienced as it was initially constructed by the Microsoft's .Net team only for the $$$$ Logistic Company.
Database Standards
This database standard applies for both SQL Server and Oracle Databases. Best Practices and Indexing Section might differ in different scenarios depending upon the database design, indexes, volume of data etc. So, test all the possible ways a query could be written and go with the efficient one.
Object Names
1. The Object Name Should be with in 30 characters as Oracle and Db2 Support Only Max 30 Characters.
2. Tables Name Should be kept with in 25 Characters since we create Language tables by Suffixing the Language code. Ex “_jpn” for Japanese Language.
3. Do not use reserved or key words as Object Names.
4. Provide Meaningful Names to the database objects and columns. Table name has to be Plural and should end with‘s’ for e.g. Persons, the first letter of the table name has to be in Upper case.
5. Always use case consistently in your code. On a case insensitive server, your code might work fine, but it will fail on a case sensitive SQL Server if the code is not consistent in case.
6. Do not use spaces in between Object Names.
7. The Names of Tables, Views etc must be in camel case for Sql Server Objects.
8. The Names of Tables, Views in oracle are in Upper Case by default.
9. Table Names or Column Names should not normally start with a digit.
10. View Names Should Start with “VW_”
11. The Foreign key Name should be kept same as the Primary Key Name
12. Create Foreign Key Constraints Starting with “PK_” and Adding the Table Name.
13. Foreign Key Name “FK_<>_<>” for Sql Server since we already have foreign key in the same manner.
14. Foreign Key Name for Oracle should start with “FK_” and Abbreviated Table Names in the same Format as Sql Server can be used but should be Limited to 30 characters.
15. If Required Separate Object Names or Column Names using Underscore and not by Hyphen.
16. The Name of Stored Procedures Should Start with “USP_”.
Guidelines for Indexing
1. Before you can identify and create optimal indexes for your tables, you must thoroughly understand the kinds of queries that will be run against them.
2. Don't automatically add indexes on a table because it seems like the right thing to do. Only add indexes if you know that they will be used by the queries run against the table.
3. Static Tables or Lookup Tables (those tables that change very little, or not at all) can be more heavily indexed than dynamic tables (those that are subject to many Inserts, UPDATES, or DELETES) without negative effect.
4. Indexes should be considered on columns that are frequently accessed by the WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT clauses. Without an index, each of these operations will require a table scan of your table, potentially hurting performance
5. Don't over index or unnecessarily add Index to your tables, as every index you add increases the time it takes to perform INSERTS, UPDATES, and DELETES Apart from occupying disk space. There must be a fine line drawn between having the ideal number of indexes (for Selects) and the ideal number for data modifications.
6. When creating a Sql Server database enable the option “Auto Create Statistics”. The sql server Query Optimizer, when running queries, will consider if there is any benefit to adding this statistics for any column that doesn't already have statistics for it. This is for columns without indexes. This is a good thing as it helps provide better information to the Query Optimizer so that better optimized execution plans are created to execute queries.
7. A good candidate for indexing would be a column containing unique number for each record, while a poor candidate would be a column that only contains a small range of numeric codes; this consideration applies to all database types, not just numbers.
8. Point queries, queries that return a single row. When Creating an index to speed the retrieval of a single record, we should create it as non-clustered index rather than as clustered index.
9. Clustered indexes define the physical sorting of a database table’s rows in the storage media. For this reason, each database table may have only one clustered index. If a PRIMARY KEY constraint is created for a database table and no clustered index currently exists for that table, SQL Server automatically creates a clustered index on the primary key.
10. A clustered index is particularly efficient on columns that are often searched for ranges of values,
a) Return a range of values by using operators such as BETWEEN, >, >=, <, and <=.
b) Return large result sets.
c) Use JOIN clauses; typically these are foreign key columns.
d) Use ORDER BY, or GROUP BY clauses.
11. It is important to define the clustered index key with as few columns as possible.
12. The FILLFACTOR and PAD_INDEX settings used to create an Index can affect performance. Also, whether the index is a composite index or not (and what columns it contains) can affect an index's performance. Create the indexes with a FILLFACTOR and a PAD_INDEX of 100 to ensure there is no wasted space. This reduces disk I/O, helping to boost overall performance.
13. If a query search is based on a table scan or partial table scan, then it will create overhead for the SQL Server process with additional page reads, leading to high CPU activity and unresponsiveness.
14. Make sure your queries do 'Index seeks' instead of 'Index scans' or 'Table scans'. A table scan or an index scan is a very bad thing and should be avoided where possible. Exception to this is when the table is too small or when the whole table needs to be processed, the optimizer will choose a table or index scan.
Best Practices
1. Select the rows only required by using a where clause, and select only the required columns names in the SELECT statement. This technique results in fewer disks IO and less network traffic and hence better performance.
2. Use Exists rather than “in” in Select Queries, Exists typically offers better performance than in with sub queries. Exists just checks for the existence of rows, whereas in checks actual values.
3. Use Integer Data type for Ownerorgid and OwnerLocid Columns.
4. Avoid Using Guids for Primary Keys and Use Integer Data Type for Primary Keys. Ensure Each Table has got a Primary Key or Unique Key.
5. Use of Unicode data types like nchar, nvarchar, ntext, only if your database is going to store not just plain English characters, but a variety of characters used all over the world. Use these data types, only when they are absolutely needed as they need twice as much space as non-Unicode data types.
6. Do not use nvarchar or nChar for a Language Dependant Column in the Main table; use only Varchar or Char unless other wise it is really required. When a Data Member is Made Language Dependant and when Language Dependant Tables are created, the Language Column is automatically created as nvarchar or nchar accordingly from the Profile Editor Tool.
7. Prefix the table names with owner names, as this improves readability, avoids any unnecessary confusion and helps in execution plan reuse.
8. Use fully qualified column references when performing joins, always include table name or table aliases name for each column referenced in the query.
9. Do not use column numbers in the ORDER BY clause.
10. Write comments in your stored procedures, triggers and SQL batches generously, wherever something is not very obvious.
11. Try not to use system tables directly. System table structures may change in a future Versions. Wherever possible, use the sp_help stored procedures or INFORMATION_SCHEMA views.
12. Try to avoid wildcard characters at the beginning of a word while searching using the LIKE keyword, as those results in an index scan, which is defeating the purpose of having an index. The following statement results in an index scan, while the second statement results in an index seek.
1. SELECT LocationID FROM Locations WHERE Name LIKE '%bai'
2. SELECT LocationID FROM Locations WHERE Name LIKE 'D%'
13. Use SET NOCOUNT ON at the beginning of your SQL batches, stored procedures and triggers in production environments, as this suppresses messages like '(1 row(s) affected)' after executing INSERT, UPDATE, DELETE and SELECT statements. This in turn improves the performance of the stored procedures by reducing the network traffic.
14. Use char data type for a column, only when the column is non-nullable. If a char column is nullable, it is treated as a fixed length column in SQL Server. So, a char (100), when NULL, will eat up 100 bytes, resulting in space wastage. So, use varchar(100) in this situation. Of course, variable length columns do have a very little processing overhead over fixed length columns. Carefully choose between char and varchar depending up on the length of the data you are going to store.
15. Try to avoid server side cursors as much as possible. Cursors can be easily avoided by SELECT statements in many cases. If a cursor is unavoidable, use a simple WHILE loop instead, to loop through the table. But for a WHILE loop to replace a cursor you need a column (primary key or unique key) to identify each row uniquely.
16. If you must do heavy text-based searches, consider using the Full-Text search feature of SQL Server for better performance.
17. Use the more readable ANSI-Standard Join clauses instead of the old style joins. With ANSI joins, the WHERE clause is used only for filtering data. With older style joins, the WHERE clause handles both the join condition and filtering data.
18. The prefix sp_ is reserved for system stored procedure that ship with SQL Server. Whenever SQL Server encounters a procedure name starting with sp_, it first tries to locate the procedure in the master database, then looks for any qualifiers (database, owner) provided, then using dbo as the owner. So, you can really save time in locating the stored procedure by avoiding sp_ prefix. But there is an exception! While creating general purpose stored procedures that are called from all your databases go ahead and prefix those stored procedure names with sp_ and create them in the master database.
19. Use 'User Defined Data types’; if a particular column repeats in a lot of your tables, so that the data type of that column is consistent across all your tables.
20. Do not store binary files, image files (Binary large objects or Blobs) etc. inside the database. Instead store the path to the binary/image file in the database and use that as a pointer to the actual binary file. Retrieving, manipulating these large binary files is better performed outside the database.
21. Do not call functions repeatedly within your stored procedures, triggers, functions and batches. For example, you might need the length of a string variable in many places of your procedure, but don't call the LEN function whenever it's needed, instead, call the LEN function once, and store the result in a variable, for later use.
22. In your queries and other SQL statements, always represent date in yyyy/mm/dd format. This format will always be interpreted correctly, no matter what the default date format on the SQL Server is.
|