SqlClient Class

Nemiro.Data.dll

SQL Server client class.

Inheritance Hierarchy

System Object
Nemiro.Data.Sql SqlClient

Namespace: Nemiro.Data.Sql
Assembly: Nemiro.Data (in Nemiro.Data.dll) Version: 2.11.4.126 (2.11.4.126)

Syntax

C++

Copy to Clipboard Print

public class SqlClient : IDisposable

public class SqlClient : IDisposable

Public Class SqlClient 
	Implements IDisposable

Public Class SqlClient 
	Implements IDisposable

The SqlClient type exposes the following members.

Constructors

	Name	Description
	SqlClient	Initializes a new instance of the SqlClient class.
	SqlClient(Int32)	Initializes a new instance of the SqlClient class with caching duration.
	SqlClient(String)	Initializes a new instance of the SqlClient class.
	SqlClient(String, Int32)	Initializes a new instance of the SqlClient class.
	SqlClient(String, String)	Initializes a new instance of the SqlClient class.
	SqlClient(Type, Int32)	Initializes a new instance of the SqlClient class with custom cache object and caching duration.
	SqlClient(Type, Object )	Initializes a new instance of the SqlClient with custom cache object.
	SqlClient(String, Type, Object )	Initializes a new instance of the SqlClient class.
	SqlClient(Type, Object , Int32)	Initializes a new instance of the SqlClient class.
	SqlClient(String, Int32, String, Type, Object )	Initializes a new instance of the SqlClient class.
	SqlClient(String, Int32, String, Type, Object , Type)	Initializes a new instance of the SqlClient class.

Top

Methods

	Name	Description
	Connect	Opens a database connection.
	CopyDataToServer	Transferring the specified tables from the instance of DataSet to the database. Used SqlBulkCopy. The fastest and most economical way to move large amounts of data in the database.
	CopyTableToServer	Transferring the specified table to the database. Used SqlBulkCopy. The fastest and most economical way to move large amounts of data in the database.
	Disconnect	Closes the database connection.
	Dispose	Releases all resources used by the object. If necessary, closing all opened connections to the database.
	Equals	(Inherited from Object.)
	ExecuteNonQuery	Executes a Transact-SQL statement against the connection and returns the number of rows affected. This method is used in conjunction with the properties CommandType, CommandText and Parameters.
	ExecuteNonQuery(SqlCommand)	Executes the specified SqlCommand and returns the number of rows affected.
	ExecuteNonQuery(String)	Executes a Transact-SQL statement against the connection and returns the number of rows affected.
	ExecuteScalar	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. This method is used in conjunction with the properties CommandType, CommandText and Parameters.
	ExecuteScalar(SqlCommand)	Executes the specified SqlCommand, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.
	ExecuteScalar(String)	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.
	Finalize	(Inherited from Object.)
	GetData	Executes the query and returns DataSet. This method is used in conjunction with the properties CommandType, CommandText and Parameters.
	GetData(SqlCommand)	Executes the specified SqlCommand and returns DataSet.
	GetData(String)	Executes the specified SQL-statement and returns DataSet.
	GetHashCode	(Inherited from Object.)
	GetRow	Executes the query and returns data row. This method is used in conjunction with the properties CommandType, CommandText and Parameters.
	GetRow(SqlCommand)	Executes the specified SqlCommand and returns строку данных.
	GetRow(String)	Executes the query and returns data row.
	GetTable	Executes the query and returns DataTable. This method is used in conjunction with the properties CommandType, CommandText and Parameters.
	GetTable(SqlCommand)	Executes the specified SqlCommand and returns DataTable.
	GetTable(String)	Executes the query and returns DataTable.
	GetType	(Inherited from Object.)
	MemberwiseClone	(Inherited from Object.)
	ToString	(Inherited from Object.)

Top

Properties

	Name	Description
	Cache	Provides access to parameters caching.
	CacheAccessTimeout	Waiting time gain access to the entry of file cache (File). After this time, if the file entry are not available, an exception is thrown.
	CacheBinder	Type serializer of custom objects.
	CacheBufferAccessTimeout	Waiting time gain access to the buffer of file cache (File).
	CacheBufferSize	File cache (File) memory buffer size (Kb). Default value is 2048 Kb (2 Mb).
	CacheCustom	Custom cache type. Used with CacheType and CacheCustomArgs.
	CacheCustomArgs	Array of additional parameters for initialization custom cache. Used with CacheType and CacheCustom.
	CacheDuration	Duration of storage in the cache entry (in seconds).
	CacheName	Name of the key cache entry (optional).
	CachePath	The path of storage a cache files. Using only with CacheType = File.
	CacheType	Cache type. Default value is Auto, unless otherwise specified in the parameter NeData:Sql:CacheType of configuration files.
	CommandText	The text of the SQL statements or stored procedure name, you must perform.
	CommandTimeout	Timeout command (in seconds). The default value is 30 seconds.
	CommandType	Command type for CommandText. Default value Auto, unless otherwise specified in the parameter NeData:Sql:CommandType of configuration file.
	ConnectionMode	Connection mode. Default value Auto, unless otherwise specified in the parameter NeData:Sql:ConnectionMode of configuration file.
	ConnectionString	Connection string. Default value: LocalSqlServer.
	ConnectionTimeout	Timeout connecting to the server (in seconds) before an exception. Default value: zero - used the server configuration.
	LastQueryResultsFromCache	Is True, if the last query result was obtained from the cache.
	LastQueryTime	Elapsed time of the last query to the database.
	Parameters	Collection of query parameters.

