DB Tutorials | Database Tutorials, Tips, and Articles http://www.dbtutorials.com Wed, 02 Apr 2014 15:02:37 +0000 en-US hourly 1 http://wordpress.org/?v=3.9.1 Built-in SQL Functions http://www.dbtutorials.com/sql-server/built-in-sql-functions/ http://www.dbtutorials.com/sql-server/built-in-sql-functions/#comments Wed, 02 Apr 2014 15:02:37 +0000 http://www.dbtutorials.com/?p=616 SQL has many Built-In SQL Functions that are useful for performing calculations on data within your database. This will help organize and optimize your workflow when working within a SQL […]

The post Built-in SQL Functions appeared first on DB Tutorials | Database Tutorials, Tips, and Articles.

]]>
SQL has many Built-In SQL Functions that are useful for performing calculations on data within your database. This will help organize and optimize your workflow when working within a SQL database. All the functions can be broken down into these different categories: Aggregate Functions, and Scalar Functions, Numeric Functions and String Functions. Each one having their own purpose and specific use. Let’s go over a few of them and include some examples. In this tutorial we will be going over some of the Aggregate Functions and Scalar Functions.

SQL Aggregate Functions

With a SQL Aggregate Function, you can return a single value, calculated from values in a column. Let’s look at the list of SQL Aggregate Functions.

  • AVG() – Returns the average value
  • COUNT() – Returns the number of rows
  • FIRST() – Returns the first value
  • LAST() – Returns the last value
  • MAX() – Returns the largest value
  • MIN() – Returns the smallest value
  • SUM() – Returns the sum

The general syntax for an aggregate function will follow the guideline : aggregate_function_name ( [ALL | DISTINCT] expression ).

The aggregate function name can be anyone of the functions listed above. The ALL clause is the default behavior (when not specified) and evaluates all rows when aggregating the value of the function. The DISTINCT clause uses only distinct values when evaluating the function. In most cases you will not need to specify All or Distinct. Let’s look at an example of how to use this guideline.

Here is our table we will be working with which is named Products:

ProductID ProductName Price
1 Apples 8
2 Oranges 12
3 Peaches 10

By using the AVG() function, we will be able to get the mathematical average value of a column. By using the following code, we are able to get the Average of the Price column and designate it a new row called PriceAverage

SELECT AVG(Price) AS PriceAverage FROM Products;

Input the command and you should get an average value of 10. To break it down, the SELECT statement is used to select the data from a database, AVG(Price) to get the average value of the price column, AS PriceAverage to place our value in a new row called PriceAverage and FROM Products to designate which table to pull the data from (if we have multiple tables).

Here is another example. What if we wanted to get the SUM of a table’s column? We would want to use the SUM function on our Products table Price and get the sum of 30.

SELECT SUM(Price) AS TotalItemsPrice FROM Products;

SQL Scalar Functions

With a SQL Scalar Function, you can return a single value, based on the input value. They are particualrly used to identify the current user session and organizing the tables. Let’s look at the list of some of the SQL Scalar Functions.

  • UCASE() – Converts a field to upper case
  • LCASE() – Converts a field to lower case
  • MID() – Extract characters from a text field
  • LEN() – Returns the length of a text field
  • ROUND() – Rounds a numeric field to the number of decimals specified
  • NOW() – Returns the current system date and time
  • FORMAT() – Formats how a field is to be displayed

Each one of these functions have their own syntax and are worth looking into. The first Scalar Function we will be trying out is the MID() Function. By using the MID() Function we will be able extract characters from a text field inside of our table. The typical syntax guidelines for this function is SELECT MID(column_name,start[,length]) AS some_name FROM table_name;. It is pretty straight forward as you designate the MID() Function and the parameters such as the name of the column you are targeting, the starting point where to count, how many characters to count from the start and give the value a new row as well as the table you are choosing from.

Here is our table we will be working with which is named Products:

ProductID ProductName Price
1 Apples 8
2 Oranges 12
3 Peaches 10

If we wanted to select the first three characters of each Product from the ProductName column, our code would look something like this. Input the command and you should get the results with a new row called “ProductAbbr” with App, Ora, and Pea in each row.

SELECT MID(ProductName,1,3) AS ProductAbbr
FROM Products;

Here is another example. What if we wanted to add a row of the time when these products prices were updated? We can use the NOW() function to solve this problem. Here is the code that will create a column called PerDate and display the current date and time.

SELECT ProductName, Price, Now() AS PerDate
FROM Products;

Wrapping It Up

There are so many more built-in SQL functions that you can utilize in your database management. We have yet to go over Numeric Functions and String Functions which you can look forward to in future lessons. Try practicing setting up your own database and test some of these functions out.

The post Built-in SQL Functions appeared first on DB Tutorials | Database Tutorials, Tips, and Articles.

]]>
http://www.dbtutorials.com/sql-server/built-in-sql-functions/feed/ 0
CRUD operations in MongoDB http://www.dbtutorials.com/mongodb/crud-operations-mongodb/ http://www.dbtutorials.com/mongodb/crud-operations-mongodb/#comments Thu, 02 Jan 2014 17:34:33 +0000 http://www.dbtutorials.com/?p=607 Introduction The web is constantly evolving and as it evolves, we are beginning to see traditional SQL based databases being replaced by document object databases like MongoDB. The advantages these […]

The post CRUD operations in MongoDB appeared first on DB Tutorials | Database Tutorials, Tips, and Articles.

]]>
Introduction

The web is constantly evolving and as it evolves, we are beginning to see traditional SQL based databases being replaced by document object databases like MongoDB. The advantages these databases have often begins with agility and scalability, however these aren’t the only redeeming qualities that databases like MongoDB possess. Today we’ll be explaining these advantages alongside the basic CRUD operations of MongoDB.

For the purpose of this tutorial we’re using the MongoDB interactive shell, if you don’t know what that is, or need help installing it you can find it here.

Create

Our first operation of discussion will be creating objects within the database. MongoDB uses a JSON style object format called BSON as it’s storage. BSON is pretty much a binary representation of a JSON object. So it makes sense that the methods that are enacted upon the database are also JavaScript-esque. We’re going to be creating a users collection and our first user. A collection in MongoDB is synonymous with a table in SQL based databases.

Let’s take a look:

db.users.save({name:"John Doe",age:30,job:"engineer",email:"name@example.com"})

Above we’re using the MongoDB save() method to save our data to our users collection.

Read

Now that we have a data object in our database, we can read it! There are many ways to read data in MongoDB. The primary method for reading data is the find() method. Let’s take a look at some basic usage of the find() method.

db.users.find({name:"John Doe"})

In the above statement, we’re querying the database using the find() method with the parameter of {name:”John Doe”}. It’s important to remember that the find() method will return all documents in the database that mach the key value pair.

We can also manipulate our query by using query cursor methods.

To return only the first document that matches we need to use the findOne() method:

db.users.findOne({name:"John Doe"})

If you’re just trying to figure out how many documents match your query, you can use the count() method:

db.users.count({name:"John Doe"})

Update

To update a document in your MongoDB database you need to use the update() method. The update method accepts two parameters, the query and the update. The query is the first parameter passed through the method, then the update parameter which is preceded by a comma.

An example of the update() method looks like this:

db.users.update({name:"John Doe"}, {age: 28})

Be cautious, this will overwrite everything in your document and leave it as just “{ age: 28 }”.

To update just a single key value pair, we need to add some keywords to the query:

db.users.update({name: "John Doe"}, { $set: { age: 28}})

Now we would retain all of the information that was in the document, and just change the value of age.

For more options and information regarding the update() method, head over to the MongoDB documentation.

Delete

The last method is pretty simple, it’s the remove() method. This method is what you would use to delete data or sets of data from your database:

db.users.remove({name: "John Doe"})

You could use keywords to alter the meaning of the remove method from within the parameter parenthesis, for more on this visit the documentation.

Like the find() method, this method will delete every document that matches or contains the query parameter unless otherwise specified.

Those are the basics of creating, reading, updating and deleting with MongoDB. As you can see it’s very agile and easy to learn!

