using System.Data.SqlClient;
using System.Collections.Generic;
using System.Configuration; //used for ConfigurationManager class
using System.Data;
namespace InventoryDomain
{
public class SupplierManager
{
public static bool NonQuery(string commandText, CommandType commandType, SqlParameter[] parameters)
{
//connection string retrieved from config file
var connectionString =
ConfigurationManager.ConnectionStrings["InventoryConnection"].ConnectionString;
try
{
using (var connection = new SqlConnection(connectionString))
{
//instantiate the command
var command = new SqlCommand(commandText, connection);
command.CommandType = commandType;
//add any parameters to the command object
if(parameters != null)
{
command.Parameters.AddRange(parameters);
}
//open the connection
connection.Open();
//execute the command
command.ExecuteNonQuery();
return true;
} //closes the connection implicitly/automatically
}
catch (Exception ex)
{
//log the exception
return false;
}
}
public static bool Add(Supplier supplier)
{
//sql command text
var sql = "prSupplierInsert";
//create a SqlParameter object and and include it in an array
var par = new SqlParameter("@Name", supplier.Name);
par.DbType = System.Data.DbType.String; //optional
var par2 = new SqlParameter("@ID", System.Data.SqlDbType.Int);
par2.Direction = System.Data.ParameterDirection.Output;
var pars = new SqlParameter[] { par, par2 };
return NonQuery(sql, CommandType.StoredProcedure, pars);
}
public static bool Update(Supplier supplier)
{
//sql command text
var sql = "UPDATE Supplier SET Name = @name WHERE ID = @id";
//create a SqlParameter object and and include it in an array
var par = new SqlParameter("@name", supplier.Name);
par.DbType = System.Data.DbType.String; //optional
var par2 = new SqlParameter("@id", supplier.Id);
//par2.DbType = System.Data.DbType.Int32; //optional
var pars = new SqlParameter[] { par, par2 };
return NonQuery(sql, CommandType.Text, pars);
}
public static List<Supplier> GetAll()
{
var suppliers = new List<Supplier>();
Supplier supplier; //used in the while loop below
//connection string retrieved from config file
var connectionString =
ConfigurationManager.ConnectionStrings["InventoryConnection"].ConnectionString;
try
{
using (var connection = new SqlConnection(connectionString))
{
//instantiate the command
var command = new SqlCommand("SELECT ID, Name FROM Supplier", connection);
//open the connection
connection.Open();
//execute the command
using (var reader = command.ExecuteReader())
{
var idOrdinal = reader.GetOrdinal("ID");
var nameOrdinal = reader.GetOrdinal("Name");
while (reader.Read())
{
//instantiate and set the state of a Supplier
supplier = new Supplier();
supplier.Id = reader.GetInt32(idOrdinal);
supplier.Name = reader.GetString(nameOrdinal);
//add the supplier to the suppliers collection
suppliers.Add(supplier);
}
}//closes the data reader implicitly
} //closes the connection implicitly/automatically
}
catch (Exception ex)
{
//log the exception
}
return suppliers;
}
public static void RunTransaction()
{
//connection string retrieved from config file
var connectionString =
ConfigurationManager.ConnectionStrings["InventoryConnection"].ConnectionString;
SqlTransaction transaction = null;
try
{
using (var connection = new SqlConnection(connectionString))
{
//open the connection
connection.Open();
transaction = connection.BeginTransaction();
var command = connection.CreateCommand();
//1. insert a new supplier
var sql = "prSupplierInsert";
command.CommandText = sql;
command.Parameters.Add("@Name", SqlDbType.VarChar).Value = "Supplier 2";
command.Parameters.Add("@ID", SqlDbType.Int).Direction = ParameterDirection.Output;
command.CommandType = CommandType.StoredProcedure;
command.Transaction = transaction;
command.ExecuteNonQuery();
//@ID output current index
int id = Convert.ToInt32(command.Parameters["@ID"].Value);
//2. insert a new product a for that supplier
command.Parameters.Clear();
command.CommandText = "prProductInsert";
command.Parameters.Add("@Name", SqlDbType.VarChar).Value = "Product D";
command.Parameters.Add("@Quantity", SqlDbType.Int).Value = 10;
command.Parameters.Add("@Price", SqlDbType.Decimal).Value = 10.10M;
command.Parameters.Add("@SupplierID", SqlDbType.Int).Value = id;
command.ExecuteNonQuery();
//3. insert a new product b for that supplier
command.Parameters.Clear();
command.Parameters.Add("@Name", SqlDbType.VarChar).Value = "Product E";
command.Parameters.Add("@Quantity", SqlDbType.Int).Value = 20;
command.Parameters.Add("@Price", SqlDbType.Decimal).Value = 20.20M;
command.Parameters.Add("@SupplierID", SqlDbType.Int).Value = id;
command.ExecuteNonQuery();
//4. insert a new product c for that supplier
command.Parameters.Clear();
command.Parameters.Add("@Name", SqlDbType.VarChar).Value = "Product F";
command.Parameters.Add("@Quantity", SqlDbType.Int).Value = 30;
command.Parameters.Add("@Price", SqlDbType.Decimal).Value = 30.30M;
command.Parameters.Add("@SupplierID", SqlDbType.Int).Value = id;
command.ExecuteNonQuery();
transaction.Commit();
} //closes the connection implicitly/automatically
}
catch (Exception ex)
{
//log the exception
transaction.Rollback();
}
}
}
}
---------------------------------------------------------------------------
//stored procedure
USE [Inventory]
GO
/****** Object: StoredProcedure [dbo].[prSupplierInsert] Script Date: 2016-09-21 9:41:10 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[prSupplierInsert]
-- Add the parameters for the stored procedure here
@Name VarChar(50),
@ID Int OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
INSERT INTO Supplier(Name) VALUES(@Name)
SET @ID = SCOPE_IDENTITY() --return current index
END
--------------------------------------------------------------------------------------
USE [Inventory]
GO
/****** Object: StoredProcedure [dbo].[prProductInsert] Script Date: 2016-09-21 9:42:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[prProductInsert]
-- Add the parameters for the stored procedure here
@Name VarChar(50),
@Quantity Int,
@Price Decimal,
@SupplierID Int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
INSERT INTO Product (Name,Quantity,Price,SupplierID) VALUES(@Name,@Quantity,@Price,@SupplierID)
END
No comments:
Post a Comment