databases

Friday, December 20, 2013

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.

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

Step-5 : Select Table-name from Dropdown menu also select Advanced 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">

    <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

June 01, 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

Binding c# programming Combo Box databases Entity Framework 5

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.

c# programming databases Entity Framework 5 LINQ

May 27, 2013

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

c# programming databases Entity Framework 5

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".

c# programming databases Entity Framework 5

May 26, 2013

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