The post CRUD operations in MongoDB appeared first on DB Tutorials | Database Tutorials, Tips, and Articles.

]]>
http://www.dbtutorials.com/mongodb/crud-operations-mongodb/feed/ 0
The Syntax of SQL SELECT http://www.dbtutorials.com/sql-server/syntax-sql-select/ http://www.dbtutorials.com/sql-server/syntax-sql-select/#comments Fri, 11 Oct 2013 19:56:06 +0000 http://dbtutorials.com/?p=589 Introduction The core command that you will use in any SQL infrastructure is SELECT statement. The select statement selects data from your database. This tutorial will introduce you to the […]

The post The Syntax of SQL SELECT appeared first on DB Tutorials | Database Tutorials, Tips, and Articles.

]]>
Introduction

The core command that you will use in any SQL infrastructure is SELECT statement. The select statement selects data from your database. This tutorial will introduce you to the syntax of the SELECT statement.

SELECT

One of the most basic queries in SQL is:

SELECT * FROM database_table;

In this query, database_table is the name of the database table we are querying. The * specifies all the rows and columns of the specified table. And of course, last but not least, the SELECT statement gives the command to grab and display the data.

So for instance, if you wanted to select a specific row from a table you could use something like this:

SELECT the_row FROM database_table;

Where the_row is a row in the database table.

Let’s step it up a bit. Say you needed to find out what the username of a user in your database, and all you have is their user ID. You could do something like this:

SELECT username FROM database_table WHERE user_ID='1';

This query is a little more complex and uses the keyword WHERE to specify a parameter that is being passed into the query.

Well that’s the basic syntax for the SELECT statement. Paired with other statements it is very powerful and the core tool used to query a database in SQL.

The post The Syntax of SQL SELECT appeared first on DB Tutorials | Database Tutorials, Tips, and Articles.

]]>
http://www.dbtutorials.com/sql-server/syntax-sql-select/feed/ 0
Execution Time using Data Access Component in VB http://www.dbtutorials.com/advanced/dac-execution-time-vb/ http://www.dbtutorials.com/advanced/dac-execution-time-vb/#comments Tue, 17 Sep 2013 19:16:29 +0000 http://dbtutorials.com/?p=550 This tutorial will show you how to create your own Data Access Component and also how to display the time it takes to retrieve data from a SQL database. VB […]

The post Execution Time using Data Access Component in VB appeared first on DB Tutorials | Database Tutorials, Tips, and Articles.

]]>
This tutorial will show you how to create your own Data Access Component and also how to display the time it takes to retrieve data from a SQL database. VB version.

Using Visual Studio.NET 2008, we can build our own Data Access Components to work with and manipulate data. In this tutorial, we will look at how we can do this, and how we can also retrieve the duration of data retrieval, which can be useful when dealing with large amounts of data. For this example, we will be working with a SQL database, and we will be creating our own class to retrieve the data. We will be using the Object Data Source to interact with our class, and a GridView to display the data.

The database we will be working with will have just one table, and three columns – id, name and age. Once created, we will add some sample data to use.

When our database is ready to go, we can start building our class that will retrieve data from our database. The class will have a method to retrieve all records from the database and input into a List. Our class will look something like this:

Private Shared ReadOnly _connectionString As String

Private _name As String
Private _age As String

Public Property Name() As String
Get
Return _name
End Get
Set(ByVal value As String)
_name = value
End Set
End Property

Public Property Age() As String
Get
Return _age
End Get
Set(ByVal value As String)
_age = value
End Set
End Property

Public Function GetAll(<System.Runtime.InteropServices.Out()> ByRef execTime As Long) As List(Of People)
Dim results As List(Of People) = New List(Of People)()
Dim con As New SqlConnection(_connectionString)
Dim cmd As New SqlCommand("SELECT Name,Age FROM tblPeople", con)
con.StatisticsEnabled = True
Using con
con.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader()
Do While dr.Read()
Dim newPerson As New People()
newPerson.Name = CStr(dr("Name"))
newPerson.Age = CStr(dr("Age"))
results.Add(newPerson)
Loop
End Using
Dim stats As IDictionary = con.RetrieveStatistics()
execTime = CLng(Fix(stats("ExecutionTime")))
Return results
End Function

Shared Sub New()
_connectionString = WebConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
End Sub

The GetAll method uses a List to collect all data from the database and then using a loop, we add each record from the database into the results List we created. This is the List that is then returned. We will use this method to select data, using the ObjectDataSource. We can now build our ASPX page like so:

<form id="form1" runat="server">
<asp:GridView ID="GridView1" runat="server" DataSourceID="ObjectDataSource1"
Width="377px" />

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
TypeName="People" SelectMethod="GetAll" OnSelected="ObjectDataSource1_Selected">
<SelectParameters>
<asp:Parameter Name="execTime" Type="Int64" Direction="Output" />
</SelectParameters>
</asp:ObjectDataSource>

<br />
<br />
Time to retrieve data was: <asp:Label ID="lblStatus" runat="server" />.
</form>

Notice that we have assigned the GetAll method to our SelectMethod attribute of our ObjectDataSource, and the TypeName is the name of the Class. We also have a method that fires on the OnSelected event. This means that when the ObjectDataSource selects data (through the class), the following code is processed:

Protected Sub ObjectDataSource1_Selected(ByVal sender As Object, ByVal e As ObjectDataSourceStatusEventArgs)
lblStatus.Text = e.OutputParameters("execTime").ToString() & "ms"
End Sub

This is where we output the time it took to retrieve the data.
The entire code-behind will look something like this:

Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub ObjectDataSource1_Selected(ByVal sender As Object, ByVal e As ObjectDataSourceStatusEventArgs)
lblStatus.Text = e.OutputParameters("execTime").ToString() & "ms"
End Sub
End Class

The entire code of the class is as follows:

Imports Microsoft.VisualBasic
Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports System.Web.Configuration

Public Class People
Private Shared ReadOnly _connectionString As String

Private _name As String
Private _age As String

Public Property Name() As String
Get
Return _name
End Get
Set(ByVal value As String)
_name = value
End Set
End Property

Public Property Age() As String
Get
Return _age
End Get
Set(ByVal value As String)
_age = value
End Set
End Property

Public Function GetAll(<System.Runtime.InteropServices.Out()> ByRef execTime As Long) As List(Of People)
Dim results As List(Of People) = New List(Of People)()
Dim con As New SqlConnection(_connectionString)
Dim cmd As New SqlCommand("SELECT Name,Age FROM tblPeople", con)
con.StatisticsEnabled = True
Using con
con.Open()
Dim dr As SqlDataReader = cmd.ExecuteReader()
Do While dr.Read()
Dim newPerson As New People()
newPerson.Name = CStr(dr("Name"))
newPerson.Age = CStr(dr("Age"))
results.Add(newPerson)
Loop
End Using
Dim stats As IDictionary = con.RetrieveStatistics()
execTime = CLng(Fix(stats("ExecutionTime")))
Return results
End Function

Shared Sub New()
_connectionString = WebConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
End Sub
End Class

Download Source Files

The post Execution Time using Data Access Component in VB appeared first on DB Tutorials | Database Tutorials, Tips, and Articles.

]]>
http://www.dbtutorials.com/advanced/dac-execution-time-vb/feed/ 0
Execution Time using Data Access Component in C# http://www.dbtutorials.com/advanced/dac-execution-time-cs/ http://www.dbtutorials.com/advanced/dac-execution-time-cs/#comments Tue, 17 Sep 2013 19:10:45 +0000 http://dbtutorials.com/?p=548 This tutorial will show you how to create your own Data Access Component and also how to display the time it takes to retrieve data from a SQL database. C# […]

The post Execution Time using Data Access Component in C# appeared first on DB Tutorials | Database Tutorials, Tips, and Articles.

]]>
This tutorial will show you how to create your own Data Access Component and also how to display the time it takes to retrieve data from a SQL database. C# version.

Using Visual Studio.NET 2008, we can build our own Data Access Components to work with and manipulate data. In this tutorial, we will look at how we can do this, and how we can also retrieve the duration of data retrieval, which can be useful when dealing with large amounts of data. For this example, we will be working with a SQL database, and we will be creating our own class to retrieve the data. We will be using the Object Data Source to interact with our class, and a GridView to display the data.

The database we will be working with will have just one table, and three columns – id, name and age. Once created, we will add some sample data to use.

When our database is ready to go, we can start building our class that will retrieve data from our database. The class will have a method to retrieve all records from the database and input into a List. Our class will look something like this:

