Installing SQL Server Mgt Studio 2012

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:

1. Download the executable file from the Microsoft official site. Double click on the .exe file and "SQL Server installation center" window will open.                                                    
   
2. Select first option "New SQL server stand-alone installation" and it will check all the support rules with a progress bar.                                                                                                  
   
3. 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.                                                                                                                         
   
4. 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.             
   
5. 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                                                                                                                              
   
6. User can help Microsoft to SQL server features and services with selecting a checkbox of Error reporting. It is optional.                                                                                                    
   
7. 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.                                                                                                                                                       
   
8. 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.

Nested gridview asp.net c# with expand and collapse example

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:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" OnRowDataBound="GridView1_RowDataBound">

<Columns>

<asp:TemplateField>
<ItemTemplate>
<a href="JavaScript:expandcollapse('<%# Eval("country_id") %>');">
<img src="plus.gif" border="0" id='img<%#Eval("country_Id") %>' />

</ItemTemplate>

</asp:TemplateField>
<asp:BoundField DataField="country_Id" HeaderText="Country Id" />
<asp:BoundField DataField="country_name" HeaderText="Country Name" />

</Columns>

</asp:GridView>

Here,

1. AutoGenerateColumns="false" means, automatically generated columns will not display in the Gridview.
2. OnRowDataBound="GridView1_RowDataBound" means, raise event after bind the parent gridview for next child gridview.
3. Using template field we create a column in the gridview.
4. ItemTemplate used for displaying data in the browser.
5. 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.
6. 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

<asp:TemplateField>
<ItemTemplate>
<tr>
<td colspan="100%">
<div id='<%# Eval("Country_Id") %>' style="display:none" />

<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="state_Id" HeaderText="State Id" />
<asp:BoundField DataField="state_name" HeaderText="State Name" />

</Columns>

</asp:GridView>

</td>

</tr>

</ItemTemplate>

</asp:TemplateField>

Here,

1. Bind division with the country_Id and set display style as none so its not visible.
2. GridView with two data Field that are state_Id and state_name.

Now the whole source code is :

<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" OnRowDataBound="GridView1_RowDataBound">

<Columns>
<asp:TemplateField>
<ItemTemplate>
<a href="JavaScript:expandcollapse('<%# Eval("country_id") %>');">
<img src="plus.gif" border="0" id='img<%#Eval("country_Id") %>' />


</ItemTemplate>


</asp:TemplateField>
<asp:BoundField DataField="country_Id" HeaderText="Country Id" />
<asp:BoundField DataField="country_name" HeaderText="Country Name" />
<asp:TemplateField>
<ItemTemplate>
<tr>
<td colspan="100%">
<div id='<%# Eval("Country_Id") %>' style="display:none" />

<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="state_Id" HeaderText="State Id" />
<asp:BoundField DataField="state_name" HeaderText="State Name" />

</Columns>

</asp:GridView>

</td>


</tr>


</ItemTemplate>


</asp:TemplateField>

</Columns>


</asp:GridView>
</div>
</form>

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,

1. <Script> tag, which is include in <head> section
2. expandcollapse is the function name in which we have a parameter named "name".
3. parameter take country_id as a value
4. 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.
5. 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,

1. First to bind the gridview with DataSet.
2. In the RowDataBound event, first to check the selected row is DataRow.
3. Now, find the nested gridview in the block and bind it with data source.

Listing with Insert and Update MVC Part 1

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:

@model CodeWallMVCApplication.ViewModels.CategoryVM
@{
ViewBag.Title = "Index";
Layout = "~/Views/Shared/_Layout.cshtml";
int i = 1;
}
<h2>Categories List</h2>
<input type="button" value="Create New" id="btnNew" />
<table>
<thead>
<tr>
<td><b>#</b></td>
<td><b>Title</b></td>
<td><b>Description</b></td>
<td><b>Active</b></td>
</tr>
</thead>
<tbody>
@foreach (var item in Model)
{
<tr>
<td>@i</td>
<td>@item.Title</td>
<td>@item.Description</td>
<td>@(item.IsActive ? "Active" : "D")</td>
</tr>
i = i + 1;
}
</tbody>
</table>

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.

Working with Database First: edmx in MVC

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:

1. 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.
   
2. 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.
   
   
   
3. 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.
   
   
   
4. 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.

1. 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.
   
   
   
2. 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.
   
   
   
3. Choose database objects like tables, stored procedures etc. to be include in edmx file and click on next.
   
   
   
4. 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.

Creating a Model in Asp.Net MVC

Creating a Model means create a database table with required fields in form of simple class and its properties. This class must be added to the pre-added folder Models which differentiate this from all the simple classes in the MVC application.

In earlier article we have created a view for an existing action method Index which is by default added. At the same time we have passed some values from the same action to the respective view page. Those values have been passed through ViewBag, used to dynamically share values from controller to view.

Models folder contains all the classes used to represent application model for the application including validation logic and data access. Right click on the Models folder and add a new class Product and add some properties in that class as shown below:

public class Product
{
public int Id {get; set;}
public string Name {get; set;}
public string Description {get; set;}
public string Color {get; set;}
public string Type {get; set;}
public double Price {get; set;}
}

Using this model class we can either create a database through code- first approach or just use it with a List object to show the data on view page. We will use second option here, create a simple list object and pass items to view page.

Create a Controller ProductsController and choose Empty MVC Controller in Template selection. There is a default action method Index, delete and create a new action method List. You can even change its name from Index to List because there is not view page for this action method.

In this action method we will create a simple list of products with some items and then show those items in the list page. Write the below code in this list method:

public ActionResult List()
{
List<Product> productList = new List<Product>();
productList.Add(new Product() { Id = 1, Name = "Mouse", Color = "Black", Description = "", Price = 800, Type = "Electronics" });
productList.Add(new Product() { Id = 2, Name = "Scanner", Color = "White", Description = "", Price = 3000, Type = "Electronics" });
productList.Add(new Product() { Id = 3, Name = "Mobile", Color = "Dark Grey", Description = "", Price = 6000, Type = "Electronics" });
productList.Add(new Product() { Id = 4, Name = "Shirt", Color = "Blue", Description = "", Price = 850, Type = "Cloths" });
productList.Add(new Product() { Id = 5, Name = "Shampoo", Color = "Black", Description = "", Price = 125, Type = "Hair Product" });

return View(productList);
}

Create a View for this action method which will have the same name “List”. In this view file we have to write simple html that will show all the records added in the action method. Write the below code in this view page:

@model IEnumerable<CodeWallMVCApplication.Models.Product>
@{
ViewBag.Title = "List";
}

<h2>Product List</h2>

<table>
<thead>
<tr>
<th>No.</th>
<th>Name</th>
<th>Description</th>
<th>Color</th>
<th>Type</th>
<th>Price</th>
</tr>
</thead>
<tbody>
@foreach (var item in Model)
{
<tr>
<td>@item.Id</td>
<td>@item.Name</td>
<td>@item.Description</td>
<td>@item.Color</td>
<td>@item.Type</td>
<td>@item.Price</td>
</tr>
}
</tbody>
</table>

The first line specifies about the model rendered on this view, remaining code is same as simple html code. Run this application the on the address bar write http://localhost:53420/Products/List and it will show the product list, added on the action method. In the address, port no may be change according to the application created so don’t copy it from here, just run your application.

Creating a View in Asp.Net MVC

Creating a View means adding an HTML file for an existing action method in controller. View is the page that is shown to user, whatever HTML you write or CSS you embed is fully applicable for that view.

In earlier article we have created a controller having an “Index” action method which is by default added. We have changed this action method and returns a string to be displayed on browser directly.

When working with views programmer should know about HTML language and should have a little bit knowledge of CSS style-sheets. In this article we will create a view page checking the options that can be applied on creating a view.

Change index action method as it was when created:

public ActionResult Index()
{
return View();
}

Right click on Index and click on Add View. It will show Add View dialog box having a list of options as in below screenshot. We will discuss all these options in later articles, by now just click on Add button.

Expand the Views folder, it have a new folder having the same name of our controller i.e. Home. All the views created through this controller will be saved in this folder. Open the newly created view, it have only a bit code as shown:

Change this code and insert an hyperlink <a href="www.codewalls.com">Code Walls</a>. Run this project and it will default show this page.

 

Data on View from Controller

When we create an MVC application as “Internet Application” template then by default Index action method have a Viewbag message defined as shown below:

public ActionResult Index()
{
ViewBag.Message = "Modify this template to jump-start your ASP.NET MVC application.";

return View();
}

Change this message or leave as is (I have changes this message a little bit) and in the view page there is a line showing the output of this viewbag message i.e.

@ViewBag.Message

Run this project and check the message on the page as shown in green ellipse. You can change this in-built template according to your own design scenario. Using ViewBag you can dynamically share the values from controller to view, we will discuss more about it later.

Creating a Controller in Asp.Net MVC

What is the MVC architecture and how it can be used to build a web application has been discussed in our earlier article. We will discuss about the concept of MVC in context of classes that can be easily accessible in our application. These classes can be created as simply as other classes including properties to be get/set values. MVC stands for Model, View and Controller that divides a web application into three parts. These parts are data, user-interfaces and logics which differentiates everything about an application. Here is a one-line definition about MVC:

• Model: these are the classes representing your application data including the properties your database have.
• View: a simple page that will show to user and redirect your complex code into basic HTML. Decides the design in which data will show.
• Controller: these classes are responsible for what is to be shown in which view.

In earlier article, we have created a simple internet application having all the basic folders and packages. Those folders contains App_Start, Content, Controllers, Scripts, Models and of course Views. Now I am starting with an Empty MVC application having only some of these folders and don’t have any code logics. Just right click on Controllers and select Add--> Controller, "Add Controller" window will appear with options listed below:

Specify the name “Home” and set Template as “Empty MVC controller” and click on add button will add a “HomeController” under Controllers folder. This controller have only one action “Index” having the structure below:

Look out the default code, there is only one action added i.e. “Index” having the return type “ActionResult” which is commonly preferred by all the action methods. Basically this action method is used for default view about any controller like listing or home page. Suppose there is a controller for products then the index action will show all the products having functionality like add/edit/delete. Now change this action a little bit as shown:

public class HomeController : Controller 

{ 

public string Index() 

{ return "This is my first controller"; } 

}

Run this application and it will show the string returned by the above “Index” method.

Check out the address in browser, there is only “localhost:portNo” without any controller’s name. This is because the default routing of an MVC application is Controller: Home and Action: Index. That’s why it is showing only the port number of the application.