- From the menu select File --> New Project.
- Give a name for your project and save.
- Open the Web.config file and add the following lines to create the connection.
[code]
<connectionStrings>
<add name="AppConString"
connectionString="Data Source=[Server name];
Initial Catalog=[Database name];
User ID=[Database username];
Password=[Database password]"
providerName="System.Data.SqlClient" />
</connectionStrings>
[/code]
- Create a new folder named "Data" and create a class within it named "DBCon".
- Add the following lines at the top of the class.
[code]using System.Data;
using System.Data.SqlClient;
using System.Configuration;[/code]
- Add the methods as follows to open and close connection.
[code]
public class DBCon
{
public SqlConnection con;
public void ReadyConnection()
{
try
{
con = new SqlConnection();
con.ConnectionString = ConfigurationManager.ConnectionStrings["AppConString"].ConnectionString;
if (con.State != ConnectionState.Open)
con.Open();
}
catch (Exception ex)
{
throw ex;
}
}
public void CloseConnection()
{
try
{
if (con.State == ConnectionState.Open)
con.Close();
}
catch (Exception ex)
{
throw ex;
}
}
}
[/code]
- Add the following methods to deal with the database.
[code]
public DataTable GetDataTableFromSQL(string sSQL)
{
DataTable dataTable = new DataTable();
try
{
ReadyConnection();
SqlCommand cmd = new SqlCommand(sSQL, con);
cmd.CommandTimeout = 0;
dataTable.Load(cmd.ExecuteReader());
return dataTable;
}
catch (Exception ex)
{
throw ex;
}
finally
{
CloseConnection();
}
}
public DataTable GetDataTableFromSp(string spName, SqlCommand cmd)
{
try
{
DataTable dt = new DataTable();
ReadyConnection();
cmd.CommandText = spName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
cmd.CommandTimeout = 0;
dt.Load(cmd.ExecuteReader());
return dt;
}
catch (Exception ex)
{
throw ex;
}
finally
{
CloseConnection();
}
}
public bool ExecuteNonQueryFromSQL(string sSQL)
{
try
{
ReadyConnection();
SqlCommand cmd = new SqlCommand(sSQL, con);
cmd.CommandTimeout = 0;
int count = cmd.ExecuteNonQuery();
if (count > 0)
return true;
else
return false;
}
catch (Exception ex)
{
throw ex;
}
finally
{
CloseConnection();
}
}
public bool ExecuteNonQueryFromSp(string spName, SqlCommand cmd)
{
try
{
ReadyConnection();
cmd.CommandText = spName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
int count = cmd.ExecuteNonQuery();
if (count > 0)
return true;
else
return false;
}
catch (Exception ex)
{
throw ex;
}
finally
{
CloseConnection();
}
}
[/code]
- Then by calling these methods we can execute the database queries as needed.
No comments:
Post a Comment