-->

Friday, December 20, 2013

Connect SqlDataSource Control with Database, Insert, Update and Delete

How to connect SqlDataSource Control with database, also add extra features like insert, update and delete. Here we will take some steps, these are

Step-1 : Create a SQL Table with some fields like
sno int (primaryKey, Isidentity=true)
name nvarchar(50)
address nvarchar(250)

Step-2 : Add SqlDataSource Control to Design window from toolbox
Step-3 : Select 'Configure Data Source' link using show Smart tag.
'Configure Data Source'



Step-4 : Select Database or ConnectionString from Dropdown menu.
ConnectionString

Step-5 : Select Table-name from Dropdown menu also select Advanced tab.
Table-name from Dropdown menuAdvanced tab

Step-6 : Select Insert, Update and delete checkbox option.
Step-7 : Click to Test Query and  Finish button

Now generate source code in page file

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    Database items
        <br />
        <asp:SqlDataSource ID="SqlDataSource1" runat="server"
            ConnectionString="<%$ ConnectionStrings:xyz %>"
            DeleteCommand="DELETE FROM [emp] WHERE [sno] = @sno"
            InsertCommand="INSERT INTO [emp] ([name], [address]) VALUES (@name, @address)"
            SelectCommand="SELECT * FROM [emp]"
            UpdateCommand="UPDATE [emp] SET [name] = @name, [address] = @address WHERE [sno] = @sno">
            <DeleteParameters>
                <asp:Parameter Name="sno" Type="Int32" />
            </DeleteParameters>
            <InsertParameters>
                <asp:Parameter Name="name" Type="String" />
                <asp:Parameter Name="address" Type="String" />
            </InsertParameters>
            <UpdateParameters>
                <asp:Parameter Name="name" Type="String" />
                <asp:Parameter Name="address" Type="String" />
                <asp:Parameter Name="sno" Type="Int32" />
            </UpdateParameters>
        </asp:SqlDataSource>
    </div>
    </form>
</body>
</html>

 

Saturday, June 1, 2013

How to Perform Binding with DataGridView in WinForms

The DataGridView control provides a powerful and easiest way to display our data in tabular format with entity framework 5. We can use DataGridView as we want to, like we can use it to simply show our read-only data or we can use it with editable views of our data, in c# programming. Access data from the database and perform binding steps to show them in datagridview.

To do binding on DataGridView with our data is straight-forward and in many cases it is as simple as setting the DataSource property of DataGridView. As DataGridView supports standard binding model, so it will bind to some instances of c# classes like DataSet, DataTable, BindingList and BindingSource etc.

The first way to bind our datagridview to table of database is:

studentDataGridView.DataSource = "DataSet Name";
studentDataGridView.DataMember = "Table Name";


Let’s suppose we have a c# class Student having some properties like following:

public class Student
{
       public int Id { get; set; }
       public string Name { get; set; }
       public string RollNo { get; set; }
       public string Branch { get; set; }
}

Now we will bind datagridview to list of students as:

DataContext dc = new DataContext();
var studentList = dc.Student;
studentDataGridView.DataSource = studentList.ToList();

Here ToList() method is used to convert our DbSet<Student> type of data to List<Student> type of data, because DbSet<> type of data cant be directly bind with any control.
Now if we want to bind only Name and Branch column of Student class then:

DataContext dc = new DataContext();
var studentList = from d in dc.Student
select new
{
d.Name,
d.Branch
};
studentDataGridView.DataSource = studentList.ToList();

We can bind our desired columns to datagridview as we have done in above code. DataGridView doesn’t have any mean where is data come from, so we can use conditions in our queries also.

See Also: DataGridView binding with DataSet

Monday, May 27, 2013

Combo Box or List Box Binding with Database, Entity Framework 5

Most often when we have a list of items and want to select a single item at a time then we can use Combo Box or List Box in winforms application. The basic difference in both is: In List Box one can select an item only from existing list of items whether in Combo Box one can write his/her own new data that can be added to our binding list.

There are some properties that are used to bind a list of items in c# programming i.e

  • Data Source
  • Display Member
  • Value Member
