using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Globalization;
//Namespace
standard is COMPANYNAME.PROJECTNAME.MODULENAME
//If
we want use this DAL project in any other project, use connection string name
as ConStr.
namespace GRMI.DiverseFund.SqlDBDiverseFundDataAccessLayer
{
public class UseDatabaseElements
{
public static SqlConnection GetConnection()
{
SqlConnection con = new SqlConnection();
con.ConnectionString = ConfigurationManager.ConnectionStrings[""].ConnectionString.ToString();
return con;
}
//Below
Method is used to Insert update records into the Databse.
public static int ExecuteInsertUpdateQuery(string procName, SqlParameter[] parameter)
{
int i = 0;
SqlCommand cmd = new SqlCommand();
cmd.Connection = GetConnection();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procName;
foreach (SqlParameter p1 in parameter)
{
cmd.Parameters.Add(p1);
}
try
{
cmd.Connection.Open();
i = cmd.ExecuteNonQuery();
}
catch (ArgumentNullException ex)
{
throw new ArgumentNullException(ex.Message.ToString());
}
finally
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
}
return i;
}
//Below
method to get single value from database.
public static string FetchSingleValue(string procName, SqlParameter[] parameter)
{
object SingleValue = null;
SqlCommand cmd = new SqlCommand();
cmd.Connection = GetConnection();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procName;
foreach (SqlParameter p1 in parameter)
{
cmd.Parameters.Add(p1);
}
try
{
cmd.Connection.Open();
SingleValue =
cmd.ExecuteScalar();
}
catch (ArgumentNullException ex)
{
throw new ArgumentNullException(ex.Message.ToString());
}
finally
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
}
if (SingleValue == null)
return "";
else
return Convert.ToString(SingleValue);
}
//Below
method to get single value from database.
public static string FetchSingleValue(string procName)
{
object SingleValue = null;
SqlCommand cmd = new SqlCommand();
cmd.Connection = GetConnection();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procName;
try
{
cmd.Connection.Open();
SingleValue =
cmd.ExecuteScalar();
}
catch (ArgumentNullException ex)
{
throw new ArgumentNullException(ex.Message.ToString());
}
finally
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
}
if (SingleValue == null)
return "";
else
return Convert.ToString(SingleValue);
}
//Below
method is to get table records.
public static DataTable FetchTable(string procName, SqlParameter[] parameter)
{
DataTable dt = new DataTable();
dt.Locale = CultureInfo.InvariantCulture;
SqlCommand cmd = new SqlCommand();
cmd.Connection = GetConnection();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procName;
SqlDataAdapter da = new SqlDataAdapter(cmd);
foreach (SqlParameter p1 in parameter)
{
cmd.Parameters.Add(p1);
}
try
{
cmd.Connection.Open();
da.Fill(dt);
}
catch (ArgumentNullException ex)
{
throw new ArgumentNullException(ex.Message.ToString());
}
finally
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
}
return dt;
}
//Below
method is to get table records
public static DataTable FetchTable(string procName)
{
DataTable dt = new DataTable();
dt.Locale = CultureInfo.InvariantCulture;
SqlCommand cmd = new SqlCommand();
cmd.Connection = GetConnection();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procName;
SqlDataAdapter da = new SqlDataAdapter(cmd);
try
{
cmd.Connection.Open();
da.Fill(dt);
}
catch (ArgumentNullException ex)
{
throw new ArgumentNullException(ex.Message.ToString());
}
finally
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
}
return dt;
}
//Below
method is to get Multiple table data.
public static DataSet FetchMultipleTable(string procName, SqlParameter[] parameter)
{
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
SqlCommand cmd = new SqlCommand();
cmd.Connection = GetConnection();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procName;
SqlDataAdapter da = new SqlDataAdapter(cmd);
foreach (SqlParameter p1 in parameter)
{
cmd.Parameters.Add(p1);
}
try
{
cmd.Connection.Open();
da.Fill(ds);
}
catch (ArgumentNullException ex)
{
throw new ArgumentNullException(ex.Message.ToString());
}
finally
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
}
return ds;
}
//Below
method is to get Multiple table data.
public static DataSet FetchMultipleTable(string procName)
{
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
SqlCommand cmd = new SqlCommand();
cmd.Connection = GetConnection();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = procName;
SqlDataAdapter da = new SqlDataAdapter(cmd);
try
{
cmd.Connection.Open();
da.Fill(ds);
}
catch (ArgumentNullException ex)
{
throw new ArgumentNullException(ex.Message.ToString());
}
finally
{
if (cmd.Connection.State == ConnectionState.Open)
{
cmd.Connection.Close();
}
}
return ds;
}
}
}
No comments:
Post a Comment