Little Programming

namespace Repository
{
    public class SQLRepository
    {

        DbConnector dbCon = new DbConnector();

        #region Query Execute
        public Result ExecuteQuery(string SQL)
        {
            Result oResult = new Result();
            SqlCommand oCmd = null;

            try
            {

                if (dbCon.Connect != null)
                {
                    dbCon.OpenConnection();
                    oCmd = new SqlCommand(SQL, dbCon.Connect);
                    oCmd.ExecuteNonQuery();
                    oResult.ResultState = true;
                }
            }
            catch (Exception ex)
            {
                oResult.ResultState = false;
                oResult.SqlError = ex.Message;
            }
            finally
            {
                dbCon.CloseConnection();
            }
            return oResult;
        }

        public Result ExecuteQuery(List SQL)
        {
            Result oResult = new Result();
            SqlTransaction oTransaction = null;
            SqlCommand oCmd = null;

            try
            {

                if (dbCon.Connect != null)
                {
                    dbCon.OpenConnection();
                    oTransaction = dbCon.Connect.BeginTransaction();
                    foreach (string s in SQL)
                    {
                        oCmd = new SqlCommand(s, dbCon.Connect);
                        oCmd.Transaction = oTransaction;
                        oCmd.ExecuteNonQuery();
                        oResult.ResultState = true;
                    }
                    oTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                oResult.ResultState = false;
                oResult.SqlError = ex.Message;
                if (oTransaction != null)
                    oTransaction.Rollback();
            }
            finally
            {
                dbCon.CloseConnection();
            }
            return oResult;
        }

        public Result ExecuteQueryParam(List SQL)
        {
            Result oResult = new Result();
            SqlTransaction oTransaction = null;
            SqlCommand oCmd = null;

            try
            {

                if (dbCon.Connect != null)
                {
                    dbCon.OpenConnection();
                    oTransaction = dbCon.Connect.BeginTransaction();
                    foreach (SQLQuery s in SQL)
                    {
                        oCmd = new SqlCommand(s.sql, dbCon.Connect);
                        oCmd.Transaction = oTransaction;

                        foreach (SQLParam param in s.Param)
                        {
                            SqlParameter picparameter = new SqlParameter();
                            picparameter.SqlDbType = param.DbType; //SqlDbType.Image;
                            picparameter.ParameterName = param.ParamName; //"@PIC";
                            picparameter.Value = param.ParamValue;
                            oCmd.Parameters.Add(picparameter);
                        }

                        oCmd.ExecuteNonQuery();
                        oResult.ResultState = true;
                    }
                    oTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                oResult.ResultState = false;
                oResult.SqlError = ex.Message;
                if (oTransaction != null)
                    oTransaction.Rollback();
            }
            finally
            {
                dbCon.CloseConnection();
            }
            return oResult;
        }

        #endregion

        public Result Select(string SQL)
        {
            Result oResult = new Result();
            SqlCommand oCmd = null;
            try
            {

                if (dbCon.Connect != null)
                {
                    oCmd = new SqlCommand(SQL, dbCon.Connect);
                    oCmd.CommandTimeout = 0;
                    SqlDataAdapter adapter = new SqlDataAdapter(oCmd);
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);
                    oResult.ResultState = true;
                    oResult.Data = dt;
                }
            }
            catch (Exception ex)
            {
                oResult.ResultState = false;
                oResult.SqlError = ex.Message;
            }
            finally
            {
                dbCon.CloseConnection();
            }
            return oResult;
        }

    }

    public class DbConnector
    {

        private SqlConnection connection;

        public DbConnector()
        {
            connection = new SqlConnection(GlobalConnection());
        }

        public string GlobalConnection()
        {

            //string entityConnectionString = ConfigurationManager.ConnectionStrings["ERPEntities"].ConnectionString;
            //string providerConnectionString = new EntityConnectionStringBuilder(entityConnectionString).ProviderConnectionString;
            //return providerConnectionString;

            string str = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
            //ConfigurationSettings.AppSettings["CNN"];
            return str;
        }

        public SqlConnection Connect
        {
            get { return connection; }
        }

        public ConnectionState OpenConnection()
        {
            Connect.Open();
            return ConnectionState.Open;
        }

        public ConnectionState CloseConnection()
        {
            Connect.Close();
            return ConnectionState.Closed;
        }
    }

    public class SQLQuery
    {
        public string sql { get; set; }
        public List Param = new List();
    }

    public class SQLParam
    {
        public string ParamName { get; set; }
        public byte[] ParamValue { get; set; }
        public SqlDbType DbType { get; set; }
    }