DataSource property will decide the list of data that is to be bind. It may be a Database, Web Service and an object. When this property is set, the item collection cannot be modified at run time.

DisplayMember property indicates the name of an object contained in DataSource property. It denotes what object will be displayed to the user. The default value is empty string.

ValueMember property is used to get the value associated with the control when we are accessing the selected item property.

Let's suppose we have an employee class with properties as shown below c# class:

public class Employee
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Department { get; set; }
    }

Now following code is used to get all records of employee from Database into a var type of variable named employees as given below:

DataContext dc = new DataContext();
var employees = from emp in dc.Employees
                          select emp;

In the above code employees variable have IEnumerable<Employee> type of data and this type of data cannot directly bind to data source property of a control in our programming language. So we have to convert it to a List<Employee>.
To convert this simple type of data a ToList() method is sufficient.

In case of Combo Box

ComboBox1.DataSource = employees.ToList();
ComboBox1.DisplayMember = "Name";
ComboBox1.ValueMember = "Id";

In case of List Box

ListBox1.DataSource = employees.ToList();
ListBox1.DisplayMember = "Name";
ListBox1.ValueMember = "Id";

Here ToList() method is used to convert IEnumerable<Employee> type of data to List<Employee>.
Now when we run our project we will see the list of employees Names in the item collection of Combo Box or ListBox.

How to Perform LINQ in C# Programming to Get Records using Entity Framework 5

LINQ stands for Language INtegrated Queries in the context of sql programming. These are like SQL queries used to manipulate collection of objects through entity framework. LINQ are queries that can be directly written in c# programming language and will be translated to SQL statements and executed against the database. It simplify the complex process of database developer to query in databases.

These type of queries are executed on collection of IEnumerable<T> or IQueryable<T> and as results are returned new collection of IEnumerable<T> objects. In this post I will explain some example that show how to use LINQ on collection of objects in c# programming.

 Let’s suppose we have a c# class Student having two properties i.e. Name and Age and of course a primary key Id. Now to get all records of students from database we have to write:

var students = from student in dc.Student
               select student;

In above code it will extract all columns from Student table like Id, Name and Age also. If we want only name and Age column then:

var students = from student in dc.Student
               select new
               {
                   student.Name,
                   student.Age
               };

It will show only Name and Age columns of all the records.

It’s very simple to get some records having where condition like in sql programming. In below code if we want to get all records having age = "25" then the following code should be written:

var students = from student in dc.Student
                     where student.Age == 25
                     select student;

Now at the last if you want to get records having age = "25" and name = "Rahul" then following line of code has to be written:


var students = from student in dc.Student
               where student.Age == 25 && student.Name == "Rahul"
               select student;

 So according to above examples, LINQ is very simple language to be queried with databases. Here dc is used for data context that is created through entity framework 5.

Sunday, May 26, 2013

How to Update and Delete record from database using EntityFramework in c# programming

Sometimes database developer have to change some of the properties of table, due to insertion mistakes. As we have inserted records into Database using entity framework 5, it's time to modify that record and delete that if required through c# programming language.

We (database developer) all know that update and delete query required a condition through which they retrieve an instance on which action is to be performed.

Update: The update operation is quite simple in entity framework 5. First retrieve an instance of specified Entity object, then edit the properties of that instance and finally save the changes to that context and done. It have a little bit complex syntax in sql programming.

Let’s suppose we have an entry of student named “Student 1” and we want to change his/her name and age then:

 DataContext dc = new DataContext();
Student student = dc.Student.First(s = > s.Name.Equals("Student 1"));
student.Name = "Student 2";
student.Age = 25;

 dc.SaveChanges();

Execute the code and check the database, you have successfully made changes through the above c# programming code.

Delete: The delete operation is little bit different from Update operation. First retrieve an instance of specified Entity object, then delete that instance from context and finally save changes to context and done.

Let’s suppose we have an entry of student named “Student 2” and we want to delete then:

 DataContext dc = new DataContext();
Student student = dc.Student.First(s = > s.Name.Equals("Student 1"));
dc.Student.Remove(student);

 dc.SaveChanges();

Execute the above code of c# programming and check the database, there is no student named "Student 2".

 Note: "When we are retrieving an instance of Student class and if that student has not been found then there may be an Exception. So better be use 'try catch' block".

