SQL Helper Class

Microsoft .net framework consists of ADO .NET which enables the developer to interact with the database. ADO .NET provides many rich features that can be used to retrieve and display data in a number of ways. Apart from the flexibility provided by the ADO .NET, sometimes we find ourselves repeating the same code again and again. Consider that at some point in our application we need to pass some parameters and retrieve some information from the database. We can perform this task by writing 5-6 lines of code which is cool. But when later we need to pass the parameters we have to write those 5-6 lines again which is not cool.

Methods

• ExecuteDataset Retrieve multiple rows from the database and return value to DataSet class.

• ExecuteNonQuery Executes a Transact-SQL statement against the connection and returns the number of rows affected
  

• ExecuteNonQueryReturn No return value.
  

• ExecuteReader Retrieve multiple rows from the database and return value to SqlDataReader class.
  

• ExecuteScalar Retrieve a single row instead of group of rows and return value to object.
  

• ExecuteXmlReader Sends the CommandText to the Connection and builds an XmlReader object.

Summary

• It is used for Database Access other than System.Data.SqlClient or System.Data.Oledb

• SqlHelper() - This method used to reduce the lines of connecting the database than when we are using the SqlDataAdapter or OleDbAdapter.

• You can download sql helper dll at this
  

Sample Code

' Very short command
Dim nResult as integer
nResult = Convert.ToInt32(SqlHelper.ExecuteScalar(ConnectionString, CommandType.Text, "Select Count(id) from table"))

Using SqlParameter Class

Represents a parameter to a SqlCommand and optionally its mapping to DataSet columns. This class cannot be inherited.

You should use parameters to filter queries in a secure manner. But I recommend to use parameters when you try to pass the datetime value in your query.

The process of using parameter contains two steps:

• create SqlParameter object and insert there value with applicable properties
• define the parameter in the SqlCommand command string, and assign the SqlParameter object to the SqlCommand object. When the SqlCommand executes, parameters will be replaced with values specified by the SqlParameter object.

Sample Code

Imports Namespace: System.Data.SqlClient

' Insert string
Dim sql As String = " INSERT INTO tblZipCode([ZIPCODE], [STATE], [CITY], [TestDate]) VALUES(@ZIPCODE, @STATE, @CITY), @TestDate"

' Create sql parameter
Dim param(3) As SqlParameter

param(0) = New SqlParameter("@ZIPCODE", SqlDbType.VarChar)
param(0).Value = "60000"

param(1) = New SqlParameter("@STATE", SqlDbType.VarChar)
param(1).Value = "Statename"

param(2) = New SqlParameter("@CITY", SqlDbType.VarChar)
param(2).Value = "Cityname"

' Recommend to use sql param when you try to send datetime value
param(3) = New SqlParameter("@TestDate", SqlDbType.DateTime)
param(3).Value = DateTime.Now

' Create Connection string
Dim sConnection As New SqlConnection("server=(local);uid=sa;pwd=pass;database=db")
sConnection.Open()

' Create Sql Command
Dim command As SqlCommand = sConnection.CreateCommand()
command.CommandText = sql

' Add Parameter to command
command.Parameters.AddRange(param)

' Execute command
Dim nResult As Integer = command.ExecuteNonQuery()

If nResult > 0 Then
Console.WriteLine("Insert completed")
End If

sConnection.Close()
command.Dispose()

SqlCommand Methods

ExecuteNonQuery

• Overridden. Executes a Transact-SQL statement against the connection and returns the number of rows affected.

ExecuteReader

• Overloaded. Sends the CommandText to the Connection and builds a SqlDataReader.

ExecuteScalar

• Overridden. Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

ExecuteXmlReader

• Sends the CommandText to the Connection and builds an XmlReader object.

Populate a DataSet from a Database

SqlDataAdapter class represents a set of data commands and a database connection that are used to fill the DataSet and update a SQL Server database. This class cannot be inherited.

Getting data from a database is easy, and working with data is easier than before. The most important concept to remember is that the DataSet is a data structure separate and distinct from a data store. Although you get data from a database in this example, it doesn't matter where the data comes from; the DataSet will always present a consistent programming model. It is a simple collection of data with relational database characteristics. There is no Load, Open, or Execute on a DataSet because it doesn't know where it gets its data from. This section describes how to use a SqlDataAdapter to get data from a database into a DataSet.

Sample Code (with MsSql)

Dim sConnection As String = "server=(local);uid=sa;pwd=password;database=yourDatabase"

Dim objDataAdapter As New SqlDataAdapter("Select * From tableName", sConnection)
Dim dsResult As New DataSet("Result")

If Not IsNothing(objDataAdapter) Then
' Fill data into dataset
objDataAdapter.Fill(dsResult)

objDataAdapter.Dispose()
End If

' Test by bind data into datagridview control
Me.DataGridView1.DataSource = dsResult.Tables(0)

Retrieving Data Using the DataReader