Top

Remarks

By default, the class initialization parameters are used in the configuration file. For web-projects - web.config; for applications Windows - app.config.

Following parameters are supported:

NeData:Sql:ConnectionMode
NeData:Sql:CommandType
NeData:Sql:CacheType
NeData:Sql:CacheDuration (not recommended)
NeData:Sql:CachePath
NeData:Sql:CacheBinder
NeData:Sql:CacheBufferSize
NeData:Sql:CacheBufferAccessTimeout (for example: 0.00:00:00.500 - 500 ms)
NeData:Sql:CacheAccessTimeout (for example: 0.00:00:00.500 - 500 ms)
NeData:Sql:CacheCustom
NeData:Sql:CacheCustomArgs

For Windows applications, the parameter names should be recorded without the colons (case sensitive). For example:

NeDataSqlCacheTyp
NeDataSqlAutoTrimmingStrings
NeDataSqlCachePath
etc.

The default connection string is LocalSqlServer. To use the LocalSqlServer in web applications with your database, you need remove default connection string and add your connection string value:

XML

Copy to Clipboard Print

<remove name="LocalSqlServer"/> 
<add 
  name="LocalSqlServer" 
  connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=example;Trusted_Connection=True;" 
/>

<remove name="LocalSqlServer"/> 
<add 
  name="LocalSqlServer" 
  connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=example;Trusted_Connection=True;" 
/>

Examples

The following example creates an instance of the SqlClient class using connection string by default (LocalSqlServer).

C++

Copy to Clipboard Print

using (SqlClient client = new SqlClient())
{
  DataRow row = client.GetRow("SELECT * FROM table WHERE id = 1000");
  if(row == null)
  {
    Console.WriteLine("No results...");
  }
  else
  {
    Console.WriteLine("id = {0}", row["id"]);
  }
}

using (SqlClient client = new SqlClient())
{
  DataRow row = client.GetRow("SELECT * FROM table WHERE id = 1000");
  if(row == null)
  {
    Console.WriteLine("No results...");
  }
  else
  {
    Console.WriteLine("id = {0}", row["id"]);
  }
}

Using client As New SqlClient()
  Dim row As DataRow = client.GetRow("SELECT * FROM table WHERE id = 1000")
  If row Is Nothing Then
    Console.WriteLine("No results...")
  Else
    Console.WriteLine("id = {0}", row("id"))
  End If 
End Using

Using client As New SqlClient()
  Dim row As DataRow = client.GetRow("SELECT * FROM table WHERE id = 1000")
  If row Is Nothing Then
    Console.WriteLine("No results...")
  Else
    Console.WriteLine("id = {0}", row("id"))
  End If 
End Using

The following example creates an instance of the SqlClient class with an explicit connection string.

C++

Copy to Clipboard Print

using (SqlClient client = new SqlClient(@"Data Source=.\SQLEXPRESS;Initial Catalog=example;Trusted_Connection=True;"))
{
  DataRow row = client.GetRow("SELECT * FROM table WHERE id = 1000");
  if(row == null)
  {
    Console.WriteLine("No results...");
  }
  else
  {
    Console.WriteLine("id = {0}", row["id"]);
  }
}

using (SqlClient client = new SqlClient(@"Data Source=.\SQLEXPRESS;Initial Catalog=example;Trusted_Connection=True;"))
{
  DataRow row = client.GetRow("SELECT * FROM table WHERE id = 1000");
  if(row == null)
  {
    Console.WriteLine("No results...");
  }
  else
  {
    Console.WriteLine("id = {0}", row["id"]);
  }
}

Using client As New SqlClient("Data Source=.\SQLEXPRESS;Initial Catalog=example;Trusted_Connection=True;")
  Dim row As DataRow = client.GetRow("SELECT * FROM table WHERE id = 1000")
  If row Is Nothing Then
    Console.WriteLine("No results...")
  Else
    Console.WriteLine("id = {0}", row("id"))
  End If 
End Using

Using client As New SqlClient("Data Source=.\SQLEXPRESS;Initial Catalog=example;Trusted_Connection=True;")
  Dim row As DataRow = client.GetRow("SELECT * FROM table WHERE id = 1000")
  If row Is Nothing Then
    Console.WriteLine("No results...")
  Else
    Console.WriteLine("id = {0}", row("id"))
  End If 