    //public class Result
    //{
    //    public bool ResultState;
    //    public string SqlError;
    //    public DataTable Data;
    //}

}

triggering closing X browser event

 

Attaching the Damaged SQL Server Database

Rebuilding the SQL Server Transaction Log

USE master
GO

DBCC TRACEON(3604)
GO

ALTER DATABASE TestDB_Repair SET EMERGENCY
GO

ALTER DATABASE TestDB_Repair SET SINGLE_USER
GO

DBCC CHECKDB('TestDB_Repair', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS
GO

ALTER DATABASE TestDB_Repair SET MULTI_USER
GO

Source : https://www.mssqltips.com/sqlservertip/3579/how-to-attach-a-sql-server-database-without-a-transaction-log-and-with-open-transactions/

 public class CustomAuthorizeAttribute : AuthorizeAttribute
    {
        public string UsersConfigKey { get; set; }
        public string RolesConfigKey { get; set; }

        protected virtual CustomPrincipal CurrentUser
        {
            get { return HttpContext.Current.User as CustomPrincipal; }
        }

        public override void OnAuthorization(AuthorizationContext filterContext)
        {
            if (filterContext.HttpContext.Request.IsAuthenticated)
            {
                var authorizedUsers = ConfigurationManager.AppSettings[UsersConfigKey];
                var authorizedRoles = ConfigurationManager.AppSettings[RolesConfigKey];

                Users = String.IsNullOrEmpty(Users) ? authorizedUsers : Users;
                Roles = String.IsNullOrEmpty(Roles) ? authorizedRoles : Roles;

                if (!String.IsNullOrEmpty(Roles))
                {
                    if (!CurrentUser.IsInRole(Roles))
                    {
                        filterContext.Result = new RedirectToRouteResult(new
                        RouteValueDictionary(new { controller = "Error", action = "AccessDenied" }));

                         base.OnAuthorization(filterContext); returns to login url
                    }
                }

                if (!String.IsNullOrEmpty(Users))
                {
                    if (!Users.Contains(CurrentUser.UserId.ToString()))
                    {
                        filterContext.Result = new RedirectToRouteResult(new
                        RouteValueDictionary(new { controller = "Error", action = "AccessDenied" }));

                         base.OnAuthorization(filterContext); returns to login url
                    }
                }
            }

        }

Process 1 : 
 protected void gvUser_Sorting(object sender, GridViewSortEventArgs e)
        {

            string search = txtSearch.Text.Trim();
            string SortExp = e.SortExpression;
           
           
            List<Common.TblUser> oList = UserBLL.GetUserList();
            if (txtSearch.Text != "")
            {
                string text = txtSearch.Text.ToUpper();
                oList = oList.FindAll(m => m.FullName.ToUpper().Contains(text) || m.RoleName.ToUpper().Contains(text) || m.UserName.ToUpper().Contains(text) || m.ExternalID.ToUpper().Contains(text)).ToList();
            }

            System.Reflection.PropertyInfo property = oList.GetType().GetGenericArguments()[0].GetProperty(SortExp);
            if (e.SortDirection == SortDirection.Ascending)
            {
                oList = oList.OrderBy(g => property.GetValue(g, null)).ToList<Common.TblUser>();
            }
            else
            {
              oList =  oList.OrderByDescending(g => property.GetValue(g, null)).ToList<Common.TblUser>();
            }

            this.gvUser.DataSource = oList;
            this.gvUser.DataBind();

        }

Process 2
Using Object Datasource


  <asp:GridView ID="GVUserList" runat="server" AutoGenerateColumns="False"
                        DataSourceID="odsGrid" AllowPaging="True" AllowSorting="True"
 DataKeyNames="CBTID" >
                        <Columns>
                            <asp:BoundField DataField="BTName" HeaderText="BTName" SortExpression="BTName" />
                        </Columns>
                        <EmptyDataTemplate>
                            <table style="width:100%;">
                                <tr>
                                    <th>No Record Added</th>
                                </tr>
                            </table>
                        </EmptyDataTemplate>
                    </asp:GridView>

<asp:ObjectDataSource ID="odsGrid" runat="server" OldValuesParameterFormatString="original_{0}"
                        SelectMethod="SelectAllForGrid" TypeName="WellFood.Business.Managers.BrandTypeManager"
                        SortParameterName="Sortby">
                        <SelectParameters>
                            <asp:Parameter Name="SearchExpression" Type="String" />
                        </SelectParameters>
                    </asp:ObjectDataSource>




[DataObjectMethod(DataObjectMethodType.Select)]
        public List<BrandType> SelectAllForGrid(string SearchExpression, string Sortby)
        {
            if (string.IsNullOrEmpty(Sortby))
                Sortby = "PrdID";

            Query = @"SELECT ROW_NUMBER() OVER(ORDER BY   " + Sortby + @" ) as RowNum,
                    CBTID ,BTID ,BTName ,PrdID ,
                    PrdName ,GroupID ,GroupName ,FloorID ,VATPrcnt ,DiscPrcnt
                    FROM [BrandType]
                    order by RowNum";
            oR = sQLDal.Select(Query);
            List<BrandType> oList = new List<BrandType>();
            if (oR.ResultState && oR.Data.Rows.Count > 0)
            {
                oList = DataTableToClass.DataTableToList<BrandType>(oR.Data);
                return oList;
            }
            else return null;
        }

        public static string GetConnectionString()
        {
            string entityConnectionString = ConfigurationManager.ConnectionStrings["ERPEntities"].ConnectionString;
            string providerConnectionString = new EntityConnectionStringBuilder(entityConnectionString).ProviderConnectionString;
            return providerConnectionString;
        }

public static List DataTableToList(this DataTable table) where T : class, new()

        {
           try
            {
                List list = new List();
                foreach (var row in table.AsEnumerable())
               {
                    T obj = new T();
                    foreach (var prop in obj.GetType().GetProperties())
                    {
                        try
                        {
                            PropertyInfo propertyInfo = obj.GetType().GetProperty(prop.Name);

                            propertyInfo.SetValue(obj, Convert.ChangeType(row[prop.Name], propertyInfo.PropertyType), null);

                        }
                        catch
                        {
                            continue;
                        }
                    }
                    list.Add(obj);
                }
                return list;
            }
            catch
            {
                return null;
            }
        }