public class People
{
private static readonly string _connectionString;

private string _name;
private string _age;

public string Name
{
get
{
return _name;
}
set
{
_name = value;
}
}

public string Age
{
get
{
return _age;
}
set
{
_age = value;
}
}

public List<People> GetAll(out long execTime)
{
List<People> results = new List<People>();
SqlConnection con = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand("SELECT Name,Age FROM tblPeople", con);
con.StatisticsEnabled = true;
using (con)
{
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
People newPerson = new People();
newPerson.Name = (string)dr["Name"];
newPerson.Age = (string)dr["Age"];
results.Add(newPerson);
}
}
IDictionary stats = con.RetrieveStatistics();
execTime = (long)stats["ExecutionTime"];
return results;
}

static People()
{
_connectionString = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
}
}

We create a ‘GetAll’ method that uses a List to collect all data from the database. Then, using a loop, we add each record from the database into the results List we created. This is the List that is then returned. We will use this method to select data, using the ObjectDataSource. We can now build our ASPX page like so:

<form id="form1" runat="server">
<asp:GridView ID="GridView1" runat="server" DataSourceID="ObjectDataSource1"
Width="377px" />

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
TypeName="People" SelectMethod="GetAll" OnSelected="ObjectDataSource1_Selected">
<SelectParameters>
<asp:Parameter Name="execTime" Type="Int64" Direction="Output" />
</SelectParameters>
</asp:ObjectDataSource>

<br />
<br />
Time to retrieve data was: <asp:Label ID="lblStatus" runat="server" />.
</form>

Notice that we have assigned the GetAll method to our SelectMethod attribute of our ObjectDataSource, and the TypeName is the name of the Class. We also have a method that fires on the OnSelected event. This means that when the ObjectDataSource selects data (through the class), the following code is processed:

protected void ObjectDataSource1_Selected(object sender, ObjectDataSourceStatusEventArgs e)
{
lblStatus.Text = e.OutputParameters["execTime"].ToString() + "ms";
}

This is where we output the time it took to retrieve the data.
The entire code-behind will look something like this:

using System;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}

protected void ObjectDataSource1_Selected(object sender, ObjectDataSourceStatusEventArgs e)
{
lblStatus.Text = e.OutputParameters["execTime"].ToString() + "ms";
}
}

The entire code of the class is follows:

using System;
using System.Data;
using System.Web.Configuration;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Collections;

/// <summary>
/// Summary description for People
/// </summary>
public class People
{
private static readonly string _connectionString;

private string _name;
private string _age;

public string Name
{
get
{
return _name;
}
set
{
_name = value;
}
}

public string Age
{
get
{
return _age;
}
set
{
_age = value;
}
}

public List<People> GetAll(out long execTime)
{
List<People> results = new List<People>();
SqlConnection con = new SqlConnection(_connectionString);
SqlCommand cmd = new SqlCommand("SELECT Name,Age FROM tblPeople", con);
con.StatisticsEnabled = true;
using (con)
{
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
People newPerson = new People();
newPerson.Name = (string)dr["Name"];
newPerson.Age = (string)dr["Age"];
results.Add(newPerson);
}
}
IDictionary stats = con.RetrieveStatistics();
execTime = (long)stats["ExecutionTime"];
return results;
}

static People()
{
_connectionString = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
}
}

Download Source Files

The post Execution Time using Data Access Component in C# appeared first on DB Tutorials | Database Tutorials, Tips, and Articles.

]]>
http://www.dbtutorials.com/advanced/dac-execution-time-cs/feed/ 0
Implementing Membership Functionality to a Website http://www.dbtutorials.com/advanced/membershipfunctionality/ http://www.dbtutorials.com/advanced/membershipfunctionality/#comments Tue, 17 Sep 2013 13:36:46 +0000 http://dbtutorials.com/?p=437 This tutorial will walk you through building membership functionality and login controls into your website to secure your data. What You Will Need For This Tutorial Microsoft Visual Studio 2010 […]

The post Implementing Membership Functionality to a Website appeared first on DB Tutorials | Database Tutorials, Tips, and Articles.

]]>
This tutorial will walk you through building membership functionality and login controls into your website to secure your data.

What You Will Need For This Tutorial

  • Microsoft Visual Studio 2010 or Microsoft Visual Web Developer 2010 Express
  • A computer with an internet connection and Microsoft Windows 7 operating system
  • A basic understanding of the web and some programming experience would be helpful

Topics That Will Be Covered

  • Overview of membership functionality and role based security
  • Overview of ASP.NET Security administration tools
  • Setting up and implementing a membership login control with users

Start A New Website

Open Visual Web Developer 2010 and in the file menu select the new website option from the dropdown menu or on the start page.

Select Empty ASP.NET Website from the list in the center. This will start your project without the default webpage.

We want to add some pages to our website now. Right click on the name of the website in the solution explorer and select Add New Item.

When the Add New Item display opens, select Web Form from the installed templates list.

In the name box type Home.aspx and make sure the place code in separate file box is checked, and the select master page box is unchecked.

Click Add.

For our website, we are going to need 3 more pages to complete the setup. Repeat steps 1 through 6 three more times to create following pages: Login.aspx, PasswordRecovery.aspx, and SignUp.aspx.

We have our pages created so let’s switch gears and take a look at some of the aspects of membership functionality.

Overview of Membership Functionality

In Visual Studio 2010 and Visual Web Developer 2010, implementing membership functionality using the membership class through the ASP.NET web application administration page. Creating a membership control is very easy and requires almost no coding. Here are a few of the advantages of adding a membership control to your website:

Limited Access to pages: Using the membership control lets you create sections of your website that are “off limits” to the general public by implementing a password login accompanied by a customer account. This is seen everywhere on the internet in websites such as social networking sites that require a fee to join. This control can also be used to make the site or sections of the site limited to a certain group of people. The information on these sites is governed by the membership control.

Role based security: Role based security is based on the concept of authenticating and authorizing a user for access and then defining that access based on the user’s role. This is accomplished through the login and password method. Without a stored login and password, the user can’t “identify” who they are. When a user has successfully logged in, his or her privileges or level of access to the website is based on the role that was created for the login.

Now that we have a basic understanding of the membership functionality we can look at the ASP.NET security administration tools that allow us to create and manage the user accounts for our website.

Overview of The ASP.NET Security Administration Tools

In the toolbar click on the website menu, then click on ASP.NET configuration.

This will open your browser and the home page ASP.NET Application Administration will appear as seen above.

Click on the security tab near the top of the page to navigate to the security administrative tools.

This page gives you access to the tools used to create, manage, or delete user roles for the website you are currently creating.

In the first column to the left under Users, click the hyperlink Select authentication type. Be sure the top selection From the internet is selected.

This indicates that the website will be accessed from the public internet as opposed to a local area network.

In the middle column under Roles, click the Enable roles link and the page will refresh with a new link in the list titled Create or Manage roles.

Now that the roles have been enabled, click the Create or Manage role link.

In the new role name box, type Basic User.

This function lets you create and name a new role. The name can be anything and based on the type of access you want to give the user, you can name the role accordingly (Example: Administrator). For the purposes of this tutorial we will keep it simple with the name “Basic User”.

Click Add Role.

On the bottom right corner of the page, click the back button and then click the create user link in the Users column (far left).
Now that a role has been created we need to create a user for that role.

Fill out the form with a mock user name, password, e-mail, and security question.

It is important to note that the password must be “strong”. This means that there must be a combination of at least 7 or more letters, numbers and at least one non-alphanumeric symbol (Example: -, +, _). For this tutorial any email address will work as the email address will not be used for anything in this tutorial. The security question can be anything, just make sure you remember both the security question and password, because you will need them to access the account you just created shortly.

Click on the Basic User box to check it. This is the name of the role you just created. This will connect the user account with the role.

Click Create user. Click Continue in the success page.

Notice that when you click the continue button it takes you back to the create User page. You can add as many users as you need using this method. There is also a function that we will build into our website shortly that will let us create a new account from a webpage on that site.

We have a user account and role created for our website so now it is time to set up our website so it will accept or decline access based on the login information.

Setting Up and Implementing A Membership Login Control With Users

First we will start with a login control and attach a link on that control to the password recovery page. Open the login page you created earlier in the tutorial and switch to design view.

In the Toolbox under the Login list click on the login control and drag it into the page. On the login control, there is a small menu box in the upper right of the control. Click the box to display the login tasks menu.