End Using

The following example creates an instance of the SqlClient class with the parameter name in the configuration file, containing the connection string.

C++

Copy to Clipboard Print

using (SqlClient client = new SqlClient("MyConnectionString"))
{
  DataRow row = client.GetRow("SELECT * FROM table WHERE id = 1000");
  if(row == null)
  {
    Console.WriteLine("No results...");
  }
  else
  {
    Console.WriteLine("id = {0}", row["id"]);
  }
}

using (SqlClient client = new SqlClient("MyConnectionString"))
{
  DataRow row = client.GetRow("SELECT * FROM table WHERE id = 1000");
  if(row == null)
  {
    Console.WriteLine("No results...");
  }
  else
  {
    Console.WriteLine("id = {0}", row["id"]);
  }
}

Using client As New SqlClient("MyConnectionString")
  Dim row As DataRow = client.GetRow("SELECT * FROM table WHERE id = 1000")
  If row Is Nothing Then
    Console.WriteLine("No results...")
  Else
    Console.WriteLine("id = {0}", row("id"))
  End If 
End Using

Using client As New SqlClient("MyConnectionString")
  Dim row As DataRow = client.GetRow("SELECT * FROM table WHERE id = 1000")
  If row Is Nothing Then
    Console.WriteLine("No results...")
  Else
    Console.WriteLine("id = {0}", row("id"))
  End If 
End Using

The following example demonstrates the use of the built-in caching mechanism.

C++

Copy to Clipboard Print

using (SqlClient client = new SqlClient())
{
  // cache the query results via file cache for 30 sec.
  client.CacheDuration = 30;
  client.CacheType = CachingType.File;
  // query building
  client.CommandText = "SELECT * FROM table";
  // query execution
  DataTable table = client.GetTable();
  // output
  Console.WriteLine("Total rows: {0}", table.Rows.Count);
  Console.WriteLine("Query execution time: {0}", client.LastQueryTime);
  Console.WriteLine("From cache: {0}", client.LastQueryResultsFromCache);
  // repeated execution of the same query
  table = client.GetTable();
  Console.WriteLine("Total rows: {0}", table.Rows.Count);
  Console.WriteLine("Query execution time: {0}", client.LastQueryTime);
  Console.WriteLine("From cache: {0}", client.LastQueryResultsFromCache);
}

using (SqlClient client = new SqlClient())
{
  // cache the query results via file cache for 30 sec.
  client.CacheDuration = 30;
  client.CacheType = CachingType.File;
  // query building
  client.CommandText = "SELECT * FROM table";
  // query execution
  DataTable table = client.GetTable();
  // output
  Console.WriteLine("Total rows: {0}", table.Rows.Count);
  Console.WriteLine("Query execution time: {0}", client.LastQueryTime);
  Console.WriteLine("From cache: {0}", client.LastQueryResultsFromCache);
  // repeated execution of the same query
  table = client.GetTable();
  Console.WriteLine("Total rows: {0}", table.Rows.Count);
  Console.WriteLine("Query execution time: {0}", client.LastQueryTime);
  Console.WriteLine("From cache: {0}", client.LastQueryResultsFromCache);
}

Using client As New SqlClient()
  ' cache the query results via file cache for 30 sec.
  client.CacheDuration = 30
  client.CacheType = CachingType.File
  ' query building
  client.CommandText = "SELECT * FROM test" 
  ' query execution 
  Dim table As DataTable = client.GetTable()
  ' output
  Console.WriteLine("Total rows: {0}", table.Rows.Count)
  Console.WriteLine("Query execution time: {0}", client.LastQueryTime)
  Console.WriteLine("From cache: {0}", client.LastQueryResultsFromCache)
  ' repeated execution of the same query
  table = client.GetTable()
  Console.WriteLine("Total rows: {0}", table.Rows.Count)
  Console.WriteLine("Query execution time: {0}", client.LastQueryTime)
  Console.WriteLine("From cache: {0}", client.LastQueryResultsFromCache)
End Using

Using client As New SqlClient()
  ' cache the query results via file cache for 30 sec.
  client.CacheDuration = 30
  client.CacheType = CachingType.File
  ' query building
  client.CommandText = "SELECT * FROM test" 
  ' query execution 
  Dim table As DataTable = client.GetTable()
  ' output
  Console.WriteLine("Total rows: {0}", table.Rows.Count)
  Console.WriteLine("Query execution time: {0}", client.LastQueryTime)
  Console.WriteLine("From cache: {0}", client.LastQueryResultsFromCache)
  ' repeated execution of the same query
  table = client.GetTable()
  Console.WriteLine("Total rows: {0}", table.Rows.Count)
  Console.WriteLine("Query execution time: {0}", client.LastQueryTime)
  Console.WriteLine("From cache: {0}", client.LastQueryResultsFromCache)
End Using