You can use the ADO.NET DataReader to retrieve a read-only, forward-only stream of data from a database. Results are returned as the query executes, and are stored in the network buffer on the client until you request them using the Read method of the DataReader. Using the DataReader can increase application performance both by retrieving data as soon as it is available, rather than waiting for the entire results of the query to be returned, and (by default) storing only one row at a time in memory, reducing system overhead.

The DataReader class in .NET provides similar functions to SQL Cursors, which are actually not supported in the .NET Framework. DataReades are used to efficiently retrieve a forward-only stream of data from a database. DataReaders are appropriate when the need is to simply display the result set, as only one record at a time is ever present in memory. The DataReader is mainly used in scenarios wherein data need not be updateable nor should persist across multiple requests.

Sample Code (with MsSql)
You must import SqlClient .

Imports System.Data.SqlClient

Dim sConnection As String = "server=(local);uid=sa;pwd=PassWord;database=DatabaseName"

Dim objCommand As New SqlCommand
objCommand.CommandText = "Select * From tablename"
objCommand.Connection = New SqlConnection(sConnection)
objCommand.Connection.Open()

Dim objDataReader As SqlDataReader = objCommand.ExecuteReader()

If objDataReader.HasRows Then
Do While objDataReader.Read()
Console.WriteLine(" Your name is: " & Convert.ToString(objDataReader(0)))
Loop
Else
Console.WriteLine("No rows returned.")
End If

objDataReader.Close()
objCommand.Dispose()

Arithmetic overflow error converting expression to data type datetime

I got this error when I tried to convert string to datetime in query.

This is Culture problem.

What culture is your webserver?
What culture is your ASP.NET application using?
What culture is your database?

This is due to an inconsistency in the language settings between the web server and SQL server (or SQL server user).

By default, SQL Server users are set to "us_english" as the default language. The result is that different date formats are being used and are therefore not recognized by the database.

There are several options to remedy this situation:

1. Change the SQL user's default language to match the language settings on the SQL server.To do this, open SQL enterprise manager and connect to your database server. Expand Security -> Logins and open the properties dialog for the user that you are using to connect to the database. On the main properties page, change the language to equal the same language that is set on your web server.

2. Modify your connection stringOn your SQL connection string, add a parameter of Language= and set it equal to the correct language. For example, Language=British. (www.prezzatech.com)

What is ADO.NET?

ADO.NET
From Wikipedia, the free encyclopedia

ADO.NET is a set of computer software components that can be used by programmers to access data and data services. It is a part of the base class library that is included with the Microsoft .NET Framework. It is commonly used by programmers to access and modify data stored in relational database systems, though it can also be used to access data in non-relational sources. ADO.NET is sometimes considered an evolution of ActiveX Data Objects (ADO) technology, but was changed so extensively that it can be conceived of as an entirely new product.

ADO.NET consists of two primary parts:

1. Data provider

These classes provide access to a data source, such as a Microsoft SQL Server or Oracle database. Each data source has its own set of provider objects, but they each have a common set of utility classes:

* Connection: Provides a connection used to communicate with the data source. Also acts as an abstract factory for command objects.
* Command: Used to perform some action on the data source, such as reading, updating, or deleting relational data.
* Parameter: Describes a single parameter to a command. A common example is a parameter to a stored procedure.
* DataAdapter: A bridge used to transfer data between a data source and a DataSet object (see below).
* DataReader: A class used to efficiently process a large list of results one record at a time.


2. DataSets

DataSets objects, a group of classes describing a simple in-memory relational database, were the star of the show in the initial release (1.0) of the Microsoft .NET Framework. The classes form a containment hierarchy:

* A DataSet object represents a schema (either an entire database or a subset of one). It can contain tables and relationships between those tables.
- A DataTable object represents a single table in the database. It has a name, rows, and columns.
- A DataView object "sits over" a DataTable and sorts the data (much like a SQL "order by" clause) and filters the records (much like a SQL "where" clause) if a filter is set. An in-memory index is used to facilitate these operations. All DataTables have a default filter, while any number of additional DataViews can be defined, reducing interaction with the underlying database and thus improving performance.
- A DataColumn represents a column of the table, including its name and type.
- A DataRow object represents a single row in the table, and allows reading and updating of the values in that row, as well as retrieving any rows that are related to it through a primary-key foreign-key relationship.
- A DataRowView represents a single row of a DataView the distinction between a DataRow and DataRowView is important when iterating over a result set.
- A DataRelation is a relationship between tables, such as a primary-key foreign-key relationship. This is useful for enabling DataRow's functionality of retrieving related rows.
- A Constraint describes an enforced property of the database, such as the uniqueness of the values in a primary key column. As data is modified any violations that arise will cause exceptions.

A DataSet is populated from a database by a DataAdapter whose Connection and Command properties have been set. However, a DataSet can save its contents to XML (optionally with an XSD schema), or populate itself from XML, making it exceptionally useful for web services, distributed computing, and occasionally-connected applications.

ADO.NET and Visual Studio.NET

Functionality exists in the Visual Studio .NET IDE to create specialized subclasses of the DataSet classes for a particular database schema, allowing convenient access to each field through strongly-typed properties. This helps catch more programming errors at compile-time and makes the IDE's Intellisense feature more useful.