The tasks menu for each control gives you different commands and options. Click Auto Format and then click Professional from the schemes menu then click Apply.
This option allows you to change the appearance of the control based on a pre-configured list of design styles.

With the login control still selected go to the properties panel and find the PasswordRecovery function (under links) and click the ellipsis button to the right.

This creates a link on the login controller that directs the user to the password recovery page (which we will build shortly).

In the function just above PasswordRecoveryUrl type the text Forgot Password? in the empty box to the right of the PasswordRecoveryText function.

Save your work and open the PasswordRecovery.aspx Page. Make sure you are in design view and in the Toolbox under the login menu, click on the PasswordRecovery control and drag it onto the page.

On the PasswordRecovery control, there is a small menu box in the upper right of the control. Click the box to display the PasswordRecovery tasks menu.
Notice that this menu contains a dropdown list called “views”. There are three views in the list. These views are the different states in which the PasswordRecovery control will appear on the page.

Click Auto Format and then click Professional from the schemes menu then click Apply. Be sure to complete this option for each view so the theme is consistent throughout the user experience.

Save your work and open the SignUp.aspx page. Be sure you are in design view and in the Toolbox under the Login menu, click on the CreateUserWizard control and drag it onto the page.

This control is set up so a user can create a new account from the internet. This is a very basic setup for this control and can be modified to accept and require different kinds of information (such as payment information) to create an account. For now we will leave it with the default settings.

On the CreateUserWizard control, there is a small menu box in the upper right of the control. Click the box to display the CreateUserWizard tasks menu.

Click Auto Format and then click Professional from the schemes menu then click Apply. Be sure to complete this option for both views so the theme is consistent throughout the user experience.

With the CreateUserWizard selected, go to the properties panel and click on the blank box to the right of the ContinueDestinationPageUrl (under behavior) then click on the ellipsis.
This provides an automatic link back to the login page after the user has created the account.

Select the login.aspx page and click Ok.

Up to this point we have created all of the membership login controls for the website. Now we need a page to tie everything together.

Save your work and open the Home.aspx page. Switch to source view and copy and paste the following code onto the page just under the closing head tag.

<body>
<form id="form1" runat="server">
<div>
</div>
Login Status:<br />
<br />
<asp:LoginStatus ID="LoginStatus1" runat="server" />
<br />
<asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl="~/SignUp.aspx">Sign up for a New Account!</asp:HyperLink>
<br />
<br />
Login Name:<br />
<br />
<asp:LoginName ID="LoginName1" runat="server" />
<br />
<br />
Login View:<br />
<br />
<asp:LoginView ID="LoginView1" runat="server">
<AnonymousTemplate>
You are currently logged out. 
</AnonymousTemplate>
<LoggedInTemplate>
Congradulations! You are logged in!
</LoggedInTemplate>
</asp:LoginView>
</form>
</body>

This is a simple page layout that contains a LoginView control (also accessible from the Toolbox under the Login menu). The login view control displays the information based on the user’s status. For this tutorial you will notice that the LoginView control has two different views: Logged in and not logged in.

In the solution explorer right click on the Home.aspx page and select Set as Start Page.

Debug or run the application.

You will notice that you are not logged in based on the text in the page. Click the login link (provided by the code) and enter the information you used to create your user account earlier. Notice that you can also navigate using the “forgot password” link to retrieve your password information. Sense we have not set up an email, this function will not work, but you should have a general idea of how it works at this point. You can also create a new account if you forgot the one you created by clicking on the “sign up for a new account” link in the Home.aspx page. So there you have it. Although this is a very basic set up, the applications for this setup are very broad. This should give you a solid starting point.

Download Source Files

The post Implementing Membership Functionality to a Website appeared first on DB Tutorials | Database Tutorials, Tips, and Articles.

]]>
http://www.dbtutorials.com/advanced/membershipfunctionality/feed/ 0
Updating Custom Object in SQL Database with VB.NET http://www.dbtutorials.com/advanced/updating-custom-object-vb/ http://www.dbtutorials.com/advanced/updating-custom-object-vb/#comments Tue, 17 Sep 2013 13:28:10 +0000 http://dbtutorials.com/?p=435 This tutorial follows on from the Inserting Custom Object and will show you how to build on that class to add the functionality of Updating Objects in VB.NET Following on […]

The post Updating Custom Object in SQL Database with VB.NET appeared first on DB Tutorials | Database Tutorials, Tips, and Articles.

]]>
This tutorial follows on from the Inserting Custom Object and will show you how to build on that class to add the functionality of Updating Objects in VB.NET

Following on from the Updating Custom Object tutorial, we will look into how we can add the Update functionality, allowing us to edit an object and then commit changes to the database. By using objects, we could first make the changes and then decide whether or not to save these changes to the database.
However, in this example, we will use the class of the previous tutorial to add a method to update the object. If you have not followed that tutorial yet, it is recommended. You can find it here.

Once you have the project opened up, open up the Car.vb from App_Code. There are two things we are going to add: A constructor that takes an int parameter; and an UpdateMethod that takes a Car object parameter.

Let’s go ahead and add the constructor first. This will be different from the constructor we already have; it will be more like a method, without returning anything. The constructor will look something like this:

''' <summary>
''' Contructor for Car object, set the Car object from ID passed to it.
''' </summary>
''' <param name="theCarID">Int32 value of the ID of the Car to get.</param>
Public Sub New(ByVal theCarID As Integer)
Dim connection As New SqlConnection(WebConfigurationManager.ConnectionStrings("ConnectionString").ToString())
Try
Dim cmd As SqlCommand
cmd = New SqlCommand("sp_GetCarByID", connection)
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.AddWithValue("@CarID", theCarID)

connection.Open()
Dim objReader As SqlDataReader = cmd.ExecuteReader()

Do While objReader.Read()
SetObjectData(objReader)
Loop

objReader.Close()
connection.Close()
Catch
connection.Close()
End Try
End Sub

Notice here we are referencing a new Stored Procedure, one that will retrieve the record from the database that matches the ID parameter we pass to it. The Stored Procedure will look something like this:

CREATE PROCEDURE dbo.sp_GetCarByID

@CarID int

AS

SELECT * FROM Cars WHERE ID = @CarID

We also need another Stored Procedure – one to Update the Car object we pass. It should look something like this:

CREATE PROCEDURE dbo.sp_UpdateCar

@ID int,
@Make varchar(50),
@Model varchar(50)

AS

UPDATE Cars
SET Make = @Make, Model = @Model
WHERE ID = @ID

Now we have our Stored Procedures, we can finish up with the class by adding the Update method:

''' <summary>
''' Updates a Car in the database.
''' </summary>
''' <returns>Returns Boolean value indicating if the method finished without errors.</returns>
Public Shared Function UpdateCar(ByVal theCar As Car) As Boolean
Dim connection As New SqlConnection(WebConfigurationManager.ConnectionStrings("ConnectionString").ToString())
Try
Dim cmd As New SqlCommand("sp_UpdateCar", connection)
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.AddWithValue("@ID", theCar.ID)
cmd.Parameters.AddWithValue("@Make", theCar.Make)
cmd.Parameters.AddWithValue("@Model", theCar.Model)

connection.Open()
cmd.ExecuteNonQuery()
connection.Close()
Return True
Catch
connection.Close()
Return False
End Try

End Function

Notice we are passing in a Car object, and then using its Properties to pass to the Stored Procedure. We return true if the Update was successful.
We are now done with modifying the class. We can move back to our ASPX page and make changes to the Repeater. What we are going to do is to display all the records in a Repeater, and provide a LinkButton for record to be edited. We will also provide two textboxes to edit the Make and Model, and then a button to commit the changes. The Repeater will look something like this:

<asp:Repeater ID="repeater_Cars" runat="server">
<HeaderTemplate>
<table width="350"><tr>
<td><strong>ID</strong></td>
<td><strong>Make</strong></td>
<td><strong>Model</strong></td>
<td><strong>Action</strong></td>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td><%# Eval("ID") %></td>
<td><%# Eval("Make") %></td>
<td><%# Eval("Model") %></td>
<td><asp:LinkButton ID="lnk_Edit" runat="server" CommandArgument='<%# Eval("ID") %>'
CommandName="EditCar" Text="Edit" /></td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</asp:Repeater>

