public int Insert(PL_TeporaryDuty pl, string xmlDoc)
{
con.cmd.CommandText = "USP_Temporary_Duty";
con.cmd.CommandType = CommandType.StoredProcedure;
con.cmd.Parameters.AddWithValue("@sptype", pl.sptype);
con.cmd.Parameters.AddWithValue("@ECODE", pl.ECODE);
con.cmd.Parameters.AddWithValue("@EMP_NAME", pl.EMP_NAME);
con.cmd.Parameters.AddWithValue("@DoB", pl.DoB);
con.cmd.Parameters.AddWithValue("@Levels", pl.Levels);
con.cmd.Parameters.AddWithValue("@Cells", pl.Cells);
con.cmd.Parameters.AddWithValue("@DateTime_Of_Start", pl.DateTime_Of_Start);
con.cmd.Parameters.AddWithValue("@Gpf_Pran_No", pl.Gpf_Pran_No);
con.cmd.Parameters.AddWithValue("@BasicPay", pl.BasicPay);
con.cmd.Parameters.AddWithValue("@Ranks", pl.Ranks);
con.cmd.Parameters.AddWithValue("@HeadqtrOffice", pl.HeadqtrOffice);
con.cmd.Parameters.AddWithValue("@OrderForMove_Duty", pl.OrderForMove_Duty);
con.cmd.Parameters.AddWithValue("@AuthorityRule_TR_SR", pl.AuthorityRule_TR_SR);
con.cmd.Parameters.AddWithValue("@StationFrom_Journey_commenced", pl.StationFrom_Journey_commenced);
con.cmd.Parameters.AddWithValue("@EmpType", pl.EmpType);
con.cmd.Parameters.AddWithValue("@fin_year", pl.fin_year);
con.cmd.Parameters.AddWithValue("@GPS_NPS", pl.GPS_NPS);
con.cmd.Parameters.AddWithValue("@TableID", pl.TableID);
con.cmd.Parameters.AddWithValue("@freeze_flage", pl.freeze_flage);
con.cmd.Parameters.AddWithValue("@refrence_no", pl.refrence_no);
con.cmd.Parameters.AddWithValue("@CREATEDBY", pl.CreatedBy);
con.cmd.Parameters.AddWithValue("@xmlDoc", xmlDoc);
SqlParameter p2 = new SqlParameter("@P_MSG", SqlDbType.VarChar, 200);
p2.Direction = ParameterDirection.Output;
con.cmd.Parameters.Add(p2);
try
{
con.Open();
int RetValue = con.cmd.ExecuteNonQuery();
pl.MSG = con.cmd.Parameters["@P_MSG"].Value.ToString();
return(RetValue);
}
catch (Exception ex)
{
// pl.MSG = con.cmd.Parameters["@P_MSG"].Value.ToString();
con.Close();
return(0);
}
finally
{
con.Close();
con.cmd.Dispose();
}
}
public string getDBMessages()
{
try
{
if (MyConnection.State == System.Data.ConnectionState.Open)
{
MyConnection.Close();
}
MyConnection.Open();
OdbcCommand cmd = new OdbcCommand("Select message from messages where name=?", MyConnection);
cmd.Parameters.Add("@email", OdbcType.VarChar, 255).Value = "human";
OdbcDataReader dr = cmd.ExecuteReader();
ArrayList values = new ArrayList();
while (dr.Read())
{
string messagev = dr[0].ToString();
}
MyConnection.Close();
return messagev;
}
catch (Exception ex)
{
return ex.Message;
}
}
public Property GetPropertyById(int id)
{
if (MyConnection.State == System.Data.ConnectionState.Closed)
{
MyConnection.Open();
}
string sql = "Select id, name, location, username, description From property where id = @id";
NpgsqlCommand cmd = new NpgsqlCommand(sql, MyConnection);
cmd.Parameters.AddWithValue("@id", id);
NpgsqlDataReader reader = cmd.ExecuteReader();
reader.Read();
Property myResults = new Property();
myResults = (new Property()
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
Location = reader.GetString(2),
Username = reader.GetString(3),
Description = reader.GetString(4)
});
return(myResults);
}
public List <Property> GetProperty()
{
//DataAdapter
//DataReader
if (MyConnection.State == System.Data.ConnectionState.Closed)
{
MyConnection.Open();
}
string sql = "Select id, name, price, propertyPicture, location, username, description From property";
NpgsqlCommand cmd = new NpgsqlCommand(sql, MyConnection);
NpgsqlDataReader reader = cmd.ExecuteReader();
List <Property> myResults = new List <Property>();
while (reader.Read())
{
myResults.Add(new Property()
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
Price = reader.GetDecimal(2),
PropertyPicture = reader.GetString(3),
Location = reader.GetString(4),
Username = reader.GetString(5),
Description = reader.GetString(6)
});
}
return(myResults);
}
//Get files of a specified email from db
public List <File> GetFiles(string email)
{
string sql = "Select * from userfiles where file_owner = @email";
NpgsqlCommand cmd = new NpgsqlCommand(sql, MyConnection);
cmd.Parameters.AddWithValue("@email", email);
List <File> outputList = new List <File>();
MyConnection.Open();
using (NpgsqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
outputList.Add(new File()
{
Id = reader.GetInt32(0),
FileOwner = reader.GetString(1),
FileTitle = reader.GetString(2),
Link = reader.GetString(3)
});
}
}
MyConnection.Close();
return(outputList);
}
public DataTable Bindgrid(PL_Designation pl)
{
MyConnection con = new MyConnection();
DataTable dt = new DataTable();
con.Open();
con.cmd.CommandText = "Sp_Designation";
con.cmd.CommandType = CommandType.StoredProcedure;
con.cmd.Parameters.Add("@ParentID", pl.ParentID);
con.cmd.Parameters.Add("@sptype", pl.sptype);
SqlParameter p2 = new SqlParameter("@msg", SqlDbType.VarChar, 2000);
p2.Direction = ParameterDirection.Output;
con.cmd.Parameters.Add(p2);
SqlParameter p1 = new SqlParameter("@ID", SqlDbType.Int);
p1.Direction = ParameterDirection.InputOutput;
p1.Value = pl.TableID;
con.cmd.Parameters.Add(p1);
SqlDataReader sdr;
sdr = con.cmd.ExecuteReader();
dt.Load(sdr);
con.Close();
return(dt);
}
public async Task <T> SingleOrDefaultAsync()
{
try
{
if (MyConnection.State == ConnectionState.Closed)
{
MyConnection.Open();
}
using (var command = MyConnection.CreateCommand())
{
command.CommandText = SqlStr;
if (MyParameters != null && MyParameters.Length > 0)
{
command.Parameters.AddRange(MyParameters);
}
return((T)await command.ExecuteScalarAsync().ConfigureAwait(false));
}
}
catch (Exception exp)
{
throw exp;
}
finally
{
if (MyConnection.State == ConnectionState.Open)
{
MyConnection.Close();
}
}
}
//Adds a new file.
public void AddFile(File file)
{
bool isConnOpen = false;
string sql = "INSERT INTO userfiles (file_owner, file_title, link) VALUES(@fileOwner, @fileTitle, @link)";
NpgsqlCommand cmd = new NpgsqlCommand(sql, MyConnection);
cmd.Parameters.AddWithValue("@fileOwner", file.FileOwner);
cmd.Parameters.AddWithValue("@fileTitle", file.FileTitle);
cmd.Parameters.AddWithValue("@link", file.Link);
if (MyConnection.State == System.Data.ConnectionState.Closed)
{
MyConnection.Open();
isConnOpen = true;
MyTransaction = MyConnection.BeginTransaction();
}
if (MyTransaction != null)
{
//used to participate in an opened trasaction happening somewhere else
//assign the Transaction property to the opened transaction
cmd.Transaction = MyTransaction;
}
cmd.ExecuteNonQuery();
MyTransaction.Commit();
if (isConnOpen)
{
MyConnection.Close();
isConnOpen = false;
}
}
public void DeleteProduct(int id)
{
string sql = "Delete from products where Id = @id";
NpgsqlCommand cmd = new NpgsqlCommand(sql, MyConnection);
cmd.Parameters.AddWithValue("@id", id);
bool connectionOpenedInThisMethod = false;
if (MyConnection.State == System.Data.ConnectionState.Closed)
{
MyConnection.Open();
connectionOpenedInThisMethod = true;
}
if (MyTransaction != null)
{
cmd.Transaction = MyTransaction; //to participate in the opened trasaction (somewhere else), assign the Transaction property to the opened transaction
}
cmd.ExecuteNonQuery();
if (connectionOpenedInThisMethod == true)
{
MyConnection.Close();
}
}
public virtual int Delete(PL_DepartmentMaster prp)
{
MyConnection Mycon = new MyConnection();
Mycon.cmd.CommandText = "[USP_M_DEPARTMENT_MASTER]";
Mycon.cmd.CommandType = CommandType.StoredProcedure;
Mycon.cmd.Parameters.AddWithValue("@spType", prp.sptype);
Mycon.cmd.Parameters.AddWithValue("@TableID", prp.TableID);
SqlParameter p2 = new SqlParameter("@msg", SqlDbType.VarChar, 200);
p2.Direction = ParameterDirection.Output;
Mycon.cmd.Parameters.Add(p2);
try
{
Mycon.Open();
int retvalue = Mycon.cmd.ExecuteNonQuery();
Mycon.Close();
prp.msg = Mycon.cmd.Parameters["@msg"].Value.ToString();
return(retvalue);
}
catch (Exception ex)
{
prp.msg = ex.Message.ToString();
return(0);
}
finally
{
if (Mycon.Mycon.State == ConnectionState.Open)
{
Mycon.Close();
}
Mycon.cmd.Dispose();
}
}
public List <Product> GetProducts(string email)
{
string sql = "Select Id, Name, Price, Ownerfk from products where ownerfk=@email";
NpgsqlCommand cmd = new NpgsqlCommand(sql, MyConnection);
cmd.Parameters.AddWithValue("@email", email);
MyConnection.Open();
List <Product> results = new List <Product>();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Product p = new Product();
p.Id = reader.GetInt32(0);
p.Name = reader.GetString(1);
p.Price = reader.GetDouble(2);
p.OwnerFk = reader.GetString(3);
results.Add(p);
}
}
MyConnection.Close();
return(results);
}
public List <Product> GetProducts()
{
string sql = "Select Id, Name, File, Ownerfk, Shareuser from products ";
NpgsqlCommand cmd = new NpgsqlCommand(sql, MyConnection);
MyConnection.Open();
List <Product> results = new List <Product>();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Product p = new Product();
p.Id = reader.GetInt32(0);
p.Name = reader.GetString(1);
p.File = reader.GetString(2);
p.OwnerFK = reader.GetString(3);
p.Shareuser = reader.GetString(4);
results.Add(p);
}
}
MyConnection.Close();
return(results);
}
public DataTable bindDDL(string name)
{
MyConnection con = new MyConnection();
con.Open();
con.cmd.CommandText = "[Sp_Designation]";
con.cmd.CommandType = CommandType.StoredProcedure;
con.cmd.Parameters.Add("@Name", SqlDbType.VarChar, 100).Value = name;
SqlParameter p2 = new SqlParameter("@msg", SqlDbType.VarChar, 2000);
p2.Direction = ParameterDirection.Output;
con.cmd.Parameters.Add(p2);
con.cmd.Parameters.Add("@ID", SqlDbType.VarChar, 100).Value = 0;
con.cmd.Parameters.Add("@Sptype", SqlDbType.Int).Value = 4;
DataTable dt = new DataTable();
SqlDataReader sdr;
sdr = con.cmd.ExecuteReader();
dt.Load(sdr);
con.Close();
return(dt);
}
public File GetFile(int id)
{
string sql = "Select Id, Name, Description, Ownerfk, Link from files where Id=@id";
NpgsqlCommand cmd = new NpgsqlCommand(sql, MyConnection);
cmd.Parameters.AddWithValue("@id", id);
MyConnection.Open();
File file = new File();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
file.Id = reader.GetInt32(0);
file.Name = reader.GetString(1);
file.Description = reader.GetString(2);
file.OwnerFk = reader.GetString(3);
file.Link = reader.GetString(4);
}
}
MyConnection.Close();
return(file);
}
public List <File> GetFiles(string email)
{
string sql = "Select Id, Name, Description, Ownerfk from files where ownerfk=@email";
NpgsqlCommand cmd = new NpgsqlCommand(sql, MyConnection);
cmd.Parameters.AddWithValue("@email", email);
MyConnection.Open();
List <File> results = new List <File>();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
File f = new File();
f.Id = reader.GetInt32(0);
f.Name = reader.GetString(1);
f.Description = reader.GetString(2);
f.OwnerFk = reader.GetString(3);
results.Add(f);
}
}
MyConnection.Close();
return(results);
}
public void ConnectedTest()
{
var mock = new Mock <IMyDriver>();
var client = new MyConnection(new[] { mock.Object });
bool flag = false;
client.Connected += (sender, args) => flag = true;
client.Open();
Assert.IsTrue(flag);
}
public void UpdatePingStatus(string AirlinePrefix, string MAWBNumber, StreamWriter strWriter)
{
try
{
System.Data.OleDb.OleDbConnection MyConnection = null;
System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
if (Trackername.Contains("NEW"))
{
//new tracker
MyConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes; DATABASE=http://klsstg03/pingSD/Support/;LIST={DEFD5BEC-D6A2-400E-9F98-8F6E653A2CE9};VIEW={CC4F76AA-FA1B-44D7-9611-665077F0E451};"); //tracker new
}
else if (Trackername.Contains("OLD"))
{
//old tracker
MyConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes; DATABASE=http://klsstg03/pingSD/;LIST={5D0E59F0-E498-4EF0-BBC1-C2FCF7C9150F};VIEW={87B39BBA-9465-4B2E-87D1-FE88E735EEA6};"); //tracker old
}
MyConnection.Open();
myCommand.Connection = MyConnection;
string DateStamp = System.DateTime.Now.ToString("dd/MM/yyyy H:mm");
string sql = "Update [Table1] set Shipment_Status='4 - Updated in PING' where Pfx='" + AirlinePrefix + "' AND AWB_No='" + MAWBNumber + "' AND Shipment_Status='2 - Ack'";
//, PDF_Sent_At = '" +DateStamp + "'
myCommand.CommandText = sql;
myCommand.ExecuteNonQuery();
sql = "";
MyConnection.Close();
MyConnection.Dispose();
Console.WriteLine("Status updated successfully for AWB " + MAWBNumber + ".\n");
Console.WriteLine("Status updated successfully for AWB " + MAWBNumber + ".\n");
strWriter.WriteLine(AirlinePrefix + "-" + MAWBNumber + " status Updated successfully as '4 - Updated in PING'");
strWriter.WriteLine(" ");
strWriter.WriteLine("-------------------------------------------------------");
strWriter.WriteLine(" ");
if (MyConnection.State == ConnectionState.Open)
{
MyConnection.Close();
MyConnection.Dispose();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
strWriter.WriteLine(MAWBNumber + " could not be Updated " + ex.Message);
strWriter.WriteLine(" ");
strWriter.WriteLine("-------------------------------------------------------");
strWriter.WriteLine(" ");
}
}
//Updates the LastLoggedIn to the current date/time.
public void UpdateLastLoggedIn(string email)
{
string sql = "UPDATE users set lastloggedin = @lastLoggedIn WHERE email = @email";
NpgsqlCommand cmd = new NpgsqlCommand(sql, MyConnection);
cmd.Parameters.AddWithValue("@LastLoggedIn", DateTime.Now);
cmd.Parameters.AddWithValue("@email", email);
MyConnection.Open();
cmd.ExecuteNonQuery();
MyConnection.Close();
}
public void ExportToGrid(String path)
{
try
{
System.Data.DataTable dt = new System.Data.DataTable();
OleDbConnection MyConnection = null;
DataSet DtSet = null;
OleDbDataAdapter MyCommand = null; //Connection for MS Excel 2003 .xls format
string sheetname = "Sheet1";
// MyConnection = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + path + "';Extended Properties=Excel 8.0;");
//Connection for .xslx 2007 format //
if (path.Substring(path.LastIndexOf('.')).ToLower() == ".xlsx")
{
MyConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + path + "';Extended Properties=Excel 12.0;"); //Select your Excel file
}
else
{
MyConnection = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + path + "';Extended Properties= 'Excel 8.0;HDR=YES;IMEX=1;';");
}
MyConnection.Open();
System.Data.DataTable schemaTable = MyConnection.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
if (schemaTable.Rows.Count > 0)
{
sheetname = schemaTable.Rows[0]["TABLE_NAME"].ToString();
}
MyConnection.Close();
MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetname + "]", MyConnection);
DtSet = new System.Data.DataSet(); //Bind all excel data in to data set
MyCommand.Fill(DtSet, "[" + sheetname + "]");
dt = DtSet.Tables[0];
MyConnection.Close(); //Check datatable have records
if (dt.Rows.Count > 0)
{
gvUploadKitty.DataSource = dt;
gvUploadKitty.DataBind();
ViewState["kittyTable"] = dt;
} //Delete temporary Excel file from the Server path
if (System.IO.File.Exists(path))
{
System.IO.File.Delete(path);
}
}
catch (Exception ex)
{
string js = "alert('The uploaded file is not in correct format !!');"; //"alert('" + ex + "');";
ScriptManager.RegisterStartupScript(this, this.GetType(), "script2", js, true);
}
}
public int Insert(PL_M_TuitionFee pl, string xmlDoc)
{
con.cmd.CommandText = "USP_M_TuitionFee";
con.cmd.CommandType = CommandType.StoredProcedure;
con.cmd.Parameters.AddWithValue("@sptype", pl.sptype);
con.cmd.Parameters.AddWithValue("@ECODE", pl.ECODE);
con.cmd.Parameters.AddWithValue("@EMP_NAME", pl.EMP_NAME);
con.cmd.Parameters.AddWithValue("@EmpType", pl.EmpType);
con.cmd.Parameters.AddWithValue("@fin_year", pl.fin_year);
con.cmd.Parameters.AddWithValue("@GPS_NPS", pl.GPS_NPS);
con.cmd.Parameters.AddWithValue("@TableID", pl.TableID);
con.cmd.Parameters.AddWithValue("@freeze_flage", pl.freeze_flage);
con.cmd.Parameters.AddWithValue("@refrence_no", pl.refrence_no);
con.cmd.Parameters.AddWithValue("@CREATEDBY", pl.CreatedBy);
con.cmd.Parameters.AddWithValue("@xmlDoc", xmlDoc);
SqlParameter p2 = new SqlParameter("@P_MSG", SqlDbType.VarChar, 200);
p2.Direction = ParameterDirection.Output;
con.cmd.Parameters.Add(p2);
try
{
con.Open();
int RetValue = con.cmd.ExecuteNonQuery();
pl.MSG = con.cmd.Parameters["@P_MSG"].Value.ToString();
return(RetValue);
}
catch (Exception ex)
{
// pl.MSG = con.cmd.Parameters["@P_MSG"].Value.ToString();
con.Close();
return(0);
}
finally
{
con.Close();
con.cmd.Dispose();
}
}
public void AddProduct(Product p)//string name, string file, string ownerfk)
{
string sql = "INSERT into products (name, file, ownerfk, shareuser) values (@name, @file, @ownerfk, @shareuser)";
NpgsqlCommand cmd = new NpgsqlCommand(sql, MyConnection);
cmd.Parameters.AddWithValue("@name", p.Name);
cmd.Parameters.AddWithValue("@file", p.File);
cmd.Parameters.AddWithValue("@ownerfk", p.OwnerFK);
cmd.Parameters.AddWithValue("@shareuser", p.Shareuser);
MyConnection.Open();
cmd.ExecuteNonQuery();
MyConnection.Close();
}
public void AddFile(string name, string description, string ownerfk, string link)
{
string sql = "INSERT into files (name, description, ownerfk, link) values (@name, @description, @ownerfk, @link)";
NpgsqlCommand cmd = new NpgsqlCommand(sql, MyConnection);
cmd.Parameters.AddWithValue("@name", name);
cmd.Parameters.AddWithValue("@description", description);
cmd.Parameters.AddWithValue("@ownerfk", ownerfk);
cmd.Parameters.AddWithValue("@link", link);
MyConnection.Open();
cmd.ExecuteNonQuery();
MyConnection.Close();
}
public void AddFile(string filename, string fileurl, string receiver, int uid)
{
string sql = "INSERT INTO files (filename, fileurl, receiver, user_id) VALUES (@filename, @fileurl, @receiver, @user_id)";
NpgsqlCommand cmd = new NpgsqlCommand(sql, MyConnection);
cmd.Parameters.AddWithValue("@filename", filename);
cmd.Parameters.AddWithValue("@fileurl", fileurl);
cmd.Parameters.AddWithValue("@receiver", receiver);
cmd.Parameters.AddWithValue("@user_id", uid);
MyConnection.Open();
cmd.ExecuteNonQuery();
MyConnection.Close();
}
public void AddUser(string email, string name, string surname)
{
string sql = "INSERT into users (email, name, surname, lastloggedin) values (@email, @name, @surname, @lastloggedin)";
NpgsqlCommand cmd = new NpgsqlCommand(sql, MyConnection);
cmd.Parameters.AddWithValue("@email", email);
cmd.Parameters.AddWithValue("@name", name);
cmd.Parameters.AddWithValue("@surname", surname);
cmd.Parameters.AddWithValue("@lastloggedin", DateTime.Now);
MyConnection.Open();
cmd.ExecuteNonQuery();
MyConnection.Close();
}
//Add user
public void AddUser(string email, string name, string surname)
{
//To avoid sql injection we add the values using parameters such as @email.
string sql = "INSERT INTO users (email, name, surname, lastloggedin) VALUES(@email, @name, @surname, @lastLoggedIn)";
NpgsqlCommand cmd = new NpgsqlCommand(sql, MyConnection);
cmd.Parameters.AddWithValue("@email", email);
cmd.Parameters.AddWithValue("@name", name);
cmd.Parameters.AddWithValue("@surname", surname);
cmd.Parameters.AddWithValue("@lastLoggedIn", DateTime.UtcNow);
//Connection is Opend and closed after Adding the new user
MyConnection.Open();
cmd.ExecuteNonQuery();
MyConnection.Close();
}
public bool DoesEmailExist(string email)
{
string sql = "Select Count(*) from users where email = @email";
NpgsqlCommand cmd = new NpgsqlCommand(sql, MyConnection);
cmd.Parameters.AddWithValue("@email", email);
MyConnection.Open();
bool result = Convert.ToBoolean(cmd.ExecuteScalar());
MyConnection.Close();
return(result);
}
public virtual int insert(PL_DepartmentMaster prp)
{
MyConnection Mycon = new MyConnection();
Mycon.cmd.CommandText = "[USP_M_DEPARTMENT_MASTER]";
Mycon.cmd.CommandType = CommandType.StoredProcedure;
Mycon.cmd.Parameters.AddWithValue("@DepartmentCode", prp.DepartmentCode);
Mycon.cmd.Parameters.AddWithValue("@sptype", prp.sptype);
Mycon.cmd.Parameters.AddWithValue("@DepartmentName", prp.DepartmentName);
Mycon.cmd.Parameters.AddWithValue("@PayBillSignAuth", prp.PayBillSighAuth);
Mycon.cmd.Parameters.AddWithValue("@PayBillSighAuthUpload", prp.PayBillSighAuthUpload);
Mycon.cmd.Parameters.AddWithValue("@CreatedBy", prp.CreatedBy);
SqlParameter p1 = new SqlParameter("@TableID", SqlDbType.VarChar, 10);
p1.Direction = ParameterDirection.InputOutput;
p1.Value = prp.TableID;
Mycon.cmd.Parameters.Add(p1);
SqlParameter p2 = new SqlParameter("@msg", SqlDbType.VarChar, 200);
p2.Direction = ParameterDirection.Output;
Mycon.cmd.Parameters.Add(p2);
try
{
Mycon.Open();
int RetValue = Mycon.cmd.ExecuteNonQuery();
Mycon.Close();
prp.msg = Mycon.cmd.Parameters["@msg"].Value.ToString();
return(RetValue);
}
catch (Exception ex)
{
prp.msg = ex.Message.ToString();
return(0);
}
finally
{
if (Mycon.Mycon.State == ConnectionState.Open)
{
Mycon.Close();
}
Mycon.cmd.Dispose();
}
}
public virtual int Insert(PL_Designation pl)
{
MyConnection con = new MyConnection();
con.cmd.CommandText = "[Sp_Designation]";
con.cmd.CommandType = CommandType.StoredProcedure;
con.cmd.Parameters.Add("@HName", pl.HName);
con.cmd.Parameters.Add("@SHName", pl.SHName);
con.cmd.Parameters.Add("@Name", pl.Name);
con.cmd.Parameters.Add("@sptype", pl.sptype);
con.cmd.Parameters.Add("@ParentID", pl.ParentID);
con.cmd.Parameters.Add("@TreeLevel", pl.TreeLevel);
con.cmd.Parameters.Add("@ParentDesignationID", pl.MyList);
con.cmd.Parameters.Add("@DesignationID", pl.TableID);
SqlParameter p1 = new SqlParameter("@ID", SqlDbType.Int);
p1.Value = pl.TableID;
p1.Direction = ParameterDirection.InputOutput;
con.cmd.Parameters.Add(p1);
SqlParameter p2 = new SqlParameter("@msg", SqlDbType.VarChar, 200);
p2.Direction = ParameterDirection.Output;
con.cmd.Parameters.Add(p2);
try
{
con.Open();
int RetValue = con.cmd.ExecuteNonQuery();
con.Close();
pl.msg = con.cmd.Parameters["@msg"].Value.ToString();
return(RetValue);
}
catch (Exception ex)
{
pl.msg = ex.Message;
return(0);
}
finally
{
if (con.Mycon.State == ConnectionState.Open)
{
con.Close();
}
con.cmd.Dispose();
}
}
public T FirstOrDefault()
{
try
{
if (MyConnection.State == ConnectionState.Closed)
{
MyConnection.Open();
}
using (var command = MyConnection.CreateCommand())
{
command.CommandText = SqlStr;
if (MyParameters != null && MyParameters.Length > 0)
{
command.Parameters.AddRange(MyParameters);
}
var rtnList = new List <T>();
using (var reader = command.ExecuteReader())
{
if (reader.HasRows)
{
rtnList = AutoFillEntity <T>(reader);
}
else
{
rtnList.Add(new T());
}
}
return(rtnList[0]);
}
}
catch (Exception exp)
{
throw exp;
}
finally
{
if (MyConnection.State == ConnectionState.Open)
{
MyConnection.Close();
}
}
}
public async Task <T> FirstOrDefaultAsync()
{
try
{
if (MyConnection.State == ConnectionState.Closed)
{
MyConnection.Open();
}
using (var command = MyConnection.CreateCommand())
{
command.CommandText = SqlStr;
if (MyParameters != null && MyParameters.Length > 0)
{
command.Parameters.AddRange(MyParameters);
}
var rtnList = new List <T>();
using (var reader = await command.ExecuteReaderAsync().ConfigureAwait(false))
{
rtnList = await AutoFillEntityAsync <T>(reader).ConfigureAwait(false);
}
if (rtnList.Count == 0)
{
rtnList.Add(new T());
}
return(rtnList[0]);
}
}
catch (Exception exp)
{
throw exp;
}
finally
{
if (MyConnection.State == ConnectionState.Open)
{
MyConnection.Close();
}
}
}
