agosto 28th, 2011 
admin Trabajar con Datos
Podemos crear una base de datos de Sql Server Compact
Sacar Datos de la Base de Datos
Creamos un archivo ListProduct.cshtml
@{
var db = Database.Open(“prueba”);
var selectQueryString = “SELECT * FROM Product ORDER BY Name”;
}
<!DOCTYPE html>
<html>
<head>
<title>Small Bakery Products</title>
<style>
table, th, td {
border: solid 1px #bbbbbb;
border-collapse: collapse;
padding: 2px;
}
</style>
</head>
<body>
<h1>Small Bakery Products</h1>
<table>
<thead>
<tr>
<th>Id</th>
<th>Product</th>
<th>Description</th>
<th>Price</th>
</tr>
</thead>
<tbody>
@foreach(var row in db.Query(selectQueryString)){
<tr>
<td>@row.Id</td>
<td>@row.Name</td>
<td>@row.Description</td>
<td>@row.Price</td>
</tr>
}
</tbody>
</table>
</body>
</html>
2.- Insertar
@{
var db = Database.Open(“prueba”);
var Name = Request["Name"];
var Description = Request["Description"];
var Price = Request["Price"];
if (IsPost) {
// Read product name.
Name = Request["Name"];
if (Name.IsEmpty()) {
ModelState.AddError(“Name”, “Product name is required.”);
}
// Read product description.
Description = Request["Description"];
if (Description.IsEmpty()) {
ModelState.AddError(“Description”,
“Product description is required.”);
}
// Read product price
Price = Request["Price"];
if (Price.IsEmpty()) {
ModelState.AddError(“Price”, “Product price is required.”);
}
// Define the insert query. The values to assign to the
// columns in the Product table are defined as parameters
// with the VALUES keyword.
if(ModelState.IsValid) {
var insertQuery = “INSERT INTO Product (Name, Description, Price) ” +
“VALUES (@0, @1, @2)”;
db.Execute(insertQuery, Name, Description, Price);
// Display the page that lists products.
Response.Redirect(@Href(“~/ListProducts”));
}
}
}
<!DOCTYPE html>
<html>
<head>
<title>Add Products</title>
<style type=”text/css”>
label {float:left; width: 8em; text-align: right;
margin-right: 0.5em;}
fieldset {padding: 1em; border: 1px solid; width: 35em;}
legend {padding: 2px 4px; border: 1px solid; font-weight:bold;}
.validation-summary-errors {font-weight:bold; color:red; font-size: 11pt;}
</style>
</head>
<body>
<h1>Add New Product</h1>
@Html.ValidationSummary(“Errors with your submission:”)
<form method=”post” action=”">
<fieldset>
<legend>Add Product</legend>
<div>
<label>Name:</label>
<input name=”Name” type=”text” size=”50″ value=”@Name” />
</div>
<div>
<label>Description:</label>
<input name=”Description” type=”text” size=”50″
value=”@Description” />
</div>
<div>
<label>Price:</label>
<input name=”Price” type=”text” size=”50″ value=”@Price” />
</div>
<div>
<label> </label>
<input type=”submit” value=”Insert” />
</div>
</fieldset>
</form>
</body>
</html>
3.- Actualizar Datos
@{
var db = Database.Open(“prueba”);
var selectQueryString = “SELECT * FROM Product ORDER BY Name”;
}
<!DOCTYPE html>
<html>
<head>
<title>Edit Products</title>
<style type=”text/css”>
table, th, td {
border: solid 1px #bbbbbb;
border-collapse: collapse;
padding: 2px;
}
</style>
</head>
<body>
<h1>Edit Small Bakery Products</h1>
<table>
<thead>
<tr>
<th> </th>
<th>Name</th>
<th>Description</th>
<th>Price</th>
</tr>
</thead>
<tbody>
@foreach (var row in db.Query(selectQueryString)) {
<tr>
<td><a href=”@Href(“~/UpdateProducts”, row.Id)”>Edit</a></td>
<td>@row.Name</td>
<td>@row.Description</td>
<td>@row.Price</td>
</tr>
}
</tbody>
</table>
</body>
</html>
Creamos otro archivo llamado UpdateProduct
@{
var db = Database.Open(“prueba”);
var selectQueryString = “SELECT * FROM Product WHERE Id=@0″;
var ProductId = UrlData[0];
if (ProductId.IsEmpty()) {
Response.Redirect(@Href(“~/EditProducts”));
}
var row = db.QuerySingle(selectQueryString, ProductId);
var Name = row.Name;
var Description = row.Description;
var Price = row.Price;
if (IsPost) {
Name = Request["Name"];
if (String.IsNullOrEmpty(Name)) {
ModelState.AddError(“Name”, “Product name is required.”);
}
Description = Request["Description"];
if (String.IsNullOrEmpty(Description)) {
ModelState.AddError(“Description”,
“Product description is required.”);
}
Price = Request["Price"];
if (String.IsNullOrEmpty(Price)) {
ModelState.AddError(“Price”, “Product price is required.”);
}
if(ModelState.IsValid) {
var updateQueryString =
“UPDATE Product SET Name=@0, Description=@1, Price=@2 WHERE Id=@3″ ;
db.Execute(updateQueryString, Name, Description, Price, ProductId);
Response.Redirect(@Href(“~/EditProducts”));
}
}
}
<!DOCTYPE html>
<html>
<head>
<title>Add Products</title>
<style type=”text/css”>
label { float: left; width: 8em; text-align: right;
margin-right: 0.5em;}
fieldset { padding: 1em; border: 1px solid; width: 35em;}
legend { padding: 2px 4px; border: 1px solid; font-weight: bold;}
.validation-summary-errors {font-weight:bold; color:red; font-size:11pt;}
</style>
</head>
<body>
<h1>Update Product</h1>
@Html.ValidationSummary(“Errors with your submission:”)
<form method=”post” action=”">
<fieldset>
<legend>Update Product</legend>
<div>
<label>Name:</label>
<input name=”Name” type=”text” size=”50″ value=”@Name” />
</div>
<div>
<label>Description:</label>
<input name=”Description” type=”text” size=”50″
value=”@Description” />
</div>
<div>
<label>Price:</label>
<input name=”Price” type=”text” size=”50″ value=”@Price” />
</div>
<div>
<label> </label>
<input type=”submit” value=”Update” />
</div>
</fieldset>
</form>
</body>
</html>
4. Borrar Datos
Creamos el archivo ListProductsForDelete.cshtml
@{
var db = Database.Open(“prueba”);
var selectQueryString = “SELECT * FROM Product ORDER BY Name”;
}
<!DOCTYPE html>
<html>
<head>
<title>Delete a Product</title>
<style>
table, th, td {
border: solid 1px #bbbbbb;
border-collapse: collapse;
padding: 2px;
}
</style>
</head>
<body>
<h1>Delete a Product</h1>
<form method=”post” action=”" name=”form”>
<table border=”1″>
<thead>
<tr>
<th> </th>
<th>Name</th>
<th>Description</th>
<th>Price</th>
</tr>
</thead>
<tbody>
@foreach (var row in db.Query(selectQueryString)) {
<tr>
<td><a href=”@Href(“~/DeleteProduct”, row.Id)”>Delete</a></td>
<td>@row.Name</td>
<td>@row.Description</td>
<td>@row.Price</td>
</tr>
}
</tbody>
</table>
</form>
</body>
</html>
Creamos DeleteProduct
@{
var db = Database.Open(“prueba”);
var ProductId = UrlData[0];
if (ProductId.IsEmpty()) {
Response.Redirect(@Href(“~/ListProductsForDelete”));
}
var prod = db.QuerySingle(“SELECT * FROM PRODUCT WHERE ID = @0″, ProductId);
if( IsPost && !ProductId.IsEmpty()) {
var deleteQueryString = “DELETE FROM Product WHERE Id=@0″;
db.Execute(deleteQueryString, ProductId);
Response.Redirect(“~/ListProductsForDelete”);
}
}
<!DOCTYPE html>
<html
<head>
<title>Delete Product</title>
</head>
<body>
<h1>Delete Product – Confirmation</h1>
<form method=”post” action=”" name=”form”>
<p>Are you sure you want to delete the following product?</p>
<p>Name: @prod.Name <br />
Description: @prod.Description <br />
Price: @prod.Price</p>
<p><input type=”submit” value=”Delete” /></p>
</form>
</body>
</html>
5.- Conectando a la base de datos
Podemos conectar a la base de datos de dos formas:
La primera es usar Database.Open y especificar el nombre del fichero de la base de datos
var db = Database.Open("prueba");
La segunda opción es utilizar una cadena de conexión en el archivo web.xml
<?xml version=”1.0″ encoding=”utf-8″ ?>
<configuration>
<connectionStrings>
<add
name=”SQLServerConnectionString”
connectionString= “server=myServer;database=myDatabase;uid=username;pwd=password”
providerName=”System.Data.SqlClient” />
</connectionStrings>
</configuration>
@{
var db = Database.Open(“SQLServerConnectionString”);
}
Posted in 
