19 Dec: Must Read Codes [1-5]
Dec19

19 Dec: Must Read Codes [1-5]

Welcome to CodeSpread! We are sharing few must read codes/concepts which are required now and then. Each of these codereads doesn’t require a full length descriptive article so combining them into groups of five. CodeReads #1, Usage of ‘this’ keyword in C#. To qualify members hidden by similar name. To have an object pass itself as a parameter to other methods. To have an object return itself from a method. To declare indexers. To declare extension methods. To pass parameters between constructors. To internally reassign value type (struct) value. fbsource: https://www.facebook.com/codespread/posts/544740368882522 CodeReads #2, ISNULL and COALESCE though equivalent, can behave differently. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL. fbsource: https://www.facebook.com/codespread/posts/545045568852002 CodeReads #3, Regular Cast: string str= (string) strToCast; Method Call: string str= strToCast.ToString(); Safe Cast: string str= strToCast as string; fbsource: https://www.facebook.com/codespread/posts/545045568852002 CodeReads #4, Control State maintains the behavior of the controls whereas View State maintains the content of the controls. Also, Control State is available even though View State for the control is disabled. fbsource: https://www.facebook.com/codespread/posts/547846855238540 CodeReads #5, In javascript, Body.Onload() event occurs only after the DOM has been loaded, plus all associated resources like images, flash etc are also completely loaded. $(document).ready() occurs when the DOM has been loaded but associated resources like images, flash etc may be pending. There can be multiple document.ready() in a page but Body.Onload() event will be only one. fbsource:...

Read More
SQL: Usage of char, varchar and varchar(MAX)
Nov24

SQL: Usage of char, varchar and varchar(MAX)

These are character data types in SQL. We will take an example and describe each type. Later we determine the cases where one of them can be applied. Create Table testable( Name char(20), Email varchar(250), Address varchar(Max)) CHAR We usually call it char or character. In the above example, Name is declared with char(20) as type. Here, 20 is the storage size and it does not depend on the actual length of name entered, whether is below 20 or above 20. It is always fixed length and can have value from from 1 through 8,000. The default length is 1 and can be mentioned as in below example. DECLARE @testvariable AS char VARCHAR We usually call it varchar or varying character. In the above example, Email is declared with varchar(250) as type. Here, 250 is the maximum storage size. The storage size is flexible and dependent on the actual length of data entered so finally storage size is computed as actual length + 2 bytes. It is always variable length and can have value from 1 through 8,000. Plus point is that the data entered can be 0 characters in length. VARCHAR(MAX) The only difference is the max storage limit which in this case is 2^31-1 bytes. Suggestions: When we are considerably sure about the length of the data in column, we should use char. The main concern is consistency about the column entries. Varchar is exact reverse condition of char. If there is a lack of consistency in column entries or length of the column data vary considerably, use varchar. Varchar(max) is a superlative form and used when the size might exceed 8,000 bytes. Conclusion: In the real world, we are more concerned about the storage space and above points, though miniscule, can really help...

Read More
Questions related to Connection Pooling in .NET
Sep20

Questions related to Connection Pooling in .NET

