private static Collection <Supplier> Retrieve(EpTransaction epTran, string whereClause, string sortClaues)
{
Collection <Supplier> entities = new Collection <Supplier>();
SqlCommand cm = new SqlCommand();
cm.CommandType = CommandType.Text;
//set connection
SqlConnection connection;
if (epTran == null)
{
connection = DataManager.GetConnection();
}
else
{
connection = epTran.GetSqlConnection();
}
if (connection.State != System.Data.ConnectionState.Open)
{
connection.Open();
}
cm.Connection = connection;
//set transaction
if (epTran != null)
{
cm.Transaction = epTran.GetSqlTransaction();
}
//Retrieve Data
string selectCommand = "SELECT [LIFNR],[PASSWD],[ANRED],[NAME],[ADDRESS],[PSTLZ],[KORT],[REGIO],[LAND1],[TELF1],[TELF2],[TELFX],[SPREQ],[EMAIL],[RECSTS] FROM vndmst";
if (!string.IsNullOrEmpty(whereClause))
{
selectCommand += " where " + whereClause;
}
if (!string.IsNullOrEmpty(sortClaues))
{
selectCommand += " order by " + sortClaues;
}
cm.CommandText = selectCommand;
SqlDataReader rd = cm.ExecuteReader();
while (rd.Read())
{
Supplier entity = new Supplier();
entity.SupplierID = rd["LIFNR"].ToString();
entity.Password = rd["PASSWD"].ToString();
entity.Title = rd["ANRED"].ToString();
entity.SupplierName = rd["NAME"].ToString();
entity.SupplierAddress = rd["ADDRESS"].ToString();
entity.PostalCode = rd["PSTLZ"].ToString();
entity.Country = rd["KORT"].ToString();
entity.Region = rd["REGIO"].ToString();
entity.CountryCode = rd["LAND1"].ToString();
entity.Telephone1 = rd["TELF1"].ToString();
entity.Telephone2 = rd["TELF2"].ToString();
entity.FaxNo = rd["TELFX"].ToString();
entity.SPREQ = rd["SPREQ"].ToString();
entity.EmailID = rd["EMAIL"].ToString();
entity.RecordStatus = rd["RECSTS"].ToString();
entities.Add(entity);
}
// close reader
rd.Close();
if (epTran == null)
{
if (connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
}
}
return(entities);
}
public static void Insert(EpTransaction epTran, Supplier entity)
{
SqlCommand cm = new SqlCommand();
cm.CommandType = CommandType.Text;
//set connection
SqlConnection connection;
if (epTran == null)
{
connection = DataManager.GetConnection();
}
else
{
connection = epTran.GetSqlConnection();
}
if (connection.State != System.Data.ConnectionState.Open)
{
connection.Open();
}
cm.Connection = connection;
//set transaction
if (epTran != null)
{
cm.Transaction = epTran.GetSqlTransaction();
}
//Check whether record exists
Supplier checkEntity = RetrieveByKey(epTran, entity.SupplierID);
if (checkEntity != null)
{
throw new Exception("Record already exists.");
}
//Insert
cm.CommandText = "INSERT INTO vndmst ([LIFNR],[PASSWD],[ANRED],[NAME],[ADDRESS],[PSTLZ],[KORT],[REGIO],[LAND1],[TELF1],[TELF2],[TELFX],[SPREQ],[EMAIL],[RECSTS]) VALUES(@LIFNR,@PASSWD,@ANRED,@NAME,@ADDRESS,@PSTLZ,@KORT,@REGIO,@LAND1,@TELF1,@TELF2,@TELFX,@SPREQ,@EMAIL,@RECSTS))";
SqlParameter p1 = new SqlParameter("@LIFNR", SqlDbType.Char, 10);
cm.Parameters.Add(p1);
p1.Value = entity.SupplierID;
SqlParameter p2 = new SqlParameter("@PASSWD", SqlDbType.Char, 10);
cm.Parameters.Add(p2);
p2.Value = entity.Password;
SqlParameter p3 = new SqlParameter("@ANRED", SqlDbType.VarChar, 15);
cm.Parameters.Add(p3);
p3.Value = entity.Title;
SqlParameter p4 = new SqlParameter("@NAME", SqlDbType.VarChar, 80);
cm.Parameters.Add(p4);
p4.Value = entity.SupplierName;
SqlParameter p5 = new SqlParameter("@ADDRESS", SqlDbType.VarChar, 60);
cm.Parameters.Add(p5);
p5.Value = entity.SupplierAddress;
SqlParameter p6 = new SqlParameter("@PSTLZ", SqlDbType.VarChar, 10);
cm.Parameters.Add(p6);
p6.Value = entity.PostalCode;
SqlParameter p7 = new SqlParameter("@KORT", SqlDbType.VarChar, 40);
cm.Parameters.Add(p7);
p7.Value = entity.Country;
SqlParameter p8 = new SqlParameter("@REGIO", SqlDbType.Char, 3);
cm.Parameters.Add(p8);
p8.Value = entity.Region;
SqlParameter p9 = new SqlParameter("@LAND1", SqlDbType.Char, 3);
cm.Parameters.Add(p9);
p9.Value = entity.CountryCode;
SqlParameter p10 = new SqlParameter("@TELF1", SqlDbType.VarChar, 16);
cm.Parameters.Add(p10);
p10.Value = entity.Telephone1;
SqlParameter p11 = new SqlParameter("@TELF2", SqlDbType.VarChar, 16);
cm.Parameters.Add(p11);
p11.Value = entity.Telephone2;
SqlParameter p12 = new SqlParameter("@TELFX", SqlDbType.VarChar, 31);
cm.Parameters.Add(p12);
p12.Value = entity.FaxNo;
SqlParameter p13 = new SqlParameter("@SPREQ", SqlDbType.Char, 3);
cm.Parameters.Add(p13);
p13.Value = entity.SPREQ;
SqlParameter p14 = new SqlParameter("@EMAIL", SqlDbType.VarChar, 100);
cm.Parameters.Add(p14);
p14.Value = entity.EmailID;
SqlParameter p15 = new SqlParameter("@RECSTS", SqlDbType.Char, 1);
cm.Parameters.Add(p15);
p15.Value = entity.RecordStatus;
cm.ExecuteNonQuery();
if (epTran == null)
{
if (connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
}
}
}
public static void Update(EpTransaction epTran, Supplier entity)
{
SqlCommand cm = new SqlCommand();
cm.CommandType = CommandType.Text;
//set connection
SqlConnection connection;
if (epTran == null)
{
connection = DataManager.GetConnection();
}
else
{
connection = epTran.GetSqlConnection();
}
if (connection.State != System.Data.ConnectionState.Open)
{
connection.Open();
}
cm.Connection = connection;
//set transaction
if (epTran != null)
{
cm.Transaction = epTran.GetSqlTransaction();
}
//Check whether record exists
Supplier checkEntity = RetrieveByKey(epTran, entity.SupplierID);
if (checkEntity == null)
{
throw new Exception("Record doesn't exist.");
}
//Update
cm.CommandText = "UPDATE vndmst SET LIFNR=@LIFNR,VN=@VN,ANRED=@ANRED,NAME=@NAME,ADDRESS=@ADDRESS,PSTLZ=@PSTLZ,KORT=@KORT,REGIO=@REGIO,LAND1=@LAND1,TELF1=@TELF1,TELF2=@TELF2,TELFX=@TELFX,SPREQ=@SPREQ,EMAIL=@EMAIL,RECSTS=@RECSTS WHERE LIFNR=@LIFNR";
SqlParameter p1 = new SqlParameter("@LIFNR", SqlDbType.Char, 10);
cm.Parameters.Add(p1);
p1.Value = entity.SupplierID;
SqlParameter p2 = new SqlParameter("@PASSWD", SqlDbType.Char, 10);
cm.Parameters.Add(p2);
p2.Value = entity.Password;
SqlParameter p3 = new SqlParameter("@ANRED", SqlDbType.VarChar, 15);
cm.Parameters.Add(p3);
p3.Value = entity.Title;
SqlParameter p4 = new SqlParameter("@NAME", SqlDbType.VarChar, 80);
cm.Parameters.Add(p4);
p4.Value = entity.SupplierName;
SqlParameter p5 = new SqlParameter("@ADDRESS", SqlDbType.VarChar, 60);
cm.Parameters.Add(p5);
p5.Value = entity.SupplierAddress;
SqlParameter p6 = new SqlParameter("@PSTLZ", SqlDbType.VarChar, 10);
cm.Parameters.Add(p6);
p6.Value = entity.PostalCode;
SqlParameter p7 = new SqlParameter("@KORT", SqlDbType.VarChar, 40);
cm.Parameters.Add(p7);
p7.Value = entity.Country;
SqlParameter p8 = new SqlParameter("@REGIO", SqlDbType.Char, 3);
cm.Parameters.Add(p8);
p8.Value = entity.Region;
SqlParameter p9 = new SqlParameter("@LAND1", SqlDbType.Char, 3);
cm.Parameters.Add(p9);
p9.Value = entity.CountryCode;
SqlParameter p10 = new SqlParameter("@TELF1", SqlDbType.VarChar, 16);
cm.Parameters.Add(p10);
p10.Value = entity.Telephone1;
SqlParameter p11 = new SqlParameter("@TELF2", SqlDbType.VarChar, 16);
cm.Parameters.Add(p11);
p11.Value = entity.Telephone2;
SqlParameter p12 = new SqlParameter("@TELFX", SqlDbType.VarChar, 31);
cm.Parameters.Add(p12);
p12.Value = entity.FaxNo;
SqlParameter p13 = new SqlParameter("@SPREQ", SqlDbType.Char, 3);
cm.Parameters.Add(p13);
p13.Value = entity.SPREQ;
SqlParameter p14 = new SqlParameter("@EMAIL", SqlDbType.VarChar, 100);
cm.Parameters.Add(p14);
p14.Value = entity.EmailID;
SqlParameter p15 = new SqlParameter("@RECSTS", SqlDbType.Char, 1);
cm.Parameters.Add(p15);
p15.Value = entity.RecordStatus;
cm.ExecuteNonQuery();
if (epTran == null)
{
if (connection.State != System.Data.ConnectionState.Closed)
{
connection.Close();
}
}
}