Notice we are using the ItemCommand event of the Repeater to handle the edits. By doing this, we are able to set a CommandName and CommandArgument for each LinkButton that is rendered, for each object in the collection. We will use the ID for the CommandArgument, because the ID is the unique identifier. Let’s finish the form and add our textboxes and button:

ID: <asp:Literal ID="lit_ID" runat="server" /><br />
Make: <asp:TextBox ID="fld_Make" runat="server" /><br />
Model: <asp:TextBox ID="fld_Model" runat="server" /><br />
<asp:Button ID="btn_Update" runat="server" Text="Update" Enabled="false" />

We also include a Literal control to display the ID, as this is not editable. We disable the button on load, and will enable when an edit is clicked.
Moving to the code-behind, we will first need to make sure we are referencing our custom class:

Imports DBTutorials.Car

Next, we bind the Repeater on Page_Load:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
If (Not IsPostBack) Then
BindRepeater()
End If
End Sub

Protected Sub BindRepeater()
repeater_Cars.DataSource = Car.GetAllCars()
repeater_Cars.DataBind()
End Sub

Next, we create the OnItemCommand handler. This can be added manually, or by clicking on the Repeater in Design view, going to Properties and double-click on the ItemCommand Event field.
This first checks to see if the correct command is being called, and then it converts the CommandArgument to the ID of the Car object, which it then retrieves from the database using our new constructor, and then fills in the literal and textboxes as well as enabling the button.

Finally, we add the handler for the button click event:

Protected Sub btn_Update_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_Update.Click
If (Not String.IsNullOrEmpty(fld_Make.Text)) AndAlso (Not String.IsNullOrEmpty(fld_Model.Text)) Then
Dim carToEdit As New Car(Convert.ToInt16(lit_ID.Text))
carToEdit.Make = fld_Make.Text
carToEdit.Model = fld_Model.Text

If Car.UpdateCar(carToEdit) Then
lit_ID.Text &= " [Updated]"
fld_Make.Text = ""
fld_Model.Text = ""

BindRepeater()
btn_Update.Enabled = False
End If
End If
End Sub

Now if this application is run, you will see that all Cars are displayed from the collection straight away. We are then able to click on the Edit link of each object, and edit them right on the page. We can even improve the functionality of this by including a ScriptManager and UpdatePanel. The editing will then seem instantaneous.

Download Source Files

The post Updating Custom Object in SQL Database with VB.NET appeared first on DB Tutorials | Database Tutorials, Tips, and Articles.

]]>
http://www.dbtutorials.com/advanced/updating-custom-object-vb/feed/ 0
Updating Custom Object in SQL Database with C# http://www.dbtutorials.com/advanced/updating-custom-object-cs/ http://www.dbtutorials.com/advanced/updating-custom-object-cs/#comments Tue, 17 Sep 2013 13:24:00 +0000 http://dbtutorials.com/?p=433 This tutorial follows on from the Inserting Custom Object and will show you how to build on that class to add the functionality of Updating Objects in C# Following on […]

The post Updating Custom Object in SQL Database with C# appeared first on DB Tutorials | Database Tutorials, Tips, and Articles.

]]>
This tutorial follows on from the Inserting Custom Object and will show you how to build on that class to add the functionality of Updating Objects in C#

Following on from the Updating Custom Object tutorial, we will look into how we can add the Update functionality, allowing us to edit an object and then commit changes to the database. By using objects, we could first make the changes and then decide whether or not to save these changes to the database.
However, in this example, we will use the class of the previous tutorial to add a method to update the object. If you have not followed that tutorial yet, it is recommended. You can find it here.

Once you have the project opened up, open up the Car.cs from App_Code. There are two things we are going to add: A constructor that takes an int parameter; and an UpdateMethod that takes a Car object parameter.

Let’s go ahead and add the constructor first. This will be different from the constructor we already have; it will be more like a method, without returning anything. The constructor will look something like this:

/// <summary>
/// Contructor for Car object, set the Car object from ID passed to it.
/// </summary>
/// <param name="theCarID">Int32 value of the ID of the Car to get.</param>
public Car(int theCarID)
{
SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
try
{
SqlCommand cmd;
cmd = new SqlCommand("sp_GetCarByID", connection);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@CarID", theCarID);

connection.Open();
SqlDataReader objReader = cmd.ExecuteReader();

while (objReader.Read())
{
SetObjectData(objReader);
}

objReader.Close();
connection.Close();
}
catch
{
connection.Close();
}
}

Notice here we are referencing a new Stored Procedure, one that will retrieve the record from the database that matches the ID parameter we pass to it. The Stored Procedure will look something like this:

CREATE PROCEDURE dbo.sp_GetCarByID

@CarID int

AS

SELECT * FROM Cars WHERE ID = @CarID

We also need another Stored Procedure – one to Update the Car object we pass. It should look something like this:

CREATE PROCEDURE dbo.sp_UpdateCar

@ID int,
@Make varchar(50),
@Model varchar(50)

AS

UPDATE Cars
SET Make = @Make, Model = @Model
WHERE ID = @ID

Now we have our Stored Procedures, we can finish up with the class by adding the Update method:

/// <summary>
/// Updates a Car in the database.
/// </summary>
/// <returns>Returns Boolean value indicating if the method finished without errors.</returns>
public static bool UpdateCar(Car theCar)
{
SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
try
{
SqlCommand cmd = new SqlCommand("sp_UpdateCar", connection);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@ID", theCar.ID);
cmd.Parameters.AddWithValue("@Make", theCar.Make);
cmd.Parameters.AddWithValue("@Model", theCar.Model);

connection.Open();
cmd.ExecuteNonQuery();
connection.Close();
return true;
}
catch
{
connection.Close();
return false;
}
}

Notice we are passing in a Car object, and then using its Properties to pass to the Stored Procedure. We return true if the Update was successful.
We are now done with modifying the class. We can move back to our ASPX page and make changes to the Repeater. What we are going to do is to display all the records in a Repeater, and provide a LinkButton for record to be edited. We will also provide two textboxes to edit the Make and Model, and then a button to commit the changes. The Repeater will look something like this:

<asp:Repeater ID="repeater_Cars" runat="server" OnItemCommand="repeater_Cars_ItemCommand">
<HeaderTemplate>
<table width="350"><tr>
<td><strong>ID</strong></td>
<td><strong>Make</strong></td>
<td><strong>Model</strong></td>
<td><strong>Action</strong></td>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td><%# Eval("ID") %></td>
<td><%# Eval("Make") %></td>
<td><%# Eval("Model") %></td>
<td><asp:LinkButton ID="lnk_Edit" runat="server" CommandArgument='<%# Eval("ID") %>'
CommandName="EditCar" Text="Edit" /></td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</asp:Repeater>

Notice we are using the ItemCommand event of the Repeater to handle the edits. By doing this, we are able to set a CommandName and CommandArgument for each LinkButton that is rendered, for each object in the collection. We will use the ID for the CommandArgument, because the ID is the unique identifier. Notice also, that we have a handler for the Repeater’s ItemCommand, which we will show you shortly. But first, let’s finis the form and add our textboxes and button:

ID: <asp:Literal ID="lit_ID" runat="server" /><br />
Make: <asp:TextBox ID="fld_Make" runat="server" /><br />
Model: <asp:TextBox ID="fld_Model" runat="server" /><br />
<asp:Button ID="btn_Update" runat="server" Text="Update" OnClick="btn_Update_OnClick" Enabled="false" />

e also include a Literal control to display the ID, as this is not editable. We disable the button on load, and will enable when an edit is clicked.
Moving to the code-behind, we will first need to make sure we are referencing our custom class:

using DBTutorials.Car;

Next, we bind the Repeater on Page_Load:

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindRepeater();
}
}

protected void BindRepeater()
{
repeater_Cars.DataSource = Car.GetAllCars();
repeater_Cars.DataBind();
}

Next, we create the OnItemCommand handler. This can be added manually, or by clicking on the Repeater in Design view, going to Properties and double-click on the ItemCommand Event field.
This first checks to see if the correct command is being called, and then it converts the CommandArgument to the ID of the Car object, which it then retrieves from the database using our new constructor, and then fills in the literal and textboxes as well as enabling the button.

Finally, we add the handler for the button click event:

