I started a thread on the asp.net forums a while back wanting to share with the others a easy way to connect to SQL. Since then I perfected quite a bit the code and made it a class.cs file, that can be up and running in a few minutes.
Connecting to SQL using the design view in visual studio is quite easy and straight forward. I found to be somewhat limited though. Having a class that you can invoke in all your application and in other classes is the works.
Here I demonstrate how to use C# in a class file to connect to SQL, and INSERT, SELECT or UPDATE an existing SQL database. In this example I will be using the Microsoft SQL Express Server. Also the connection string in this example is using integrated SQL security; if you need to connect to an external server or any other king of SQL server go to http://connectionstrings.com to get the replacement connection string code.
First off unlike asp.net insert you don’t use a connection string in the web.config, you can but for sake of simplicity it does not. If that’s your goal, the class is commented on hot to do that.
Add an .cs file to your solution and paste the following code. Be weary of the namespace either rename or remove it.
using System;
using System.Configuration;
namespace ToolBox
{
/// <summary>
/// SqlConnection class connects to SQL and when
/// disposed it closes the connection to SQL.
/// </summary>
public sealed class SqlConnection : IDisposable
{
#region Properties
public System.Data.SqlClient.SqlConnection Connection { get; set; }
public System.Data.SqlClient.SqlDataReader DataReader { get; set; }
public System.Data.SqlClient.SqlCommand Command { get; set; }
#endregion
#region Constructors
/// <summary>
/// Instanciate SqlConnection and open connection to SQL Server
/// </summary>
public SqlConnection()
{
//New up the SqlConnection class
if (Connection == null)
Connection = new System.Data.SqlClient.SqlConnection();
// Assign the connection string
//
// It can be also saved in the .config file
//<connectionStrings>
//<add name="MyConnectionString"
// connectionString="server=SERVERNAME;database=DATABASENAME;Trusted_Connection=True;"
// providerName="System.Data.SqlClient" />
//</connectionStrings>
//Uncomment the following line to get it form the configuration file
//Connection.ConnectionString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
Connection.ConnectionString = "server=SERVERNAME;database=DATABASENAME;Trusted_Connection=True;";
//Open the connection
Open();
}
#endregion
/// <summary>
/// Opens a connection to SQL
/// </summary>
/// <returns>Connected System.Data.SqlClient.SqlConnection</returns>
private void Open()
{
//Attempt to connect and catch exception
if (Connection.State != System.Data.ConnectionState.Open)
try
{
//Try to connect with a short timeout and pooling
Connection.ConnectionString =
String.Format(
"Min Pool Size=1;Max Pool Size=10;Connect Timeout=4;{0}"
, Connection.ConnectionString);
Connection.Open();
}
catch
{
//If connection failed, try again without pooling and a linger timeout
if (Connection.State != System.Data.ConnectionState.Closed)
Connection.Close();
Connection.ConnectionString =
String.Format(
"Pooling=false;Connect Timeout=45;{0}"
, Connection.ConnectionString);
Connection.Open();
}
}
#region IDisposable Members
// Track whether Dispose has been called.
private Boolean disposed = false;
/// <summary>
/// Implement IDisposable.
/// Do not make this method virtual.
/// A derived class should not be able to override this method.
/// </summary>
public void Dispose()
{
Dispose(true);
// This object will be cleaned up by the Dispose method.
// Therefore, you should call GC.SupressFinalize to
// take this object off the finalization queue
// and prevent finalization code for this object
// from executing a second time.
GC.SuppressFinalize(this);
}
/// <summary>
/// Dispose(bool disposing) executes in two distinct scenarios.
/// If disposing equals true, the method has been called directly
/// or indirectly by a user's code. Managed and unmanaged resources
/// can be disposed.
/// If disposing equals false, the method has been called by the
/// runtime from inside the finalizer and you should not reference
/// other objects. Only unmanaged resources can be disposed.
/// </summary>
/// <param name="disposing"></param>
private void Dispose(bool disposing)
{
// Check to see if Dispose has already been called.
if (!this.disposed)
{
lock (this)
{
// If disposing equals true, dispose all managed
// and unmanaged resources.
if (disposing)
{
Connection.Dispose();
if (DataReader != null)
DataReader.Dispose();
if (Command != null)
Command.Dispose();
}
// Note disposing has been done.
disposed = true;
}
}
}
/// <summary>
/// Use C# destructor syntax for finalization code.
/// This destructor will run only if the Dispose method
/// does not get called.
/// It gives your base class the opportunity to finalize.
/// Do not provide destructors in types derived from this class.
/// </summary>
~SqlConnection()
{
Dispose(false);
}
#endregion
}
}
IDisposable Interface
Since the class contains property that implement IDisposable interface, the rule is that the class also has to implement it. IDisposable offer 2 very nice way to use the calss:
1. With a using statement:
using (SqlConnection connection = new SqlConnection())
{
//Do something
}
2. With try / finally statement:
SqlConnection connection = new SqlConnection();
try
{
//Do something
}
finally
{
connection.Dispose();
}
Usage
Here are some examples on how to use the class, in the examples I prefer the using statement.
SELECT:
using (SqlConnection connection = new SqlConnection())
{
connection.Command = connection.Connection.CreateCommand();
connection.Command.CommandType = System.Data.CommandType.Text;
connection.Command.Parameters.Add("@Name", System.Data.SqlDbType.NVarChar);
connection.Command.Parameters["@Name"].Value = "Simon";
connection.Command.CommandText = @"SELECT *FORM [dbo].[table1] WHERE [Name] = @Name";
connection.DataReader = connection.Command.ExecuteReader();
while (connection.DataReader.Read())
{
//Do something
}
}
INSERT:
using (SqlConnection connection = new SqlConnection())
{
connection.Command = connection.Connection.CreateCommand();
connection.Command.CommandType = System.Data.CommandType.Text;
connection.Command.Parameters.Add("@Value", System.Data.SqlDbType.NVarChar);
connection.Command.Parameters["@Value"].Value = "Simon";
connection.Command.CommandText = @"INSERT INTO [dbo].[table1] VALUES (@Name)";
connection.Command.ExecuteNonQuery();
}
Subscribe