What is Connection Pooling in .NET? Here, the term connection refers to connecting to a data source and Pooling means grouping together of resources. So, Connection + Pooling means grouping together of resources like connection. A more formal definition would be; a Connection Pool is a group of open and reusable connections. Why it is required? We know that, to execute any query against our database, we are required to establish a connection with the database server and then execute the query against that database server. But this whole process requires lot of handshaking, parsing of the connection string, authentication of the credentials and so on. Is this a lot we are doing to open a connection? The answer is No, But Imagine a situation where the same process is repeated for many requests. At this point, much time consuming and resource eating, similar connections are opened and yes, closed also. Connection pooling addresses this problem by bypassing the whole process of repetitive cumbersome connection creation. Connection Pool maintains a set of active connections for each given connection configuration. When there are many repetitive requests, one of the idle and free connection is taken from the pool to serve each of the requests. Advantages: Improvement of performance for ex: less instances of opening and closing of connections to the database. Improvement of scalability for ex: Reusability of connections to cater maximum users. Disadvantages: Idle open connections. How it works? Note that, Connection Pool maintains a set of active connections for each given connection configuration. A Connection Pool is created starting from the first request where a connection is created for the first time. Ok, now we have a live connection and we did some DB operations like executing stored procedure or a query. Once work is over, this connection is closed or disposed and is released back into the pool where it sits idle and wait for next request. Similarly, successive connection objects are created and added to the pool as needed, and it can maximum reach till Max Pool Size(default: 100). Note: MinPoolSize is 0 else specified Try { SqlConnection connection = new SqlConnection( "Integrated Security=SSPI;Initial Catalog=Northwind") { connection.Open(); } } Catch(Exception ex) { //Throw Exception } Finally { Connection.close(); } How can a connection be deleted from Connection Pool? We have two methods viz. ClearAllPools: It clears the connection pools for a given provider, and ClearPool ; It clears the connection pool that is associated with a specific connection. Or else “iisreset” clears all the connection pools. Simple! More : We would like to know the active connections in the pool. To do this, open the Query...

Read More
IDENTITY as SQL Property and IDENTITY as SQL Function
Aug29

IDENTITY as SQL Property and IDENTITY as SQL Function

IDENTITY as SQL Property: Almost all the time, We have a table with a primary key and would like to insert a new value automatically in the primary key field. To achieve this, We have IDENTITY property in SQL which creates an auto-increment field in the table. Syntax: IDENTITY [ (seed , increment) ] example: (1,1) An identity column has a name, initial seed and step.  Seed is the starting value for IDENTITY and Increment is the incremental value. In the example, Seed is 1 and Increment is 1. Note: In case if values are not specified the default is (1,1). Example:  Create a new table Employee with Employee Id as an IDENTITY column. CREATE TABLE Employee ( Employee_ID int IDENTITY(1,1) PRIMARY KEY, Employee_FirstName varchar(255), Employee_LastName varchar(255) ) INSERT INTO Employee (Employee_FirstName, Employee_LastName) VALUES (‘Code’, ‘Spread’) INSERT INTO Employee< (Employee_FirstName, Employee_LastName) VALUES (‘Code1’, ‘Spread1’) @@IDENTITY It returns last identity value entered into a table. Example: The following example inserts a row into a table with an identity column (Employee_ID) INSERT INTO Employee (Employee_FirstName, Employee_LastName) VALUES (‘Code2’, ‘Spread2’) GO SELECT @@IDENTITY AS 'Identity'; GO Here, It uses @@IDENTITY to display last identity value entered into a table. SET IDENTITY_INSERT What if I want to insert values into an IDENTITY column? SQL provides  SET IDENTITY_INSERT to insert values into an IDENTITY column. But before that, set the IDENTITY_INSERT option to “ON”. Note: The setting of SET IDENTITY_INSERT is set at execute or run time. Example: SET IDENTITY_INSERT dbo. Employee ON; CREATE TABLE Employee ( Employee_ID int IDENTITY(1,1) PRIMARY KEY, Employee_FirstName varchar(255), Employee_LastName varchar(255) ) INSERT INTO Employee VALUES (3,‘Code1’, ‘Spread1’) SET IDENTITY_INSERT dbo. Employee OFF; Reset IDENTITY The following line resets the Identity value for the Customer table to 0 so that the next record added starts at 1. DBCC CHECKIDENT('Customer', RESEED, 0) IDENTITY as SQL Function The IDENTITY function can only be used when the SELECT statement has an INTO clause. Example: SELECTIDENTITY (INT, 100, 5) ASNEW_ID,Employee_FirstName,Employee_LastName) INTO #tempEmployee From Employee The first parameter for the IDENTITY function is that data type The second parameter is the seed. The third parameter is the increment. Note: Only one Identity column is possible for a...

Read More
Verify existence of SQL tables using Object_ID()
Aug27

Verify existence of SQL tables using Object_ID()

Why Object_ID() ? Let’s create a SQL table “Employee” CREATE TABLE Employee ( [EmployeeID] [int] IDENTITY(1,1) PRIMARY KEY, [EmployeeName] [varchar](50) NULL, [Salary] [int] NULL, ) Looks ok!! But did I check whether the same table already exists in database? Good Question! Let’s check then, by verifying that the table has an object ID. IF OBJECT_ID(N‘Employee’) IS NOT NULL DROP TABLE Employee; CREATE TABLE Employee ( [EmployeeID] [int] IDENTITY(1,1) PRIMARY KEY, [EmployeeName] [varchar](50) NULL, [Salary] [int] NULL, ) In the above example, if the object id is available for the table then it is dropped else the drop statement is jumped over. What is Object_ID()? Returns the database object identification number of a schema-scoped object. What is database object identification number? The OBJECT_ID is used to identify the object uniquely in the system base tables. It is the primary key of the sys.sysschobjs base table used by the sys.objects metadata view and appears in many other of the metadata views. e.g sys.partitions. What is a schema scoped object? A schema scoped object is one that belongs to a schema (e.g a table, view, stored procedure). Objects that are not schema-scoped, such as DDL triggers, cannot be queried by using OBJECT_ID. Non schema scoped objects metadata is still stored in sys.sysschobjs but does not show up in the sys.objects view. To obtain the object identification numbers for them, query the appropriate catalog view. SELECT OBJECT_ID FROM sys.triggers WHERE name = ‘DatabaseTriggerLog’. What about temporary tables? When specifying a temporary table name, the database name must precede the temporary table name, for example: SELECT OBJECT_ID('tempdb..#mytemptable') Just adding two extra lines of code can save lot of efforts. Ref: • http://technet.microsoft.com/en-us/library/aa276843%28v=sql.80%29.aspx • http://stackoverflow.com/questions/9372867/what-does-object-id-do-in-sql-server •...

Read More
Page 1 of 512345