protected void btn_Update_OnClick(object sender, EventArgs e)
{
if (!String.IsNullOrEmpty(fld_Make.Text) && !String.IsNullOrEmpty(fld_Model.Text))
{
Car carToEdit = new Car(Convert.ToInt16(lit_ID.Text));
carToEdit.Make = fld_Make.Text;
carToEdit.Model = fld_Model.Text;

if (Car.UpdateCar(carToEdit))
{
lit_ID.Text += " [Updated]";
fld_Make.Text = "";
fld_Model.Text = "";

BindRepeater();
btn_Update.Enabled = false;
}
}
}

Now if this application is run, you will see that all Cars are displayed from the collection straight away. We are then able to click on the Edit link of each object, and edit them right on the page. We can even improve the functionality of this by including a ScriptManager and UpdatePanel. The editing will then seem instantaneous.

Download Source Files

The post Updating Custom Object in SQL Database with C# appeared first on DB Tutorials | Database Tutorials, Tips, and Articles.

]]>
http://www.dbtutorials.com/advanced/updating-custom-object-cs/feed/ 0
Inserting Custom Object to SQL Database in VB.NET http://www.dbtutorials.com/advanced/custom-object-inserting-vb/ http://www.dbtutorials.com/advanced/custom-object-inserting-vb/#comments Tue, 17 Sep 2013 13:16:58 +0000 http://dbtutorials.com/?p=431 This tutorial will show you how to use Custom Objects to Insert to a SQL Server Database using VB.NET In this tutorial, we are going to look at how we […]

The post Inserting Custom Object to SQL Database in VB.NET appeared first on DB Tutorials | Database Tutorials, Tips, and Articles.

]]>
This tutorial will show you how to use Custom Objects to Insert to a SQL Server Database using VB.NET

In this tutorial, we are going to look at how we can create our own class to represent database data, and how we can use that object to insert new data to the database. ASP.NET is object-orientated and allows us to create our own objects to use, which can make it easier for us to work with and manipulate data. When working with databases, we can create custom objects that directly relate to our database structure. For instance, instead of working with DataTables, where we have to reference the data by the row number, we can create a class that represents our data and reference by Property names.

We will be creating an object Car, and storing a number of different Make and Models into a SQL Server Database. We will demonstrate how to use this class to create new objects and insert them into the database. We will also create a collection class of the Car object so that we can group them together, and display them with a Repeater control.

To begin, start a new web application in Visual Studio, then right-click the App_Data folder in Solution Explorer and choose to Add New Item.. SQL Server Database. You should see that the database opens up in the Server Explorer window. Right-click the Tables folder and choose Add New Table. We will create three columns: ID, Make, and Model. We will use the data types int, varchar(50), and varchar(50) respectively. We also want to make the ID the Primary Key, and the Primary Specification (in the Properties). Close and save this table when you’re done, and name it Cars.

Now right-click the table you just created in Server Explorer and choose to Show Table Data. Add one or two entries for sample data. Now go into your Web.config and add the following ConnectionString. Once done, we will work on our class.

<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Cars.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
</connectionStrings>

We need to create a representation of this table data. Right-click your project in Solution Explorer, then choose to Add ASP.NET Folder > App_Code. Then right-click the App_Code folder and choose to Add New Item.. Class. Name it Car.vb

The first thing we will do is to wrap the new class in a namespace. This is useful to do especially when we have a lot of code, and multiple classes we can group together in one namespace. We also want to add a few references. Let’s add System.Data, System.Data.SqlClient, System.Web.Configuration, and System.Collections. We should have something that looks like this:

Imports Microsoft.VisualBasic

Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration
Imports System.Collections

Namespace DBTutorials.Car
''' <summary>
''' Summary description for Car
''' </summary>
Public Class Car
Public Sub New()
End Sub
End Class
End Namespace

All we are presented with is a default constructor, which when called, will instantiate the class. What we will do next is build out the Properties. We will add these after the constructor:

#Region "properties"

''' <summary>
''' Gets or sets Car ID. [Default value is 0].
''' </summary>
Public Property ID() As Int32
Get
Return _ID
End Get
Set(ByVal value As Int32)
_ID = value
End Set
End Property
Private _ID As Int32 = 0

''' <summary>
''' Gets or sets the Make of the Car. [Default value is ''].
''' </summary>
Public Property Make() As String
Get
Return _Make
End Get
Set(ByVal value As String)
_Make = value
End Set
End Property
Private _Make As String = ""

''' <summary>
''' Gets or sets the Model of the Car. [Default value is ''].
''' </summary>
Public Property Model() As String
Get
Return _Model
End Get
Set(ByVal value As String)
_Model = value
End Set
End Property
Private _Model As String = ""

#End Region

Notice we declare the properties as public so that they are accessible beyond class level. We also set the default values and data type of each, which mirrors our Cars table structure.

Now that we have our Properties set, we can move onto the Methods. In this example, the only methods we will create are:

SetObjectData – used to set the object Car from a SqlDataReader;
InsertCar – used to insert a Car object into the database and return its ID;
GetAllCars – used to retrieve a collection of all Car objects from the database.

First, we will add the SetObjectData method and also create a second constructor, which will use the method to build the Car object:

#Region "methods"

''' <summary>
''' Sets the Car object using the SqlDataReader object passed to it.
''' </summary>
''' <param name="theObjReader">SqlDataReader object that contains DB info to set the Car object.</param>
Private Sub SetObjectData(ByVal theObjReader As SqlDataReader)
Try
Me._ID = Convert.ToInt32(theObjReader("ID"))
Me._Make = theObjReader("Make").ToString()
Me._Model = theObjReader("Model").ToString()
Catch

End Try
End Sub
#End Region

Notice that this method is private, so it can only be called within the class.
Next, we add the second constructor:

''' <summary>
''' Contructor for Car object, sets the Car object from the SqlDataReader passed to it.
''' </summary>
''' <param name="theObjReader">SqlDataReader value that contains the DB information to set </param>
Public Sub New(ByVal theObjReader As SqlDataReader)
SetObjectData(theObjReader)
End Sub

Now that we have a constructor that can build an object from a SqlDataReader we pass to it, we need to create a method to pass it a SqlDataReader.
But first, let’s go ahead and add the Stored Procedures we will be using for the next two methods. Open up the Server Explorer window and right-click the Stored Procedures folder and choose Add New Stored Procedure. First, add GetAllCars:

CREATE PROCEDURE dbo.sp_GetAllCars

AS

SELECT * FROM Cars

Next, add InsertCar:

CREATE PROCEDURE dbo.sp_InsertCar

@Make varchar(50),
@Model varchar(50)

AS

INSERT INTO Cars (Make, Model)
VALUES (@Make, @Model)

SELECT SCOPE_IDENTITY()

We are using the SELECT SCOPE_IDENTITY() to return the ID of the newly created record. We will use this to find out if the add was successful.
Now before we can add the GetAllCars method, we will need to create a Collection Class for it to use. So let’s get the InsertCar method in first, which will take a parameter of the Car object. Add the method below SetObjectData. We want to make use of the Connection String in the Web.config, and also we make sure to return the ID of the new record. Our method will look like this:

''' <summary>
''' Inserts a Car object into the database.
''' </summary>
''' <param name="theCar">Car object to insert.</param>
''' <returns>Returns Int32 value of the new Car ID.</returns>
Public Shared Function InsertCar(ByVal theCar As Car) As Int32
Dim newCarID As Int32 = 0
Dim connection As New SqlConnection(WebConfigurationManager.ConnectionStrings("ConnectionString").ToString())
Try
Dim cmd As New SqlCommand("sp_InsertCar", connection)
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.AddWithValue("@Make", theCar.Make)
cmd.Parameters.AddWithValue("@Model", theCar.Model)

connection.Open()
newCarID = Convert.ToInt32(cmd.ExecuteScalar())
connection.Close()
Catch
connection.Close()
End Try

Return newCarID
End Function

In order for us to return all the Cars in the database as objects, we are going to have to create a Collection class of the Car object. To do this, we inherit from the CollectionBase class. The Collection class is defined within the namespace, but outside of the Car class:

#Region "Collection Classes"

''' <summary>
''' Represents a collection of Car objects
''' </summary>
<Serializable()> _
Public Class Cars
Inherits CollectionBase
Public Property TotalRecords() As Integer
Get
Return _TotalRecords
End Get
Set(ByVal value As Integer)
_TotalRecords = value
End Set
End Property
Protected _TotalRecords As Integer = 0

