locked
Data Access Layer RRS feed

  • Question

  • User-1258070753 posted

    i am starting on a new project

    it will be two-tier with data access layer and presentation layer

    i need help in daya access layer

    can anyone give me data layer e.g

    which provide establishing a connection with database and performing various dml statements like insert delete and update

    and

    datafill class -in which from frontend i just need to pass value and rest making of dataset and binding of data controls in data fill class

    or maybe something similar to this will be a great start for me


    thank you


    Regards

    Rockyy

    Language=ASP.NET C#

    BACKEND-SQL SERVER 2005

    Friday, June 18, 2010 10:21 AM

Answers

  • User-1802908944 posted

    you can right these line in DAL and call it in on you presentation layer.


      public static void DBBackUp(string r)
            {
                Database db = null;  //Creating Object of Database Class 
                DbCommand dbCmd = null; //Creating Object of DbCommand Class
                string strBackUp = "";
                strBackUp = con.Database.ToString() + r;
                //con.Open();
                //dbCmd.Connection = con;
                dbCmd = db.GetStoredProcCommand(ConstantDAL.spDbBackUp);
                
                    db.AddInParameter(dbCommand, ConstantDAL.pnDBName, DbType.String, QuestionCategory.Category);
                    db.AddInParameter(dbCommand, ConstantDAL.pnBackUpFileName, DbType.String, QuestionCategory.Code);
                    
                    
                int result = 0;
                Database db = null;  //Creating Object of Database Class 
                DbCommand dbCommand = null; //Creating Object of DbCommand Class
                try
                {
                    //Insert Into [QuestionCategory] Table 
                    db = ConnectionString.GetDataBaseInstance();
                    dbCommand = db.GetStoredProcCommand(ConstantDAL.spInsertQuestionCategory);
                    db.AddInParameter(dbCommand, ConstantDAL.pnQuestionCategory, DbType.String, QuestionCategory.Category);
                    db.AddInParameter(dbCommand, ConstantDAL.pnQuestionCategoryCode, DbType.String, QuestionCategory.Code);
                    db.AddInParameter(dbCommand, ConstantDAL.pnQuestionCategoryDescription, DbType.String, QuestionCategory.Description);
                    db.AddInParameter(dbCommand, ConstantDAL.pnQuestionCategoryStatus, DbType.String, QuestionCategory.Status);
                    result = db.ExecuteNonQuery(dbCommand);
                    return result; //returns value
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    QuestionCategory = null;
                    db = null;
                    dbCommand = null;
                }
            }

    string ConnectionString = string.Empty;

      //// Get connection information from the application configuration file

    ConnectionString = ConfigurationManager.ConnectionStrings["SQLServerConnection"].ConnectionString;

                //// Declare new SQL Command object

                SqlCommand command = new SqlCommand();

                //// Declare new SQL Connection object

                SqlConnection connection = new SqlConnection(ConnectionString);


                // set SQL Command object's text, type, and connection


               command.CommandText = strQuery;

               command.CommandType = CommandType.StoredProcedure;

               command.CommandType = CommandType.Text;

               command.Parameters.Add("@Name", SqlDbType.Int).Value = _Name;

               command.Connection = connection;

               //// Open database Connection

               connection.Open();

               command.ExecuteNonQuery();

    connection.close();


    working code


    happy coding

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 18, 2010 12:25 PM

All replies

  • User-1802908944 posted

    you can right these line in DAL and call it in on you presentation layer.


      public static void DBBackUp(string r)
            {
                Database db = null;  //Creating Object of Database Class 
                DbCommand dbCmd = null; //Creating Object of DbCommand Class
                string strBackUp = "";
                strBackUp = con.Database.ToString() + r;
                //con.Open();
                //dbCmd.Connection = con;
                dbCmd = db.GetStoredProcCommand(ConstantDAL.spDbBackUp);
                
                    db.AddInParameter(dbCommand, ConstantDAL.pnDBName, DbType.String, QuestionCategory.Category);
                    db.AddInParameter(dbCommand, ConstantDAL.pnBackUpFileName, DbType.String, QuestionCategory.Code);
                    
                    
                int result = 0;
                Database db = null;  //Creating Object of Database Class 
                DbCommand dbCommand = null; //Creating Object of DbCommand Class
                try
                {
                    //Insert Into [QuestionCategory] Table 
                    db = ConnectionString.GetDataBaseInstance();
                    dbCommand = db.GetStoredProcCommand(ConstantDAL.spInsertQuestionCategory);
                    db.AddInParameter(dbCommand, ConstantDAL.pnQuestionCategory, DbType.String, QuestionCategory.Category);
                    db.AddInParameter(dbCommand, ConstantDAL.pnQuestionCategoryCode, DbType.String, QuestionCategory.Code);
                    db.AddInParameter(dbCommand, ConstantDAL.pnQuestionCategoryDescription, DbType.String, QuestionCategory.Description);
                    db.AddInParameter(dbCommand, ConstantDAL.pnQuestionCategoryStatus, DbType.String, QuestionCategory.Status);
                    result = db.ExecuteNonQuery(dbCommand);
                    return result; //returns value
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    QuestionCategory = null;
                    db = null;
                    dbCommand = null;
                }
            }

    string ConnectionString = string.Empty;

      //// Get connection information from the application configuration file

    ConnectionString = ConfigurationManager.ConnectionStrings["SQLServerConnection"].ConnectionString;

                //// Declare new SQL Command object

                SqlCommand command = new SqlCommand();

                //// Declare new SQL Connection object

                SqlConnection connection = new SqlConnection(ConnectionString);


                // set SQL Command object's text, type, and connection


               command.CommandText = strQuery;

               command.CommandType = CommandType.StoredProcedure;

               command.CommandType = CommandType.Text;

               command.Parameters.Add("@Name", SqlDbType.Int).Value = _Name;

               command.Connection = connection;

               //// Open database Connection

               connection.Open();

               command.ExecuteNonQuery();

    connection.close();


    working code


    happy coding

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 18, 2010 12:25 PM
  • User-1535021499 posted

    Check this link for most of your data access related queries. If need any further help get back.

    http://msdn.microsoft.com/en-us/library/32c5dh3b(v=VS.71).aspx 

    Monday, June 21, 2010 6:50 AM
  • User516094431 posted

    please use Microsoft Application Block for your requirement. Microsoft Application Block provide the functionality of data access layer. it is simple a class that SQLHelper.cs and provide the every function which is use for data access. you include in your project and use it freely. for more information about MAB read this.

    http://msdn.microsoft.com/en-us/library/ms954827.aspx

    download this class:

    http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=76FE2B16-3271-42C2-B138-2891102590AD

    Thanks.

    Monday, June 21, 2010 7:06 AM