Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- using System;
- using System.Configuration;
- using System.Data;
- using System.Data.SqlClient;
- namespace PruebaDB
- {
- class Program
- {
- static void Main(string[] args)
- {
- var arrayIds = new int[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
- Console.WriteLine("============== INSERTANDO REGISTROS EN LA BD =============");
- string name = "John {0}", lastname = "Doe {0}", address = "Dirección {0}.";
- foreach (var id in arrayIds)
- {
- var item = new Item()
- {
- Name = string.Format(name, id),
- LastName = string.Format(lastname, id),
- Address = (id%2) > 0 ? string.Format(address,id):null
- };
- AddItem(item);
- }
- Console.WriteLine("...Listo.");
- Console.WriteLine("\n============== LEYENDO DATOS DE LA BD =============");
- foreach (var id in arrayIds)
- {
- var item = GetItemById(id);
- if(item != null)
- {
- Console.WriteLine("Id:{0} | Name: {1} | LastName: {2} | Address:{3}",
- item.Id, item.Name, item.LastName, item.Address);
- }
- }
- Console.WriteLine("...Listo.");
- Console.WriteLine("\n============= Limpiando Tabla (truncate) =============");
- TruncateTable();
- Console.WriteLine("...Listo.");
- Console.ReadKey();
- }
- private static void AddItem(Item item)
- {
- if (item == null) throw new ArgumentNullException("item");
- try
- {
- var connString = ConfigurationManager.ConnectionStrings["TESTDBConnection"].ConnectionString;
- var sqlCommand = "INSERT INTO Item(Name, LastName, Address) VALUES(@Name, @LastName, @Address)";
- using (var connection = new SqlConnection(connString))
- {
- using (var command = new SqlCommand(sqlCommand, connection))
- {
- command.Parameters.Add("@Name", SqlDbType.NVarChar).Value = item.Name;
- command.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = item.LastName;
- if(string.IsNullOrEmpty(item.Address))
- command.Parameters.Add("@Address", SqlDbType.NVarChar).Value = DBNull.Value;
- else
- command.Parameters.Add("@Address", SqlDbType.NVarChar).Value = item.Address;
- connection.Open();
- command.ExecuteNonQuery();
- }
- }
- }
- catch
- {
- throw;
- }
- }
- //EJECUTANDO A UN STORE PROCEDURE
- private static Item GetItemById(int itemId)
- {
- try
- {
- Item retrievedItem = null;
- var connString = ConfigurationManager.ConnectionStrings["TESTDBConnection"].ConnectionString;
- var sqlCommand = "sp_GetItemById";
- using (var connection = new SqlConnection(connString))
- {
- using (var command = new SqlCommand(sqlCommand, connection))
- {
- command.CommandType = CommandType.StoredProcedure;
- command.Parameters.Add("@ItemId", SqlDbType.Int).Value = itemId;
- connection.Open();
- var reader = command.ExecuteReader();
- if (!reader.HasRows)
- return retrievedItem;
- else
- retrievedItem = new Item();
- while (reader.Read())
- {
- retrievedItem.Id = Convert.ToInt32(reader["Id"]);
- retrievedItem.Name = reader["Name"] as string;
- retrievedItem.LastName = reader["LastName"] as string;
- retrievedItem.Address = reader["Address"] as string;
- }
- return retrievedItem;
- }
- }
- }
- catch
- {
- throw;
- }
- }
- private static void TruncateTable()
- {
- try
- {
- var connString = ConfigurationManager.ConnectionStrings["TESTDBConnection"].ConnectionString;
- var sqlCommand = "TRUNCATE TABLE Item";
- using (var connection = new SqlConnection(connString))
- {
- using (var command = new SqlCommand(sqlCommand, connection))
- {
- connection.Open();
- command.ExecuteNonQuery();
- }
- }
- }
- catch
- {
- throw;
- }
- }
- }//program.cs
- public class Item
- {
- public int Id { get; set; }
- public string Name { get; set; }
- public string LastName { get; set; }
- public string Address { get; set; }
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement