SqlHelper Class

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

///

/// Summary description for SQLHelper
///

public class SQLHelper
{
SqlConnection conn;
DataTable t;
DataSet ds = new DataSet();
DataSet DsMain = new DataSet();
public SQLHelper()
{
//
// TODO: Add constructor logic here
//
}
#region // Connection string
public static string Connect
{
get { return ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString; }
}
#endregion

#region // Excute Insert Command
public static int excute(string sql,SqlParameter[] par)
{
SqlConnection conn = new SqlConnection(SQLHelper.Connect);
SqlCommand cmd = new SqlCommand(sql);
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
for (int i = 0; i < par.Length; i++)
{
// if (par[i] != null)
{
cmd.Parameters.Add(par[i]);
}
}
conn.Open();
int affected = cmd.ExecuteNonQuery();
conn.Close();
cmd.Dispose();
return affected;
}
#endregion

#region // Fetch The Data
public static DataTable GetData(string sql)
{
SqlDataAdapter adpt = new SqlDataAdapter(sql, SQLHelper.Connect);
DataTable dt = new DataTable();
adpt.Fill(dt);
return dt;
}
#endregion

public static string ExcuteScaler(string sql)
{
SqlConnection conn = new SqlConnection(SQLHelper.Connect);
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
try
{
string dy = (string)cmd.ExecuteScalar();
return dy;
}
catch
{
return "";
}
conn.Close();
}

#region // Count no of Records
public static int CountData(string sql)
{
SqlConnection conn = new SqlConnection(SQLHelper.Connect);
SqlCommand cmd = new SqlCommand(sql,conn);
conn.Open();
int count = (int)cmd.ExecuteScalar();
conn.Close();
return count;
}
#endregion

#region // Excute Query For Delete Update
public static void ExcuteRecord(string sql)
{
SqlConnection conn = new SqlConnection(SQLHelper.Connect);
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
#endregion

public static int AffectedRows(string sql)
{
SqlConnection conn = new SqlConnection(SQLHelper.Connect);
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
try
{
int rs = (int)cmd.ExecuteScalar();
return rs;
}
catch
{
return 0;
}
conn.Close();

}


#region // For Update Query Excuate
public static DataTable selected_update(string sql)
{
SqlDataAdapter adpt = new SqlDataAdapter(sql, SQLHelper.Connect);
DataTable dt = new DataTable();
adpt.Fill(dt);
return dt;
}
#endregion

#region // Fetch Data in Format Like TreeView

public DataTable yet()
{
t = new DataTable("Total_List");
DsMain.Tables.Add(t);
DataColumn c1 = new DataColumn("CategoryID", Type.GetType("System.String"), "");
DataColumn c2 = new DataColumn("CategoryDesc", Type.GetType("System.String"), "");
DataColumn c3 = new DataColumn("Sub ID", Type.GetType("System.String"), "");
DataColumn c4 = new DataColumn("CategoryLevel", Type.GetType("System.String"), "");
DataColumn c5 = new DataColumn("IsActive", Type.GetType("System.Boolean"), "");
t.Columns.Add(c1);
t.Columns.Add(c2);
t.Columns.Add(c3);
t.Columns.Add(c4);
t.Columns.Add(c5);
LoadResults(0);
return DsMain.Tables["Total_List"];
}
int SubID = 0;
private void LoadResults(int inParentID)
{
if (inParentID == 0)
{
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM CMSForumPCCategory Where SubID=0 order by linkorder asc" , SQLHelper.Connect);
conn = new SqlConnection(SQLHelper.Connect);
conn.Open();
da.Fill(ds, "Search_List" + inParentID);
conn.Close();
}
else
{
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM CMSForumPCCategory Where SubID = " + inParentID, SQLHelper.Connect);
//SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM CMSForumPCCategory Where SubID = " + inParentID, SQLHelper.Connect);
conn.Open();
da.Fill(ds, "Search_List" + inParentID);
conn.Close();
}
DataTable ParentTable;
ParentTable = ds.Tables["Search_List" + inParentID];
foreach (DataRow parentrow in ParentTable.Rows)
{
DataRow newRow;
newRow = t.NewRow();

String ID = Convert.ToString(parentrow.ItemArray[0]);
newRow["CategoryID"] = ID;

String CategoryLevel = Convert.ToString(parentrow.ItemArray[4]);
newRow["CategoryLevel"] = CategoryLevel;

Boolean IsActive = Convert.ToBoolean(parentrow.ItemArray[3]);
newRow["IsActive"] = IsActive;

String Name = String.Concat(Convert.ToString(parentrow.ItemArray[1]));
newRow["CategoryDesc"] = (string)Name;

if (inParentID == 0)
{
newRow["Sub ID"] = (string)SubID.ToString();
}
else
{
SubID = Convert.ToInt32(parentrow.ItemArray[2]);
newRow["Sub ID"] = (string)SubID.ToString();
}
t.Rows.Add(newRow);
LoadResults(Convert.ToInt32(parentrow.ItemArray[0]));
}
}
#endregion
}