public static int Insert(ServiceProvider serviceProvider)
{
int result = 0;
using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[Connection.ConnectionName].ConnectionString))
{
string insertstmt = "INSERT INTO [dbo].[ServiceProvider] ([name], [code], [companyid], [sptype], [description], [installationcharges], [servicecharges]) VALUES (@name, @code, @companyid, @sptype, @description, @installationcharges, @servicecharges)";
SqlCommand command = new SqlCommand(insertstmt, myConnection);
command.CommandType = CommandType.Text;
command.Parameters.Add("@name", SqlDbType.VarChar, 50).Value = serviceProvider.Name;
command.Parameters.Add("@code", SqlDbType.VarChar, 3).Value = serviceProvider.Code;
command.Parameters.Add("@companyid", SqlDbType.Int).Value = serviceProvider.CompanyId;
command.Parameters.Add("@sptype", SqlDbType.Int).Value = serviceProvider.ProviderType;
if (serviceProvider.Description == null)
command.Parameters.Add("@description", SqlDbType.VarChar, 200).Value = DBNull.Value;
else
command.Parameters.Add("@description", SqlDbType.VarChar, 200).Value = serviceProvider.Description;
command.Parameters.Add("@installationcharges", SqlDbType.Money).Value = serviceProvider.InstallationCharges;
command.Parameters.Add("@servicecharges", SqlDbType.Money).Value = serviceProvider.ServiceCharges;
DbParameter returnValue;
returnValue = command.CreateParameter();
returnValue.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(returnValue);
myConnection.Open();
command.ExecuteNonQuery();
result = Convert.ToInt32(returnValue.Value);
myConnection.Close();
}
return result;
}
public ActionResult AddServiceProvider(ServiceProvider serviceProvider)
{
if (ModelState.IsValid)
{
try
{
DTHManager.SaveDTHServiceProvider(serviceProvider);
MvcApplication.SetMessage("DTH Service Provider added successfully.");
return RedirectToAction("ViewDTHProviders");
}
catch (Exception ex)
{
MvcApplication.SetMessage(ex.Message);
ViewBag.message = MvcApplication.GetMessage();
}
}
else
{
if (serviceProvider.CompanyId <= 0)
ModelState.AddModelError("", "Specifying Company Name is mandatory.");
}
return View(serviceProvider);
}
public ActionResult EditDTHProvider(ServiceProvider serviceProvider)
{
try
{
DTHManager.UpdateServiceProvider(serviceProvider);
MvcApplication.SetMessage("Service Provider updated successfully.");
}
catch (Exception ex)
{
ModelState.AddModelError("", ex.Message);
}
return RedirectToAction("ViewDTHProviders");
}
public static bool UpdateServiceProvider(ServiceProvider serviceProvider)
{
return ServiceProviderDB.Update(serviceProvider);
}
public static int SaveDTHServiceProvider(ServiceProvider serviceProvider)
{
return ServiceProviderDB.Insert(serviceProvider);
}
public static bool Update(ServiceProvider serviceProvider)
{
using (SqlConnection sqlDBConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[Connection.ConnectionName].ConnectionString))
{
try
{
string sqlstmt = "UPDATE [dbo].[ServiceProvider] SET installationcharges = @installationcharges, servicecharges = @servicecharges where id = @id";
SqlCommand myCommand = new SqlCommand(sqlstmt, sqlDBConnection);
myCommand.CommandType = CommandType.Text;
myCommand.Parameters.AddWithValue("@installationcharges", serviceProvider.InstallationCharges);
myCommand.Parameters.AddWithValue("@servicecharges", serviceProvider.ServiceCharges);
myCommand.Parameters.AddWithValue("@id", serviceProvider.Id);
sqlDBConnection.Open();
myCommand.ExecuteNonQuery();
sqlDBConnection.Close();
}
catch (Exception ex)
{
return false;
}
}
return true;
}
private static ServiceProvider FillDataRecord(IDataRecord myDataRecord)
{
ServiceProvider serviceProvider = new ServiceProvider();
serviceProvider.Id = myDataRecord.GetInt32(myDataRecord.GetOrdinal("Id"));
serviceProvider.Name = myDataRecord.GetString(myDataRecord.GetOrdinal("spname"));
serviceProvider.Code = myDataRecord.GetString(myDataRecord.GetOrdinal("code"));
serviceProvider.CompanyName = myDataRecord.GetString(myDataRecord.GetOrdinal("companyname"));
serviceProvider.ProviderType = (ServiceProviderType)myDataRecord.GetInt32(myDataRecord.GetOrdinal("sptype"));
if (!myDataRecord.IsDBNull(myDataRecord.GetOrdinal("description")))
serviceProvider.Description = myDataRecord.GetString(myDataRecord.GetOrdinal("description"));
else
serviceProvider.Description = "";
serviceProvider.InstallationCharges = myDataRecord.GetDecimal(myDataRecord.GetOrdinal("installationcharges"));
serviceProvider.ServiceCharges = myDataRecord.GetDecimal(myDataRecord.GetOrdinal("servicecharges"));
return serviceProvider;
}