Insert Entities into database Table using Entity Framework 5: C# programming

After creating databases with foreign key constraints in entity framework 5, its time to insert some records and save them for further references. In this article i will cover up, how to add entities and how entity framework processes these during saveChanges method. A database developer often use this method to insert records.


As i have added Student table which is a c# class in our solution, i will insert a record in the same table directly through code. I am using the same Winforms Application that we have used in previous article so make sure you have studied Creating databases in entity framework 5.

Note: "You must create an object of DataContext class in Form1's Constructor in order to insert some data in Student class."

 To insert a record in database we will use the method i.e. EntityCollection<TEntity>.Add(). Lookout the c# programming code given below:

DataContext dc = new DataContext();
Student newStudent = new Student();
newStudent.Name = "Student 1";
newStudent.Age = 24;

 dc.Student.Add(newStudent);
dc.SaveChanges();

All we are doing here is create a new instance of Student class and populate its properties. Then add that instance to Students set using EntityCollection<TEntity>.Add() method.

In above code Add() method is used to add newly created instance in database, and SaveChanges() method is used to save all the changes you have done with database.

Finally, go to your database and check, you will find the above instance there in Students table.

Download Source Code.

Update n Delete Entities in Entity Framework 5

Saturday, May 25, 2013

Create Foreign key in database using Entity Framework 5 in C# Programming

In the context of relational database management system (RDBMS), foreign key is a field in one table that uniquely identifies a row of another table. In sql programming, foreign key is a column which points to the primary key of another table.

An entity can be related referenced with other entities through this foreign key, in entity framework. This can be done through an association between the tables. As we have studied in our Previous Post how to create databases and tables in it. In this article i will create a foreign key in the table.

Steps of creating foreign key in entity framework 5.

Here above i have mentioned the version of entity framework, because it is the stable version currently. Follow these steps and it will add a reference of student in address class.
  • In our Previous post we have created a table Student. We will use this table here. 
  • In that same project add a new class Address and create some properties (fields in context of databases).
  • In the Address class create a property type of Student having keyword virtual as written here:
    public virtual Student Student { get; set; }
  • Now repeat the same procedure of creating an object of DataContext class in Form1's Constructor.
  • And Finally we have successfully created a foreign key in Address table by running Form1.

Thursday, May 23, 2013

Create Databases using EntityFramework 5 in C# programming

Entity Framework is an open source object-relational (ORM) framework for ADO.NET. It allows user to create a model by writing code in EF designer. The releases are improving from entity framework 3.5 and onwards. Entity Framework 5 is the stable version for visual studio 2010 and 2012 and updated soon with the latest features.

The main advantages of using Entity Framework 5 are:
  • Speed - You do not have to worry about creating a DB you just start coding. Good for developers coming from a programming background without much DBA experience.
  • Simple - you do not have a edmx model to update or maintain.
Entity framework 5 released with some advanced features like enum support, table-valued functions and performance improvements. In this article we will create a database using entity framework 5 code first approach.

 Steps of Creating Database through Entity Framework
  • Create a Project ClassLibrary in Visual Studio.
  • Install EntityFramework from Package Manager Console (required internet connection) using
    • PM> install-package entityframework 
  • Remove InBuilt Class i.e "Class1".
  • Add a new class “Student”.
  • Add another class DataContext (Name may be change) inherited by DbContext
    • DbContext is in System.Data.Entity Namespace.
  • Write the below code in DataContext class 
j
public DataContext()
          :base("Database Name")
{
    if(!Database.Exists("Database Name"))
        Database.SetInitializer(new DropCreateDatabaseAlways<DataContext>());
}
public DbSet<Student> Student { get; set; }

  •  Add a new project2 (Window Form Application) that will be used to execute constructor of DataContext class.
  • Install Entity Framework 5 in this new project also.
  • Create an object of DataContext class in Form1 Constructor.
  • Now run project2 and your Database has been created.
When we will execute the given command to install entity framework, it will execute the stable version i.e. entity framework 5.

Download code file

Insert foreign key in database
© Copyright 2013 Computer Programming | All Right Reserved