Coding Correctly in .NET

 

 

Prerequisites

 

Knowledge of .NET and Using Visual Studio. References to using Database Stored procedures are made in the lecture but not covered as part of the lecture.

 

Description of Video

 

Shown is a solid methodology for moving between interface code, domain (business logic code) and the database layer. This is a lecture on how to correctly structure your code.

 

Video 

 

Download Link (right click and save target as ...).

 

 

Reference Materials

 

dboperations code in C#

 

This is the full code of the C# version of a common class to handle database operations - callig a stored procedure within the database. A more complete version of the class written in Visual Basic is available at Code - Database Common Class

 

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.Collections;
using System.Xml;
using System.Data.SqlClient;
using System.Xml.XPath;
using System.Collections.Generic;

namespace sage

{

    /// <summary>

    /// Database Operations Class

    /// </summary>

    public class dboperations

    {

        /// <summary>

        /// Gets XML Document from database stored procedure

        /// </summary>

        /// <param name="sp"></param>

        /// <param name="Parameters"></param>

        /// <returns></returns>

        public XmlDocument GetXmlDocument(string sp, Dictionary<string, object> Parameters)
        {
            XmlDocument document = new XmlDocument();

            using (SqlConnection connection =
                       new SqlConnection(ConfigurationManager.ConnectionStrings["charlotteConnectionString"].ConnectionString))
            {
                using (SqlCommand command =
                               new SqlCommand(
                    sp, connection)
                    )
                {
                    command.CommandType = CommandType.StoredProcedure;

                    foreach (string Key in Parameters.Keys)
                    {
                        command.Parameters.AddWithValue(Key, Parameters[Key] == null ? DBNull.Value : Parameters[Key]);
                    }

                    connection.Open();

                    using (XmlReader reader =
                                command.ExecuteXmlReader())
                    {
                        if (reader.Read())
                            document.Load(
                          reader);
                    }

                    connection.Close();
                }
            }

            return document;
        }

        public DataSet GetDataset(string sp, Dictionary<string, object> Parameters)
        {

            DataSet ds = new DataSet();

            using (SqlConnection connection =
                       new SqlConnection(ConfigurationManager.ConnectionStrings["YOUR_CONNECTION_STRING_NAME_FROM_WEB.CONFIG"].ConnectionString))
            {
                using (SqlCommand command =
                               new SqlCommand(
                    sp, connection)
                    )
                {
                    command.CommandType = CommandType.StoredProcedure;

                    foreach (string Key in Parameters.Keys)
                    {
                        command.Parameters.AddWithValue(Key, Parameters[Key] == null ? DBNull.Value : Parameters[Key]);
                    }

                    connection.Open();

                    SqlDataAdapter daAdapter = new SqlDataAdapter(command);
                    daAdapter.Fill(ds);
                }
            }
            return ds;
        }

        public int ExecuteProcedure(String sp, Dictionary<string, object> Parameters, bool hasReturnValue)
        {
            try
            {
                int intReturn = 0;

                using (SqlConnection connection =
                           new SqlConnection(ConfigurationManager.ConnectionStrings["YOUR_CONNECTION_STRING_NAME_FROM_WEB.CONFIG"].ConnectionString))
                {
                    using (SqlCommand command =
                                   new SqlCommand(
                        sp, connection)
                        )

                    {
                        command.CommandType = CommandType.StoredProcedure;

                        foreach (string Key in Parameters.Keys)
                        {
                            command.Parameters.AddWithValue(Key, Parameters[Key] == null ? DBNull.Value : Parameters[Key]);
                        }

                        if (hasReturnValue)
                        {
                            SqlParameter prmReturn = new SqlParameter("@Return", SqlDbType.Int);
                            prmReturn.Direction = ParameterDirection.ReturnValue;
                            command.Parameters.Add(prmReturn);
                        }

                        connection.Open();
                        command.ExecuteNonQuery();

                        if (hasReturnValue)
                        {

                            // Get the out parameters

                            SqlParameter prmReturn = (SqlParameter)command.Parameters["@Return"];
                            intReturn = Convert.ToInt32(prmReturn.Value);
                        }
                    }
                }

                return intReturn;
            }

            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}

 

Example Code of Using dboperations code for setting a Bookmark

 

This method is a member of Class bookmark - Static means you can call this without instantiating a Bookmark object

 

        public static DataSet getBookmarksForUser(int userid)
        {
            dboperations dbs = new dboperations();
            Dictionary<string, object> Parameters = new Dictionary<string, object>();
            DataSet ds = new DataSet();
            Parameters.Add("@userID", userid);
            ds = dbs.GetDataset("sp_GetBookmarksForUser", Parameters);
            return ds;
        }

 

Example call from Code-behind to view bookmarks for a user

 

    protected void Page_Load(object sender, EventArgs e)
    {

// Get User ID from Session
// this assumes that the userid is stored as a session variable
 
      String userid = Session["userid"].ToString();


// Sets the DataSource to an Object from Class Bookmarks
// Which is a public static class that returns a DataSet

       GridView_viewbkmrks.DataSource =Bookmark.getBookmarksForUser(int.Parse(userid));
       GridView_viewbkmrks.DataBind();
    }

 

 

Additional Information

 

 

 

CET 4429 Lectures | Front Page