Create an UserControll Named As Treeviewmenu
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using Commonlogics;
namespace ambonewversion.Controls
{
public partial class Treeviewmenu : System.Web.UI.UserControl
{
DataTable dt;
DataSet ds = new DataSet();
BusinessLayer objcommonbus = new BusinessLayer();
Commonlogics.DataLogic.DataLayer objcommondb = new Commonlogics.DataLogic.DataLayer();
//DataLayer objcommondb = new DataLayer();
protected void Page_Load(object sender, EventArgs e)
{
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.Cache.SetNoStore();
if (Session["UserId"] != null)
{
if (!Page.IsPostBack)
{
bindtree();
}
}
else
{
Response.Redirect("WebLogin.aspx");
}
}
public void bindtree()
{
dt = objcommonbus.get_menu_BL();
InitializeMenu(dt, Int64.Parse(Session["UserId"].ToString()));
}
/*********** Latest ***************/
public void InitializeMenu(DataTable menu, Int64 UserId)
{
//TreeNode child = new TreeNode();
vfconnecttreeMenu.Nodes.Clear();
DataRow[] menuRows = menu.Select("Type='MENU'");
for (int i = 0; i < menuRows.Length; i++)
{
int menuId = int.Parse(menuRows[i]["Id"].ToString());
if (!Commonlogics.DataLogic.DataLayer.IsViewAllowedModule(menuId, UserId))
continue;
string menuName = menuRows[i]["DisplayName"].ToString();
// Add this menu option
TreeNode root = new TreeNode(menuName);
//MenuItem menuItem = new MenuItem(menuName);
DataRow[] subMenus = menu.Select("ModuleId=" + menuId);
// Initialize all the submenus
for (int j = 0; j < subMenus.Length; j++)
{
int subMenuId = int.Parse(subMenus[j]["Id"].ToString());
if (!Commonlogics.DataLogic.DataLayer.IsViewAllowed(subMenuId, UserId))
continue;
string subMenuName = subMenus[j]["DisplayName"].ToString();
string url = subMenus[j]["Url"].ToString();
TreeNode child = new TreeNode(subMenuName);
//MenuItem subMenuItem = new MenuItem(subMenuName);
// child.NavigateUrl = "../" + url;
root.ChildNodes.Add(child);
// menuItem.ChildItems.Add(subMenuItem);
}
vfconnecttreeMenu.Nodes.Add(root);
vfconnecttreeMenu.CollapseAll();
//vfconnecttreeMenu.
// vfconnectMenu.Items.Add(menuItem);
}
}
protected void vfconnecttreeMenu_SelectedNodeChanged(object sender, EventArgs e)
{
try
{
//DataTable menu = dt.GetMenu();
DataTable menu=objcommonbus.get_menu_BL();
DataRow[] menuRows = menu.Select("Type='MENU'");
for (int i = 0; i < menuRows.Length; i++)
{
int menuId = int.Parse(menuRows[i]["Id"].ToString());
DataRow[] subMenus = menu.Select("ModuleId=" + menuId);
for (int j = 0; j < subMenus.Length; j++)
{
int subMenuId = int.Parse(subMenus[j]["Id"].ToString());
string subMenuName = subMenus[j]["DisplayName"].ToString();
string url = subMenus[j]["Url"].ToString();
if (vfconnecttreeMenu.SelectedNode.Value.ToUpper() == subMenuName.ToUpper())
{
Session["Flag"] = "True";
string SSLEnabled = ConfigurationManager.AppSettings["SSLEnabled"].ToString();
if (SSLEnabled == "Y")
{
if (!Request.IsSecureConnection)
{
string path = string.Format("https{0}", Request.Url.AbsoluteUri.Substring(4));
Response.Redirect(path, false);
break;
}
}
else
{
Response.Redirect(url, false);
break;
}
}
}
}
}
catch (Exception ex) { }
}
}
}
Commonlogics.DataLogic.DataLayer
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Security.Cryptography;
using System.Collections;
using Commonlogics;
using Commonlogics.DataLogic;
using BlowfishNET;
namespace Commonlogics.DataLogic
{
public class DataLayer
{
DataManager dbobj = new DataManager();
DataTable dt;
public DataRow AdminLogin_DL(BusinessLayer objcommon)
{
SqlParameter[] Params = new SqlParameter[1];
Params[0] = new SqlParameter("@LoginId",objcommon.User_Id);
//SqlCommand cmd = new SqlCommand();
string query = "select distinct users.*,campaign.theam from users inner join usercampaigns on users.id=usercampaigns.userid inner join campaign on campaign.campaignid=usercampaigns.CampaignId where Users.loginId='"+ Params[0].SqlValue.ToString()+"'";
dt=dbobj.SelectQuery(query);
MD5 md5Enc = MD5.Create();
byte[] encodedPassword = md5Enc.ComputeHash(System.Text.ASCIIEncoding.ASCII.GetBytes(objcommon.Password));
foreach (DataRow row in dt.Rows)
{
if (row["LoginId"].ToString().Trim().ToLower().Equals(objcommon.User_Id.Trim().ToLower()))
// check the password now
if (row["Password"].ToString().Equals(DataManager.GetHex(
md5Enc.ComputeHash(encodedPassword))
)
)
return row;
}
return null;
// dbobj.SelectData(query,Params[0]);
}
public DataTable get_menu_DL(BusinessLayer objcommon)
{
//SqlCommand cmd = new SqlCommand();
string query = "Select * from ((Select Id,Name as DisplayName,'MENU' as Type,Sequence,'' as URL,-1 as ModuleId from Module) " +
"union " +
"(Select Id,DisplayName,'SUBMENU' as Type,Sequence,Url as URL,ModuleId from Submodule))menu order by Sequence";
dt = dbobj.SelectQuery(query);
return dt;
// dbobj.SelectData(query,Params[0]);
}
public static bool IsViewAllowed(int submoduleId, Int64 UserId)
{
//return true;
SqlCommand command = new SqlCommand();
command.Parameters.Add(new SqlParameter("@SubModuleId", DbType.Int64)).Value = submoduleId;
command.Parameters.Add(new SqlParameter("@UserId", DbType.Int64)).Value = UserId;
command.CommandText = "SELECT count(*) from UserSubModuleActivityRight Where UserId=@UserId and SubModuleRightId in (Select Id From SubModuleActivityRight Where SubModuleId=@SubModuleId and ActivityId=1)";
Object obj = DataManager.ExecuteScalarCommand(command);
int count = int.Parse(obj.ToString());
if (count == 0)
return false;
else
return true;
}
public static bool IsViewAllowedModule(int moduleId, Int64 UserId)
{
//return true;
SqlCommand command = new SqlCommand();
command.Parameters.Add(new SqlParameter("@ModuleId", DbType.Int64)).Value = moduleId;
command.Parameters.Add(new SqlParameter("@UserId", DbType.Int64)).Value = UserId;
command.CommandText = "SELECT count(*) from UserModuleActivityRight Where UserId=@UserId and ModuleRightId in (Select Id From ModuleActivityRight Where ModuleId=@ModuleId and ActivityId=1)";
Object obj = DataManager.ExecuteScalarCommand(command);
int count = int.Parse(obj.ToString());
if (count == 0)
return false;
else
return true;
}
public static bool IsEditAllowed(int submoduleId, Int32 UserId)
{
//return true;
SqlCommand command = new SqlCommand();
command.Parameters.Add(new SqlParameter("@SubModuleId", DbType.Int64)).Value = submoduleId;
command.Parameters.Add(new SqlParameter("@UserId", DbType.Int64)).Value = UserId;
command.CommandText = "SELECT count(*) from UserSubModuleActivityRight Where UserId=@UserId and SubModuleRightId in (Select Id From SubModuleActivityRight Where SubModuleId=@SubModuleId and ActivityId=3)";
Object obj = DataManager.ExecuteScalarCommand(command);
int count = int.Parse(obj.ToString());
if (count == 0)
return false;
else
return true;
}
public static bool IsDeleteAllowed(int submoduleId, Int32 UserId)
{
//return true;
SqlCommand command = new SqlCommand();
command.Parameters.Add(new SqlParameter("@SubModuleId", DbType.Int64)).Value = submoduleId;
command.Parameters.Add(new SqlParameter("@UserId", DbType.Int64)).Value = UserId;
command.CommandText = "SELECT count(*) from UserSubModuleActivityRight Where UserId=@UserId and SubModuleRightId in (Select Id From SubModuleActivityRight Where SubModuleId=@SubModuleId and ActivityId=4)";
Object obj = DataManager.ExecuteScalarCommand(command);
int count = int.Parse(obj.ToString());
if (count == 0)
return false;
else
return true;
}
public static bool IsAddAllowed(int submoduleId, Int32 UserId)
{
//return true;
SqlCommand command = new SqlCommand();
command.Parameters.Add(new SqlParameter("@SubModuleId", DbType.Int64)).Value = submoduleId;
command.Parameters.Add(new SqlParameter("@UserId", DbType.Int64)).Value = UserId;
command.CommandText = "SELECT count(*) from UserSubModuleActivityRight Where UserId=@UserId and SubModuleRightId in (Select Id From SubModuleActivityRight Where SubModuleId=@SubModuleId and ActivityId=2)";
Object obj = DataManager.ExecuteScalarCommand(command);
int count = int.Parse(obj.ToString());
if (count == 0)
return false;
else
return true;
}
public static string GetHex(byte[] text)
{
char[] hexChars = new char[] { '0', '1', '2', '3', '4', '5', '6', '7',
'8', '9', 'A', 'B', 'C', 'D', 'E', 'F'
};
System.Text.StringBuilder sb = new StringBuilder();
for (int i = 0; i < text.Length; i++)
{
sb.Append(hexChars[text[i] >> 4]);
sb.Append(hexChars[text[i] & 0x0F]);
}
return sb.ToString();
}
public static int GetGMTOffset(Int64 UserId)
{
SqlCommand command = new SqlCommand();
command.Parameters.Add(new SqlParameter("@UserId", DbType.Int64)).Value = UserId;
command.CommandText = "SELECT GMTOffset from TimeZone Where Id in (Select TimeZoneId From Users Where Id=@UserId)";
DataTable table = DataManager.GetData(command);
int gmtOffset = int.Parse(table.Rows[0]["GMTOffset"].ToString());
return gmtOffset;
}
public static int GetGMTOffset(string UserId)
{
SqlCommand command = new SqlCommand();
command.Parameters.Add(new SqlParameter("@UserId", DbType.String)).Value = UserId;
command.CommandText = "SELECT GMTOffset from TimeZone Where Id in (Select TimeZoneId From Users Where LoginId=@UserId)";
DataTable table = DataManager.GetData(command);
int gmtOffset = int.Parse(table.Rows[0]["GMTOffset"].ToString());
return gmtOffset;
}
public static DateTime GetUserDateTime(int gmtOffset)
{
return DateTime.UtcNow.AddMinutes(gmtOffset);
}
public static DateTime ConvertToUserDateTime(DateTime dt, int gmtOffset)
{
long tickdifference = DateTime.Now.Ticks - DateTime.UtcNow.Ticks;
return dt.AddTicks(-1 * tickdifference).AddMinutes(gmtOffset);
}
public static bool IsValidNumber(string strValue)
{
if (strValue.Trim().Length != 0)
{
return System.Text.RegularExpressions.Regex.IsMatch(strValue.Trim(), @"^([0-9]){0,10}$");
}
else
{
return false;
}
}
public static bool IsAlphanumeric(string strValue)
{
if (strValue.Trim().Length != 0)
{
return System.Text.RegularExpressions.Regex.IsMatch(strValue.Trim(), @"^([0-9\-.a-zA-Z\s]){0,300}$");
}
else
{
return false;
}
}
public static bool IsChar(string strValue)
{
if (strValue.Trim().Length != 0)
{
return System.Text.RegularExpressions.Regex.IsMatch(strValue.Trim(), @"^([a-zA-Z\s]){0,100}$");
}
else
{
return false;
}
}
public static bool IsValidEmail(string strValue)
{
if (strValue.Trim().Length != 0)
{
return System.Text.RegularExpressions.Regex.IsMatch(strValue.Trim(), @"^([0-9a-zA-Z]([-.\w]*[0-9a-zA-Z])*@([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,9})$");
}
else
{
return false;
}
}
public static string findRestrictKeywords(string str, out bool Restrictfound)
{
ArrayList list = getRestrictKeywords();
string[] words = str.TrimStart().TrimEnd().Split(' ');
Restrictfound = false;
string returnstring = "";
foreach (string word in words)
{
returnstring = word;
Restrictfound = list.Contains(word.Trim().ToUpper());
if (Restrictfound)
break;
}
return returnstring;
}
public static String EncryptString(String blowfishkey, String str)
{
BlowfishSimple bfs = new BlowfishSimple(blowfishkey);
//return GetHex(ASCIIEncoding.ASCII.GetBytes(bfs.Encrypt(str)));
return Uri.EscapeDataString(bfs.Encrypt(str));
}
public static String DecryptString(String blowfishkey, String encodedString)
{
BlowfishSimple bfs = new BlowfishSimple(blowfishkey);
//return bfs.Decrypt(GetString(hexEncodedString));
return bfs.Decrypt(encodedString);
}
public static CustomHashTable GetKeyValues(string str)
{
Hashtable keyvaluelist = new Hashtable();
String[] keyvaluepairs = str.Split(new char[] { '&' });
for (int i = 0; i < keyvaluepairs.Length; i++)
{
string[] keyvalue = keyvaluepairs[i].Split(new char[] { '=' });
if (!keyvaluelist.Contains(keyvalue[0]))
keyvaluelist.Add(keyvalue[0].ToLower().Trim(), keyvalue[1]);
}
return new CustomHashTable(keyvaluelist);
}
private static string GetString(String hex)
{
System.Text.StringBuilder sb = new System.Text.StringBuilder();
char ch;
for (int i = 0; (i + 1) < hex.Length; i += 2)
{
ch = (char)((16 * GetHexVal(hex[i])) + GetHexVal(hex[i + 1]));
sb.Append(ch);
}
String data = sb.ToString();
return data;
}
private static byte GetHexVal(char ch)
{
ch = char.ToUpper(ch);
if (ch >= '0' && ch <= '9')
return (byte)(ch - '0');
else
return (byte)((ch - 'A') + 10);
}
public static ArrayList getRestrictKeywords()
{
ArrayList list = new ArrayList();
list.Add("DROP");
list.Add("DELETE");
list.Add("DATABASE");
list.Add("--");
list.Add("ALTER");
list.Add("CREATE");
list.Add("BEGIN");
list.Add("EXEC");
list.Add("EXECUTE");
list.Add("GRANT");
list.Add("INSERT");
list.Add("UPDATE");
list.Add("TRUNCATE");
list.Add("TRIGGER");
list.Add("WHILE");
list.Add("MODIFY");
list.Add("SELECT");
list.Add("Order By");
list.Add("Group By");
list.Add("Having");
return list;
}
public static String GetString(Object obj)
{
if (obj == null)
return null;
else
return obj.ToString();
}
}
}
Commonlogics.Datamanager
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using Commonlogics;
namespace Commonlogics.DataLogic
{
public class DataManager
{
string connectionstring;
static string connectionString = ConfigurationManager.AppSettings["ConnectionString"];
public SqlConnection conn;
public SqlCommand command;
public SqlDataReader datareader;
SqlCommand cmd_tran = new SqlCommand();
public static DataLogic.DataLayer CInst()
{
return new Commonlogics.DataLogic.DataLayer();
}
public DataManager()
{
connectionstring = ConfigurationManager.AppSettings["ConnectionString"];
conn = new SqlConnection(connectionstring);
}
public static string GetHex(byte[] text)
{
char[] hexChars = new char[] { '0', '1', '2', '3', '4', '5', '6', '7',
'8', '9', 'A', 'B', 'C', 'D', 'E', 'F'
};
System.Text.StringBuilder sb = new StringBuilder();
for (int i = 0; i < text.Length; i++)
{
sb.Append(hexChars[text[i] >> 4]);
sb.Append(hexChars[text[i] & 0x0F]);
}
return sb.ToString();
}
public static void SetConnectionString(string ConnectionString)
{
connectionString = String.Copy(ConnectionString);
}
public static SqlConnection CreateConnection()
{
return new SqlConnection(connectionString);
}
#region helped from rohit(nbcc)
/// <summary>
/// Method is used to select Table Records order by coloumns name.
/// </summary>
/// <param name="TableName">Name of Database Table</param>
/// <param name="dsfillTableName">Table Alias Name for DataSet filling</param>
/// <param name="OrderByColumn">Column Name for Order by clause.</param>
/// <returns></returns>
public DataSet SelectTable(String TableName, string dsfillTableName, string OrderByColumn)
{
SqlDataAdapter da = new SqlDataAdapter("select * from " + TableName + " where status=1 order by " + OrderByColumn + "", conn);
DataSet ds = new DataSet();
try
{
da.Fill(ds, dsfillTableName);
return ds;
}
catch
{
//ds.Tables[0].Rows[0][0] = 11;
return null;
}
}
/// <summary>
/// Method is used to select all records of one Table Records.
/// </summary>
/// <param name="TableName">Name of Database Table</param>
/// <param name="dsfillTableName">Table Alias Name for DataSet filling</param>
/// <returns></returns>
public DataSet SelectTable(String TableName, string dsfillTableName)
{
SqlDataAdapter da = new SqlDataAdapter("select * from " + TableName + " where status=1", conn);
DataSet ds = new DataSet();
try
{
da.Fill(ds, dsfillTableName);
return ds;
}
catch
{
//ds.Tables[0].Rows[0][0] = 11;
return null;
}
}
/// <summary>
/// Method is used to select Table Records of specific columns on the basis of condition.
/// </summary>
/// <param name="TableName">Name of Database Table</param>
/// <param name="colParam">String of column name to be selected in query.</param>
/// <param name="condParam">String of condition for query.</param>
/// <param name="dsfillTableName">Table Alias Name for DataSet filling</param>
/// <param name="OrderByColumn">Column Name for Order by clause.</param>
/// <returns></returns>
public DataSet SelectTable(String TableName, string colParam, string condParam, string dsfillTableName, string OrderByColumn)
{
SqlDataAdapter da = new SqlDataAdapter("select " + colParam + " from " + TableName + " where status=1 and " + condParam + " order by " + OrderByColumn + "", conn);
DataSet ds = new DataSet();
try
{
da.Fill(ds, dsfillTableName);
return ds;
}
catch
{
//ds.Tables[0].Rows[0][0] = 11;
return null;
}
}
/// <summary>
/// Method is used to select Table Records of specific columns on the basis of condition.
/// </summary>
/// <param name="TableName">Name of Database Table</param>
/// <param name="colParam">String of column name to be selected in query.</param>
/// <param name="condParam">String of condition for query.</param>
/// <param name="dsfillTableName">Table Alias Name for DataSet filling</param>
/// <returns></returns>
public DataSet SelectTable(String TableName, string colParam, string condParam, string dsfillTableName)
{
SqlDataAdapter da = new SqlDataAdapter("select " + colParam + " from " + TableName + " where status=1 and " + condParam + "", conn);
DataSet ds = new DataSet();
try
{
da.Fill(ds, dsfillTableName);
return ds;
}
catch
{
return null;
}
}
/// Method is used to select Records of one row using data reader on the basis of condition.
/// <param name="TableName">Name of Database Table</param>
/// <param name="colParam">String of column name to be selected in query.</param>
/// <param name="condParam">String of condition for query.</param>
/// <param name="dsfillTableName">Table Alias Name for DataSet filling</param>
/// <returns></returns>
public string ReadSingleStringFromDb(String TableName, string colParam, string condParam, string OrderByColumn)
{
string str = null;
// SqlConnection con = new SqlConnection(conn);
SqlCommand com = new SqlCommand("select " + colParam + " from " + TableName + " where " + condParam + " order by " + OrderByColumn + "", conn);
try
{
conn.Open();
SqlDataReader dR = com.ExecuteReader();
if (dR.Read())
{
str = dR[0].ToString();
}
conn.Close();
com.Dispose();
conn.Dispose();
}
catch (Exception ex)
{
conn.Close();
com.Dispose();
conn.Dispose();
HttpContext.Current.Response.Write("<script>alert('" + ex.Message + "')</script>");
HttpContext.Current.Response.End();
return null;
}
return str;
}
//public int insertrecord(string query)
//{
// //
//}
#endregion
#region helped from Ambo2.0
/// <summary>
/// Execute the sql command and return the data table
/// </summary>
/// <param name="strCommand">get a SqlCommand</param>
/// <returns>DataTable dt</returns>
public static DataTable GetData(SqlCommand strCommand)
{
SqlConnection connection = null;
try
{
connection = CreateConnection();
strCommand.Connection = connection;
SqlDataAdapter dataAdapter = new SqlDataAdapter(strCommand);
DataTable dt = new DataTable();
dataAdapter.Fill(dt);
return dt;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
/// <summary>
/// Executes the non-query
/// </summary>
/// <param name="strCommand">The non-query sql command to execute</param>
public static void ExecuteNonQuery(string strCommand)
{
SqlConnection connection = null;
try
{
connection = CreateConnection();
SqlCommand command = new SqlCommand(strCommand, connection);
connection.Open();
command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
/// <summary>
/// Execute the Command
/// </summary>
/// <param name="cmdCommand">SqlCommand with command text</param>
public static int ExecuteCommand(SqlCommand cmdCommand)
{
SqlConnection connection = null;
try
{
connection = CreateConnection();
cmdCommand.Connection = connection;
connection.Open();
return cmdCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
/// <summary>
/// Execute the Command
/// </summary>
/// <param name="cmdCommand">SqlCommand with command text</param>
public static int ExecuteCommand(SqlCommand cmdCommand, ref SqlTransaction sqlTransaction, ref SqlConnection connection)
{
try
{
if (connection == null)
connection = CreateConnection();
cmdCommand.Connection = connection;
if (connection.State != ConnectionState.Open)
connection.Open();
if (sqlTransaction == null)
sqlTransaction = connection.BeginTransaction();
cmdCommand.Transaction = sqlTransaction;
return cmdCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
if (sqlTransaction != null)
sqlTransaction.Rollback();
if (connection != null)
connection.Close();
throw ex;
}
}
/// <summary>
/// Executes a scalar Command
/// </summary>
/// <param name="strCommand">A Sql Query</param>
/// <returns>Result object a scalar value</returns>
public static object ExecuteScalar(string strCommand)
{
Object result;
SqlConnection connection = null;
try
{
connection = CreateConnection();
SqlCommand command = new SqlCommand(strCommand, connection);
connection.Open();
result = command.ExecuteScalar();
return result;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
/// <summary>
/// Executes a scalar command
/// </summary>
/// <param name="strCommand">The sql command object to execute</param>
/// <returns>result object a scalar value</returns>
public static object ExecuteScalarCommand(SqlCommand cmd)
{
Object result;
SqlConnection connection=null ;
try
{
connection = CreateConnection();
cmd.Connection = connection;
connection.Open();
result = cmd.ExecuteScalar();
return result;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection != null)
{
connection.Close();
}
}
}
public static DataTable GetSchema(string TableName)
{
string strQuery = "SELECT * FROM " + TableName + " where 1=0 ";
SqlConnection connection = CreateConnection();
SqlCommand cmd = new SqlCommand(strQuery, connection);
connection.Open();
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
// Get the schema table.
DataTable tblSchema = rdr.GetSchemaTable();
connection.Close();
rdr.Close();
return tblSchema;
}
/// <summary>
/// Executes a transaction on a sql server database
/// </summary>
/// <param name="sqlCommands">
/// The array of sql commands to execute
/// </param>
public static void ExecuteTransaction(SqlCommand[] sqlCommands)
{
SqlConnection connection = null;
SqlTransaction transaction = null;
try
{
connection = CreateConnection();
connection.Open();
transaction = connection.BeginTransaction();
for (int i = 0; i < sqlCommands.Length; i++)
{
sqlCommands[i].Connection = connection;
sqlCommands[i].Transaction = transaction;
sqlCommands[i].ExecuteNonQuery();
}
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
finally
{
if (transaction != null)
transaction.Commit();
if (connection != null)
{
connection.Close();
}
}
}
#endregion
#region helped from Rohit(myboolha) method
public string Safe(string usString)
{
if (usString == null)
{
return usString;
}
return Regex.Replace(usString, @"[\r\n\x00\x1a\\'""]", @"\$0");
}
public string Escape(string usString)
{
if (usString == null)
{
return usString;
}
if (usString.ToLower() == "na")
{
usString = "";
}
return usString.Replace("'", "''");
}
public DataTable SelectQuery(string query)
{
DataTable dt = new DataTable();
conn.Open();
SqlDataAdapter adapter = new SqlDataAdapter(query, conn);
adapter.Fill(dt);
conn.Close();
return dt;
}
public DataTable SelectData(String query, SqlParameter[] SqlParams)
{
DataTable dt = new DataTable();
SqlCommand cmd = new SqlCommand(query, conn);
for (int i = 0; i < SqlParams.Length; i++)
{
cmd.Parameters.Add(SqlParams[i]);
}
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(dt);
return dt;
}
public void ExecuteQuery(String query, SqlParameter[] SqlParams)
{
DataTable dt = new DataTable();
conn.Open();
command = new SqlCommand(query, conn);
for (int i = 0; i < SqlParams.Length; i++)
{
command.Parameters.Add(SqlParams[i]);
}
command.ExecuteNonQuery();
conn.Close();
}
public DataTable ExecuteProcedure(String ProcedureName, SqlParameter[] SqlParams)
{
DataTable dt = new DataTable();
command = new SqlCommand(ProcedureName, conn);
command.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adp = new SqlDataAdapter();
for (int i = 0; i < SqlParams.Length; i++)
{
command.Parameters.Add(SqlParams[i]);
}
adp.SelectCommand = command;
adp.Fill(dt);
return dt;
}
public DataTable OutExecuteProcedure111(String ProcedureName, SqlParameter[] SqlParams, out int Emp_Id)
{
DataTable dt = new DataTable();
command = new SqlCommand(ProcedureName, conn);
command.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adp = new SqlDataAdapter();
for (int i = 0; i < SqlParams.Length; i++)
{
command.Parameters.Add(SqlParams[i]);
}
adp.SelectCommand = command;
adp.Fill(dt);
Emp_Id = (int)command.Parameters["@Emp_Id"].Value;
return dt;
}
public DataTable OutExecuteProcedure(String ProcedureName, SqlParameter[] SqlParams, out int totalrows)
{
DataTable dt = new DataTable();
command = new SqlCommand(ProcedureName, conn);
command.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adp = new SqlDataAdapter();
for (int i = 0; i < SqlParams.Length; i++)
{
command.Parameters.Add(SqlParams[i]);
}
adp.SelectCommand = command;
adp.Fill(dt);
totalrows = (int)command.Parameters["@totalrows"].Value;
return dt;
}
public int Update(string spName, SqlParameter[] param)
{
// Open the connection.
OpenConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = spName;
for (int i = 0; i < param.Length; i++)
{
cmd.Parameters.Add(param.GetValue(i));
}
// run command
int rows = cmd.ExecuteNonQuery();
CloseConnection();
Dispose();
// return number of changed rows;
return rows;
}
public int UpdateByTransaction(string spName, SqlParameter[] param, SqlTransaction tr)
{
// Open the connection.
//OpenConnection();
// int id = 0;
cmd_tran.Parameters.Clear();
cmd_tran.Connection = conn;
cmd_tran.CommandType = CommandType.StoredProcedure;
cmd_tran.CommandText = spName;
cmd_tran.Transaction = tr;
for (int i = 0; i < param.Length; i++)
{
cmd_tran.Parameters.Add(param.GetValue(i));
}
// run command
int rows = cmd_tran.ExecuteNonQuery();
//if (Records == 0)
//{
// Genric_Id = (int)cmd_tran.Parameters[Parameter].Value;
//}
//else
//{
// Genric_Id = Convert.ToInt32(param[26].Value);
//}
return rows;
//Dispose();
// return number of changed rows;
}
public DataTable ExecuteStoredProcedureWithReturnValues(String ProcedureName, SqlParameter[] SqlParams)
{
DataTable dt = new DataTable();
command = new SqlCommand(ProcedureName, conn);
command.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adp = new SqlDataAdapter();
for (int i = 0; i < SqlParams.Length; i++)
{
command.Parameters.Add(SqlParams[i]);
}
adp.SelectCommand = command;
adp.Fill(dt);
return dt;
}
public DataTable ExecuteSimpleProcedure(String ProcedureName)
{
DataTable dt = new DataTable();
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
command = new SqlCommand(ProcedureName, conn);
command.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adp = new SqlDataAdapter();
adp.SelectCommand = command;
adp.Fill(dt);
conn.Close();
return dt;
}
public string ReturnSingleValue(string query)
{
DataTable dt = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(query, conn);
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
return dt.Rows[0][0].ToString();
}
else
{
return "";
}
}
public void FireQuery(string query)
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
command = new SqlCommand(query, conn);
command.ExecuteNonQuery();
// command.LastInsertedId;
conn.Close();
}
/////////////*****DB Methods*****//////////////
#region Cleanup methods.
/// <summary>
/// Try to dispose of the connection objects... Not a lot of point in
/// re-throwing an exception here since this will only normally be called
/// when we are finished with the object.
/// </summary>
public void Dispose()
{
conn.Dispose();
conn = null;
}
/// <summary>
/// Attempt to close the master connection.
/// </summary>
public void CloseConnection()
{
if (conn.State != System.Data.ConnectionState.Closed)
{
conn.Close();
}
}
#endregion
/// <summary>
///
/// </summary>
public void OpenConnection()
{
// Open the connection.
if (conn.State == System.Data.ConnectionState.Closed)
{
conn.Open();
}
}
#endregion
}
}
