Thursday, 12 September 2013
DataLayer
namespace DataAccessLayer
{
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Text;
///
/// Represent the Description of the DataAccess.
///
public sealed class DataAccessProvider
{
#region Variable declaration.
///
/// Connection string.
///
private static string connectionString;
///
/// Connectionn Object.
///
private static SqlConnection conn;
///
/// Command Object.
///
private static SqlCommand command;
///
/// Adapte Object.
///
private static SqlDataAdapter adapter;
///
/// Transaction Object.
///
private static SqlTransaction transaction;
///
/// Simple Object.
///
private static object result;
#endregion
///
/// Prevents a default instance of the
private DataAccessProvider()
{
}
///
/// Gets or sets connection string.
///
/// Connection string as string
public static string ConnectionString
{
get
{
if (String.IsNullOrEmpty(connectionString))
{
try
{
connectionString = System.Configuration.ConfigurationManager.AppSettings["connectionString"].ToString();
}
catch (NullReferenceException)
{
connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ToString();
}
}
else if (String.IsNullOrEmpty(connectionString))
{
AppSettingsReader objConfig = new AppSettingsReader();
connectionString = objConfig.GetValue("connectionString", typeof(string)).ToString();
}
return connectionString;
}
set
{
connectionString = value;
}
}
///
/// Return the Collection of the record against Query.
///
/// The object of the Query.
/// The dataset of the Result set.
public static DataSet GetDataSet(Query query)
{
try
{
PopulatCommandObject(ref conn, ref command, query.Name, query.Parameters);
adapter = new SqlDataAdapter(command);
if (query.DataSet == null)
{
query.DataSet = new DataSet();
query.DataSet.Locale = CultureInfo.InvariantCulture;
}
conn.Open();
if (!String.IsNullOrEmpty(query.DataSetTableName))
{
if ((query.DataSet.Tables.Count == 0) || (query.DataSet.Tables[query.DataSetTableName] == null))
{
query.DataSet.Tables.Add(query.DataSetTableName);
}
adapter.Fill(query.DataSet, query.DataSetTableName);
}
else
{
adapter.Fill(query.DataSet);
}
}
catch (MyException)
{
throw;
}
catch (Exception)
{
throw;
}
finally
{
if (conn != null)
{
conn.Close();
if (adapter != null)
{
adapter.Dispose();
}
if (command != null)
{
command.Dispose();
}
conn.Dispose();
conn = null;
connectionString = string.Empty;
}
}
return query.DataSet;
}
///
/// Return the Collecton of records against Query.
///
/// The object the query.
/// The datatable of recordset.
public static DataTable GetDataTable(Query query)
{
try
{
PopulatCommandObject(ref conn, ref command, query.Name, query.Parameters);
adapter = new SqlDataAdapter(command);
if (query.DataTable == null)
{
query.DataTable = new DataTable();
query.DataTable.Locale = CultureInfo.InvariantCulture;
}
conn.Open();
adapter.Fill(query.DataTable);
}
catch (MyException)
{
throw;
}
catch (Exception)
{
throw;
}
finally
{
if (conn != null)
{
conn.Close();
if (adapter != null)
{
adapter.Dispose();
}
if (command != null)
{
command.Dispose();
}
conn.Dispose();
conn = null;
connectionString = string.Empty;
}
}
return query.DataTable;
}
///
/// Gets the value of specified colum in its native format.
///
/// The object of the Query.
/// The object of the column in its native format.
public static object GetValues(Query query)
{
try
{
PopulatCommandObject(ref conn, ref command, query.Name, query.Parameters);
conn.Open();
SqlDataReader read = command.ExecuteReader();
while (read.Read())
{
query.ReturnValue = read.GetValue(0);
}
read.Close();
read = null;
}
catch (MyException)
{
throw;
}
catch (Exception)
{
throw;
}
finally
{
if (conn != null)
{
conn.Close();
if (adapter != null)
{
adapter.Dispose();
}
if (command != null)
{
command.Dispose();
}
conn.Dispose();
conn = null;
connectionString = string.Empty;
}
}
return query.ReturnValue;
}
///
/// Excute the inset and update Query.
///
/// The object of the Query.
/// The first column of first rows in the result set.
public static object ExecuteScalar(Query query)
{
try
{
PopulatCommandObject(ref conn, ref command, query.Name, query.Parameters);
conn.Open();
transaction = conn.BeginTransaction();
command.Transaction = transaction;
result = command.ExecuteScalar();
PopulateOutPutParamValues(query.Parameters, command);
transaction.Commit();
return result;
}
catch (MyException)
{
throw;
}
catch (Exception)
{
if (transaction != null)
{
transaction.Rollback();
}
throw;
}
finally
{
if (conn != null)
{
conn.Close();
if (command != null)
{
command.Dispose();
}
if (transaction != null)
{
transaction.Dispose();
transaction = null;
}
conn.Dispose();
conn = null;
connectionString = string.Empty;
}
}
}
///
/// Excute the inset and update Query.
///
/// The object of the Query.
/// The Number of rows affected.
public static int ExecuteNonQuery(Query query)
{
int result = 0;
try
{
PopulatCommandObject(ref conn, ref command, query.Name, query.Parameters);
conn.Open();
transaction = conn.BeginTransaction();
command.Transaction = transaction;
result = command.ExecuteNonQuery();
PopulateOutPutParamValues(query.Parameters, command);
transaction.Commit();
return result;
}
catch (MyException)
{
throw;
}
catch (Exception)
{
if (transaction != null)
{
transaction.Rollback();
}
throw;
}
finally
{
if (conn != null)
{
conn.Close();
if (command != null)
{
command.Dispose();
}
if (transaction != null)
{
transaction.Dispose();
transaction = null;
}
conn.Dispose();
conn = null;
connectionString = string.Empty;
}
}
}
///
/// Excute the set of query string againt connection.
///
/// set of query string as arraylist
/// The Number of row affected.
public static int ExecuteNonQuery(ArrayList arrayQuery)
{
int result = 0;
SqlConnection conn = null;
SqlCommand command = null;
Query query;
// start the Ienum collection.
if (conn == null)
{
conn = new SqlConnection(ConnectionString);
}
conn.Open();
if (command == null)
{
command = new SqlCommand();
transaction = conn.BeginTransaction();
command.Transaction = transaction;
}
try
{
IEnumerator objenum = arrayQuery.GetEnumerator();
objenum.MoveNext();
do
{
query = (Query)objenum.Current;
PopulatCommandObject(conn, command, query.Name, query.Parameters);
result = command.ExecuteNonQuery();
PopulateOutPutParamValues(command, query);
command.Parameters.Clear();
}
while (objenum.MoveNext());
transaction.Commit();
}
catch (MyException)
{
throw;
}
catch (Exception)
{
if (transaction != null)
{
transaction.Rollback();
}
throw;
}
finally
{
if (conn != null)
{
conn.Close();
if (command != null)
{
command.Dispose();
}
if (transaction != null)
{
transaction.Dispose();
transaction = null;
}
conn.Dispose();
conn = null;
connectionString = string.Empty;
}
}
return result;
}
///
/// Build the command object.
///
/// The connection object reference.
/// The command object Reference.
/// The Command Text.
/// The parameter name as parameter list.
private static void PopulatCommandObject(ref SqlConnection conn, ref SqlCommand command, string queryName, List parameters)
{
try
{
if (conn == null)
{
conn = new SqlConnection(ConnectionString);
}
command = new SqlCommand(queryName, conn);
command.CommandType = CommandType.StoredProcedure;
foreach (Parameter parameter in parameters)
{
SqlParameter param = new SqlParameter();
param.ParameterName = "@" + parameter.ParameterName;
param.DbType = parameter.DbType;
param.Value = parameter.Value;
param.Direction = parameter.Direction;
command.Parameters.Add(param);
// command.Parameters.AddWithValue("@" + parameter.ParameterName, parameter.DbType.ToString()).Value = parameter.Value;
}
}
catch (MyException)
{
throw;
}
catch (Exception)
{
throw;
}
}
///
/// Build the command object.
///
/// The connection object Reference.
/// The command object Reference.
/// The command text.
/// The parameter name as parameter list.
private static void PopulatCommandObject(SqlConnection conn, SqlCommand command, string queryName, List parameters)
{
try
{
command.Connection = conn;
command.CommandText = queryName;
command.CommandType = CommandType.StoredProcedure;
foreach (Parameter parameter in parameters)
{
SqlParameter param = new SqlParameter();
param.ParameterName = "@" + parameter.ParameterName;
param.DbType = parameter.DbType;
param.Value = parameter.Value;
param.Direction = parameter.Direction;
command.Parameters.Add(param);
}
}
catch (MyException)
{
throw;
}
catch (Exception)
{
throw;
}
}
///
/// Build output parameter values.
///
/// the object of IDbCommand interface.
/// the object of the query.
private static void PopulateOutPutParamValues(IDbCommand command, Query query)
{
query.Parameters.Clear(); // only return output and return type param from Storedprocedure.
foreach (IDataParameter param in command.Parameters)
{
query.Parameters.Add(new Parameter(param.ParameterName, param.DbType, param.Value, param.Direction));
}
}
///
/// Build ouput parameter.
///
/// the parameter of the parameter list.
/// the object of the IDbcommand interface.
private static void PopulateOutPutParamValues(List parameters, IDbCommand command)
{
parameters.Clear(); // only return output and return type param from Storedprocedure.
foreach (IDataParameter param in command.Parameters)
{
parameters.Add(new Parameter(param.ParameterName, param.DbType, (command.Parameters[param.ParameterName] as IDataParameter).Value, param.Direction));
}
}
}
}
Friday, 1 March 2013
how to make single radio button selection in gridview using javascript
By: Suresh Dasari Dec 20, 2010
Categories: Asp.net, Gridview, Javascript Introduction
Here I will explain how to make single radio button selection in gridview using javascript.
Description
In my application I have designed gridview with radio button after completion of all the functionality if try to select radio button I am able to select all the radio buttons in gridview that is like this
My requirement is to make single radio button selection in gridview for that I have written JavaScript function to maintain single radio button selection in gridview. To implement this first design your aspx page like this
Grdview with Arraylist
In code behind bind your gridview and check it
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGridviewData();
}
}
protected void BindGridviewData()
{
SqlConnection con = new SqlConnection("Data Source=MYSystem;Initial Catalog=MySamplesDB;Integrated Security=True");
con.Open();
SqlCommand cmd = new SqlCommand("select UserId,UserName,FirstName,LastName,Location from User_Information", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
gvdata.DataSource = ds;
gvdata.DataBind();
con.Close();
}
Demo
Thursday, 28 February 2013
SQL SERVER – Find First Day of Any Month – Current Previous Next
--FirstDay_PreviousMonth
SELECT DATEADD(mm, DATEDIFF(m,0,getdate())-1,0)
--FirstDay_CurrentMonth
SELECT DATEADD(mm, DATEDIFF(m,0,getdate()),0)
--FirstDay_NextMonth
SELECT DATEADD(mm, DATEDIFF(m,0,getdate())+1,0)
SQL SERVER – Find Last Day of Any Month – Current Previous Next
----Last Day of Previous Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
LastDay_PreviousMonth
----Last Day of Current Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
LastDay_CurrentMonth
----Last Day of Next Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))
Tuesday, 26 February 2013
Friday, 15 February 2013
Table rows and column count in sql server query
CREATE TABLE #temp
(
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,a.row_count,COUNT(*) AS col_count FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count
DROP TABLE #temp
SQL Server database details
how many table and rows to be display
DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128), [RowCount] INT)
INSERT INTO @TableRowCounts ([TableName], [RowCount])
EXEC sp_MSforeachtable 'SELECT ''?'' [TableName], COUNT(*) [RowCount] FROM ?'
SELECT [TableName], [RowCount]
FROM @TableRowCounts
ORDER BY [TableName]
Subscribe to:
Posts (Atom)