''' <summary>
''' Adds a new Car to the collection
''' </summary>
''' <param name="theCar">The Car to add to the collection</param>
Public Function Add(ByVal theCar As Car) As Integer
Me.TotalRecords += 1
Return List.Add(theCar)
End Function

''' <summary>
''' Inserts a new Car to the collection, at the specified index
''' </summary>
''' <param name="index">The Index to insert the Car into</param>
''' <param name="theCar">The Car to insert into the collection</param>
Public Sub Insert(ByVal index As Int32, ByVal theCar As Car)
List.Insert(index, theCar)
End Sub

''' <summary>
''' Removes a Car from the collection
''' </summary>
''' <param name="theCar">The Car to remove from the collection</param>
Public Sub Remove(ByVal theCar As Car)
List.Remove(theCar)
End Sub

''' <summary>
''' Checks to see if the Car object exists in the collection
''' </summary>
''' <param name="theCar">The Car to search for in the collection</param>
''' <returns>Returns true if the Car exists in the collection</returns>
Public Function Contains(ByVal theCar As Car) As Boolean
Return List.Contains(theCar)
End Function

''' <summary>
''' Returns the index of the Car object in the collection (if it exists)
''' </summary>
''' <param name="theCar">The Car to search for in the collection</param>
''' <returns>Returns the int index of the Car object in the collection</returns>
Public Function IndexOf(ByVal theCar As Car) As Integer
Return List.IndexOf(theCar)
End Function

Public Sub CopyTo(ByVal array() As Car, ByVal index As Integer)
List.CopyTo(array, index)
End Sub

Default Public Property Item(ByVal index As Integer) As Car
Get
Return CType(List(index), Car)
End Get
Set(ByVal value As Car)
List(index) = value
End Set
End Property
End Class

#End Region

Finally for our class, we add the GetAllCars method, which will return the collection we just defined:

''' <summary>
''' Gets all the Cars in the database.
''' </summary>
''' <returns>Returns Cars Collection that contains all Cars.</returns>
Public Shared Function GetAllCars() As Cars
Dim CarsCollection As New Cars()
Dim connection As New SqlConnection(WebConfigurationManager.ConnectionStrings("ConnectionString").ToString())
Try
Dim cmd As New SqlCommand("sp_GetAllCars", connection)
cmd.CommandType = CommandType.StoredProcedure

connection.Open()
Dim objReader As SqlDataReader = cmd.ExecuteReader()
Do While objReader.Read()
Dim newCar As New Car(objReader)
CarsCollection.Add(newCar)
Loop
objReader.Close()
connection.Close()
Catch
connection.Close()
End Try

Return CarsCollection
End Function

We grab all of the records from the database, and then loop through each one, adding them to a collection, which we eventually return.

Now we can move onto our ASPX page and build the front-end. All we need is a Repeater to display all the objects we will retrieve in the collection, and then two textboxes and a button to add new objects to the database.
Our ASPX page should look something like this:

Make: <asp:TextBox ID="fld_Make" runat="server" /><br />
Model: <asp:TextBox ID="fld_Model" runat="server" /><br />
<asp:Button ID="btn_Add" runat="server" Text="Add" OnClick="btn_Add_OnClick" />
<br />
<br />
<asp:Repeater ID="repeater_Cars" runat="server">
<HeaderTemplate>
<table width="350"><tr>
<td><strong>ID</strong></td>
<td><strong>Make</strong></td>
<td><strong>Model</strong></td>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td><%# Eval("ID") %></td>
<td><%# Eval("Make") %></td>
<td><%# Eval("Model") %></td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</asp:Repeater>

We are displaying all of the properties of the Car object with the repeater. Using Eval, we reference the Property names of the object, as we will be binding the collection to the Repeater in the code-behind. Moving to the code-behind, we will bind the repeater on Page_Load, using the GetAllCars method. First, we need to add a reference to the class we just created:

Imports DBTutorials.Car

Next, we bind the Repeater on page load with the Collection we created:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If (Not IsPostBack) Then
BindRepeater()
End If
End Sub

Protected Sub BindRepeater()
repeater_Cars.DataSource = Car.GetAllCars()
repeater_Cars.DataBind()
End Sub

Finally, on the button click, we want to create a new object from the two text fields, then add that to the database, and update the Repeater to display all Cars:

Protected Sub btn_Add_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_Add.Click
If (Not String.IsNullOrEmpty(fld_Make.Text)) AndAlso (Not String.IsNullOrEmpty(fld_Model.Text)) Then
Dim newCar As New Car()
newCar.Make = fld_Make.Text
newCar.Model = fld_Model.Text

If Car.InsertCar(newCar) > 0 Then
fld_Make.Text = ""
fld_Model.Text = ""
fld_Make.Focus()

BindRepeater()
End If
End If
End Sub

Download Source Files

The post Inserting Custom Object to SQL Database in VB.NET appeared first on DB Tutorials | Database Tutorials, Tips, and Articles.

]]>
http://www.dbtutorials.com/advanced/custom-object-inserting-vb/feed/ 0
Inserting Custom Object to SQL Database in C# http://www.dbtutorials.com/advanced/custom-object-inserting-cs/ http://www.dbtutorials.com/advanced/custom-object-inserting-cs/#comments Tue, 17 Sep 2013 13:05:36 +0000 http://dbtutorials.com/?p=428 This tutorial will show you how to use Custom Objects to Insert to a SQL Server Database using C# In this tutorial, we are going to look at how we […]

The post Inserting Custom Object to SQL Database in C# appeared first on DB Tutorials | Database Tutorials, Tips, and Articles.

]]>
This tutorial will show you how to use Custom Objects to Insert to a SQL Server Database using C#

In this tutorial, we are going to look at how we can create our own class to represent database data, and how we can use that object to insert new data to the database. ASP.NET is object-orientated and allows us to create our own objects to use, which can make it easier for us to work with and manipulate data. When working with databases, we can create custom objects that directly relate to our database structure. For instance, instead of working with DataTables, where we have to reference the data by the row number, we can create a class that represents our data and reference by Property names.

We will be creating an object Car, and storing a number of different Make and Models into a SQL Server Database. We will demonstrate how to use this class to create new objects and insert them into the database. We will also create a collection class of the Car object so that we can group them together, and display them with a Repeater control.

To begin, start a new web application in Visual Studio, then right-click the App_Data folder in Solution Explorer and choose to Add New Item.. SQL Server Database. You should see that the database opens up in the Server Explorer window. Right-click the Tables folder and choose Add New Table. We will create three columns: ID, Make, and Model. We will use the data types int, varchar(50), and varchar(50) respectively. We also want to make the ID the Primary Key, and the Primary Specification (in the Properties). Close and save this table when you’re done, and name it Cars.

Now right-click the table you just created in Server Explorer and choose to Show Table Data. Add one or two entries for sample data. Now go into your Web.config and add the following ConnectionString. Once done, we will work on our class.

<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Cars.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
</connectionStrings>

If you need help with the Connection String, see ConnectionStrings.com

We need to create a representation of this table data. Right-click your project in Solution Explorer, then choose to Add ASP.NET Folder > App_Code. Then right-click the App_Code folder and choose to Add New Item.. Class. Name it Car.cs

The first thing we will do is to wrap the new class in a namespace. This is useful to do especially when we have a lot of code, and multiple classes we can group together in one namespace. We also want to add a few references. Let’s add System.Data, System.Data.SqlClient, System.Web.Configuration, and System.Collections. We should have something that looks like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Collections;

/// <summary>
/// Summary description for Car
/// </summary>
namespace DBTutorials.Car
{
/// <summary>
/// Summary description for Car
/// </summary>
public class Car
{
public Car()
{
}
}
}

All we are presented with is a default constructor, which when called, will instantiate the class. What we will do next is build out the Properties. We will add these after the constructor:

#region properties

/// <summary>
/// Gets or sets Car ID. [Default value is 0].
/// </summary>
public Int32 ID
{
get
{
return _ID;
}
set
{
_ID = value;
}
}
private Int32 _ID = 0;

/// <summary>
/// Gets or sets the Make of the Car. [Default value is ''].
/// </summary>
public String Make
{
get
{
return _Make;
}
set
{
_Make = value;
}
}
private String _Make = "";

/// <summary>
/// Gets or sets the Model of the Car. [Default value is ''].
/// </summary>
public String Model
{
get
{
return _Model;
}
set
{
_Model = value;
}
}
private String _Model = "";

#endregion

Notice we declare the properties as public so that they are accessible beyond class level. We also set the default values and data type of each, which mirrors our Cars table structure.

Now that we have our Properties set, we can move onto the Methods. In this example, the only methods we will create are:

SetObjectData – used to set the object Car from a SqlDataReader;
InsertCar – used to insert a Car object into the database and return its ID;
GetAllCars – used to retrieve a collection of all Car objects from the database.

First, we will add the SetObjectData method and also create a second constructor, which will use the method to build the Car object:

#region methods

/// <summary>
/// Sets the Car object using the SqlDataReader object passed to it.
/// </summary>
/// <param name="theObjReader">SqlDataReader object that contains DB info to set the Car object.</param>
private void SetObjectData(SqlDataReader theObjReader)
{
try
{
this._ID = Convert.ToInt32(theObjReader["ID"]);
this._Make = theObjReader["Make"].ToString();
this._Model = theObjReader["Model"].ToString();
}
catch
{ }
}

#endregion

Notice that this method is private, so it can only be called within the class.
Next, we add the second constructor:

/// <summary>
/// Contructor for Car object, sets the Car object from the SqlDataReader passed to it.
/// </summary>
/// <param name="theObjReader">SqlDataReader value that contains the DB information to set </param>
public Car(SqlDataReader theObjReader)
{
SetObjectData(theObjReader);
}

Now that we have a constructor that can build an object from a SqlDataReader we pass to it, we need to create a method to pass it a SqlDataReader.
But first, let’s go ahead and add the Stored Procedures we will be using for the next two methods. Open up the Server Explorer window and right-click the Stored Procedures folder and choose Add New Stored Procedure. First, add GetAllCars:

CREATE PROCEDURE dbo.sp_GetAllCars

AS

SELECT * FROM Cars

Next, add InsertCar:

CREATE PROCEDURE dbo.sp_InsertCar

@Make varchar(50),
@Model varchar(50)

AS

INSERT INTO Cars (Make, Model)
VALUES (@Make, @Model)

SELECT SCOPE_IDENTITY()

We are using the SELECT SCOPE_IDENTITY() to return the ID of the newly created record. We will use this to find out if the add was successful.
Now before we can add the GetAllCars method, we will need to create a Collection Class for it to use. So let’s get the InsertCar method in first, which will take a parameter of the Car object. Add the method below SetObjectData. We want to make use of the Connection String in the Web.config, and also we make sure to return the ID of the new record. Our method will look like this:

/// <summary>
/// Inserts a Car object into the database.
/// </summary>
/// <param name="theCar">Car object to insert.</param>
/// <returns>Returns Int32 value of the new Car ID.</returns>
public static Int32 InsertCar(Car theCar)
{
Int32 newCarID = 0;
SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
try
{
SqlCommand cmd = new SqlCommand("sp_InsertCar", connection);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@Make", theCar.Make);
cmd.Parameters.AddWithValue("@Model", theCar.Model);

connection.Open();
newCarID = Convert.ToInt32(cmd.ExecuteScalar());
connection.Close();
}
catch
{
connection.Close();
}
return newCarID;
}

In order for us to return all the Cars in the database as objects, we are going to have to create a Collection class of the Car object. To do this, we inherit from the CollectionBase class. The Collection class is defined within the namespace, but outside of the Car class:

#region Collection Classes

/// <summary>
/// Represents a collection of Car objects
/// </summary>
[Serializable]
public class Cars : CollectionBase
{
public int TotalRecords
{
get
{
return _TotalRecords;
}
set
{
_TotalRecords = value;
}
}
protected int _TotalRecords = 0;

/// <summary>
/// Adds a new Car to the collection
/// </summary>
/// <param name="theCar">The Car to add to the collection</param>
public int Add(Car theCar)
{
this.TotalRecords++;
return List.Add(theCar);
}

/// <summary>
/// Inserts a new Car to the collection, at the specified index
/// </summary>
/// <param name="index">The Index to insert the Car into</param>
/// <param name="theCar">The Car to insert into the collection</param>
public void Insert(Int32 index, Car theCar)
{
List.Insert(index, theCar);
}

/// <summary>
/// Removes a Car from the collection
/// </summary>
/// <param name="theCar">The Car to remove from the collection</param>
public void Remove(Car theCar)
{
List.Remove(theCar);
}

/// <summary>
/// Checks to see if the Car object exists in the collection
/// </summary>
/// <param name="theCar">The Car to search for in the collection</param>
/// <returns>Returns true if the Car exists in the collection</returns>
public bool Contains(Car theCar)
{
return List.Contains(theCar);
}

/// <summary>
/// Returns the index of the Car object in the collection (if it exists)
/// </summary>
/// <param name="theCar">The Car to search for in the collection</param>
/// <returns>Returns the int index of the Car object in the collection</returns>
public int IndexOf(Car theCar)
{
return List.IndexOf(theCar);
}

public void CopyTo(Car[] array, int index)
{
List.CopyTo(array, index);
}

public Car this[int index]
{
get
{
return (Car)List[index];
}
set
{
List[index] = value;
}
}
}

#endregion

Finally for our class, we add the GetAllCars mehtod, which will return the collection we just defined:

/// <summary>
/// Gets all the Cars in the database.
/// </summary>
/// <returns>Returns Cars Collection that contains all Cars.</returns>
public static Cars GetAllCars()
{
Cars CarsCollection = new Cars();
SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ToString());
try
{
SqlCommand cmd = new SqlCommand("sp_GetAllCars", connection);
cmd.CommandType = CommandType.StoredProcedure;

connection.Open();
SqlDataReader objReader = cmd.ExecuteReader();
while (objReader.Read())
{
Car newCar = new Car(objReader);
CarsCollection.Add(newCar);
}
objReader.Close();
connection.Close();
}
catch
{
connection.Close();
}
return CarsCollection;
}

We grab all of the records from the database, and then loop through each one, adding them to a collection, which we eventually return.

Now we can move onto our ASPX page and build the front-end. All we need is a Repeater to display all the objects we will retrieve in the collection, and then two textboxes and a button to add new objects to the database.
Our ASPX page should look something like this:

Make: <asp:TextBox ID="fld_Make" runat="server" /><br />
Model: <asp:TextBox ID="fld_Model" runat="server" /><br />
<asp:Button ID="btn_Add" runat="server" Text="Add" OnClick="btn_Add_OnClick" />
<br />
<br />
<asp:Repeater ID="repeater_Cars" runat="server">
<HeaderTemplate>
<table width="350"><tr>
<td><strong>ID</strong></td>
<td><strong>Make</strong></td>
<td><strong>Model</strong></td>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td><%# Eval("ID") %></td>
<td><%# Eval("Make") %></td>
<td><%# Eval("Model") %></td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</asp:Repeater>

We are displaying all of the properties of the Car object with the repeater. Using Eval, we reference the Property names of the object, as we will be binding the collection to the Repeater in the code-behind. Moving to the code-behind, we will bind the repeater on Page_Load, using the GetAllCars method. First, we need to add a reference to the class we just created:

using DBTutorials.Car;

Next, we bind the Repeater on page load with the Collection we created:

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindRepeater();
}
}

protected void BindRepeater()
{
repeater_Cars.DataSource = Car.GetAllCars();
repeater_Cars.DataBind();
}

Finally, on the button click, we want to create a new object from the two text fields, then add that to the database, and update the Repeater to display all Cars:

protected void btn_Add_OnClick(object sender, EventArgs e)
{
if (!String.IsNullOrEmpty(fld_Make.Text) && !String.IsNullOrEmpty(fld_Model.Text))
{
Car newCar = new Car();
newCar.Make = fld_Make.Text;
newCar.Model = fld_Model.Text;

if (Car.InsertCar(newCar) > 0)
{
fld_Make.Text = "";
fld_Model.Text = "";
fld_Make.Focus();

BindRepeater();
}
}
}

Download Source Files

The post Inserting Custom Object to SQL Database in C# appeared first on DB Tutorials | Database Tutorials, Tips, and Articles.

]]>
http://www.dbtutorials.com/advanced/custom-object-inserting-cs/feed/ 0