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
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
Comments (0)
You don't have permission to comment on this page.