Creating a Database is to occupy some space in physical drive for the records saving through the application. These saved records can easily be listed or modified as per user’s requirement. To perform operations like add/edit/delete on these records a person used to create some application having capability of doing so.
In earlier article, we have installed SQL Server Management Studio 2012 following some easy steps. This time we will establish a connection with server and create a database EmpDb having only a single table Employee. Start Management studio through all programs or whatever shortcut you have on your system and follow these steps.
First screen will prompt you to establish a connection with server requiring some values as
below: Just write (LocalDb)\v11.0 because i have only local db installed in my system, you can change as per your server installation.
On the left side there are some options "Database, Security, Replication, Management" are pre-added. Right click on Database and select New Database, a window will show as below prompting database name:
Enter database name "EmpDb" and click on Ok button, it will create a new database with none of tables, views. Now look out the server connection there is a new entry of your database as shown:
Right click on tables and select New Table, it will show you editor having three columns name, data-types and nullable. Write some columns with their data-types, don’t forgot to make Id a primary key and Identity set to true. Identity property will auto-increment this columns according to data entry. Save this table and enter name “Employee”. You can see the columns and their data-types in the below image:
Your database have easily been created with a single table and some columns. In the next article we create an edmx file for this database to be used for MVC application and will perform operation on this database.
Before we talk about nested gridview asp.net c#. First we talk about gridview control. It is a data source control through which we can bind data in the form of table. In this article, we will take two gridview asp.net controls and we will arrange both control in this way, please see the mentioned picture.
Lets to start how to design it
First to add asp.net gridview in the source page
Add a <Columns> tag inside it.
Add single column inside it using TemplateField with HeaderText property, if you want to add more than one column then add equal number of TemplateField inside it.
Show the data in the browser with the help of ItemTemplate, so add it inside Template Field.
Now, the article's main point is here, Add another gridview asp.net controls inside the ItemTemplate, Now your code look like:
If we want to display ads on the webpage then we use Adrotator control in ASP.NET. Without filter ads adrotator control display all ads, which is exist in the xml file. But all ads are not good for website owner because only a content related ad earn more money compare to all ads. So lastly developers decide that they have to design user choice ad system. The KeywordFilter property is created by visual studio developers for ads filtration. Through this property you can display only those ads, which is related to content.
How to use KeywordFilter Property
Advertisements.xml file
<ad>
<Keyword> name of the keyword </Keyword>
<ad>
Adrotator control property
KeywordFilter = name of the keyword which is mentioned in Advertisements file
Installing SQL Server is as simple as installing a software following a list of steps. SQL Server 2012 was released in April 2012 and because of its list of features and easiness to use made favorite among professionals and beginners. We will learn about to creating database in SQL Server Management studio after installing it in the system.
Microsoft SQL Server is a powerful and free data management tool, designed for easy development and utilities including:
LocalDB (MSI installer) LocalDB is a lightweight version of Express that has all its programmability features. It can be bundled with Application and Database Development tools like Visual Studio and or embedded with an application that needs local databases.
Express (Containing only the database engine) Use this if you need to accept remote connections or administer remotely.
Express with Tools (with LocalDB) Choose either LocalDB or Express depending on your needs above.
SQL Server Management Studio Express (Tools only) This does not contain the database, but only the tools to manage SQL Server instances, including LocalDB, SQL Express, SQL Azure, etc. Use this if you already have the database and only need the management tools.
Express with Advanced Services (contains the database engine, Express Tools, Reporting Services, and Full Text Search) This is a larger download than "with Tools" as it also includes both Full Text Search and Reporting Services.
Here are the steps to installing SQL Server Management studio 2012:
Download the executable file from the Microsoft official site. Double click on the .exe file and "SQL Server installation center" window will open.
Select first option "New SQL server stand-alone installation" and it will check all the support rules with a progress bar.
After completing rule check it will opt for license terms that must accept by you for installation. User may also select the option to send feature data to Microsoft or just ignore this checkbox.
It will then install setup files used for preparing the installation into your system showing progress to user. On this step if your system is connected to internet connection then any update available will also be installed with this installation.
Next step will opt for feature selection to be installed as shown in the image. Some features must be installed and those features are only readable as LocalDB and Connectivity tools
User can help Microsoft to SQL server features and services with selecting a checkbox of Error reporting. It is optional.
Important step in which the installation process continues with copying all the required files on the system. This step takes more time because of copying required files.
The last step of installation to confirm about installation have successfully completed or having some errors. Successfully completed window will show with list of all the features installed with this installation.
Completing these steps you can easily access SQL Server management studio to create database. We will create database with some tables in next article.
In previous example we have already learn about nested gridview asp.net . Now, today we will learn nested gridview asp.net with expand and collapse feature. Parent Gridview bind with country database table and child Gridview bind with State database table when we select any country. Child Gridview appeared according to country_id column like. Lets start to design parent GridView:
AutoGenerateColumns="false" means, automatically generated columns will not display in the Gridview.
OnRowDataBound="GridView1_RowDataBound" means, raise event after bind the parent gridview for next child gridview.
Using template field we create a column in the gridview.
ItemTemplate used for displaying data in the browser.
Both anchor and image tag bind with the country_id using Eval method. Also call a javaScript method when we click on image. Script method define later in the post.
Bind two column with table , now the design window look like
Now, add nested Gridview using Template field. Similarly again create columns for state table but remember that state table get from country_id so also take country_id inside the template field, see the code
Now, come to the javaScript function which is calling from anchor tag in first TemplateField. Now, copy this code and paste into your <Script> block.
<script>
function expandcollapse(name)
{
var div = document.getElementById(name);
var img = document.getElementById('img' + name);
if(div.style.display=='none')
{
div.style.display = "inline";
img.src = "minus.gif";
}
else
{
div.style.display = "none";
img.src = "plus.gif";
}}
</script>
Here,
<Script> tag, which is include in <head> section
expandcollapse is the function name in which we have a parameter named "name".
parameter take country_id as a value
we have two variable first is used for division which is hide in the second template field and second variable is used for image which is changed according to expand and collapse.
display inline means state table open in the parent table boundary.
Now, come to the code file. The following code demonstrates how to bind the GridView control with the DataSet also demonstrate how to wok with RowDataBound event.
public partial class Default21 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if(!Page.IsPostBack)
{
bindgridview();
}
}
private void bindgridview()
{
SqlConnection con = new SqlConnection();
con.ConnectionString = ConfigurationManager.ConnectionStrings["btti"].ToString();
con.Open();
SqlCommand cmd = new SqlCommand("select * from [country]", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
GridView1.DataSource = ds;
GridView1.DataBind();
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if(e.Row.RowType==DataControlRowType.DataRow)
{
SqlConnection con = new SqlConnection();
con.ConnectionString = ConfigurationManager.ConnectionStrings["btti"].ToString();
con.Open();
GridView gv = (GridView)e.Row.FindControl("GridView2");
int countryid = Convert.ToInt32(e.Row.Cells[1].Text);
SqlCommand cmd = new SqlCommand("select * from [state] where country_id="+countryid, con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
gv.DataSource = ds;
gv.DataBind();
}
}
}
Here,
First to bind the gridview with DataSet.
In the RowDataBound event, first to check the selected row is DataRow.
Now, find the nested gridview in the block and bind it with data source.
Listing of records is binding the model with custom list of objects and show on the view page. For this process we will create custom class that will be used to create objects list and then will bind that list with model.
We have learnt how to use default process and create listing with CRUD actions. In this article we will create with our own code blocks. Create a class CategoryD having all the fields saved in the database table Category as:
public class CategoryD
{
public int Id { get; set; }
public string Title { get; set; }
public string Description { get; set; }
public bool IsActive { get; set; }
}
Create a controller CategoriesController with default Index action. This action is empty and write following code fragment in this action:
public ActionResult Index()
{
List<CategoryD> categories = new List<CategoryD>();
using (EmpDbEntities db = new EmpDbEntities())
{
categories = db.Categories.Select(a => new CategoryD
{
Id = a.CategoryId,
Title = a.Name,
Description = a.Description,
IsActive = a.IsActive
}).ToList();
}
return View(categories);
}
Create view for this action and paste below code in this view page:
Run this project and go to this controller’s action, it will show all the records from categories table. This output is as same as the output in earlier article where the view has been created using default MVC feature.
This is only listing part, we have to insert and update through this same page. To perform this, just change the category class a little bit and add one more class. We will insert and update category entity in our next article.
Database First approach creates all the model classes, properties and its context from the selecting database and then let the user access those classes for performing CRUD actions. User first have to create database and then follow this article to create an edmx file.
An edmx file is an XML file defines a model used to perform CRUD operations on database. This file can also be used with Entity Framework and contains some information to be used by EF designer. User can change this file to change EF designer and sometimes have to change in edmx file manually.
In previous article we have created a database EmpDb with single table, now we will create an edmx file for this database. By using the same MVC project (used in earlier articles) follow below steps:
Right click on Models folder and Add New Item and then select ADO.NET Entity Data Model, name it whatever you want and click Add.
The next window is for choosing model contents whether from an existing database or an empty model. Because we have our existing database so select Generate from database and click on next.
You need to connect with your data connection, click on new connection and select Microsoft SQL Server from data source and then continue. After clicking on next button, it will ask for connection properties.
Enter server name or it may be selected by-default if you have any. It will load all the databases by clicking on drop-down for enter database name, select your database and click on OK. You can even test this connection whether it have problem or succeed.
Now the third steps will open again with filled information like whole connection string and name in web.config to be saved. This entities name is changeable, leave it as is and next.
Next window will ask for entity framework version your project is using. It will detect all you entity framework versions and checked according to compatibility. Look out below image, it will disable V-6 and auto select V-5, so just next this step.
Choose database objects like tables, stored procedures etc. to be include in edmx file and click on next.
Your edmx file have been created as shown below.
This will have all the tables in classes forms to be easily access by programmer and a constructor with the name of entities saved in web.config file. Now we can easily create our controllers and views as per the tables, we will continue in next article.
