public bool UpdateEntity(SalesOrdersModel entity)
{
//OrderNumber 这儿写上吧，实际业务里不能被修改
string sql = string.Format("Update salesOrders Set orderNumber='{0}',customerId={1},customerName='{2}',telphone='{3}',address='{4}',contract='{5}',orderDate='{6}' Where id={7}",
entity.OrderNumber, entity.CustomerId, entity.CustomerName, entity.Tel, entity.Address, entity.Contract, entity.OrderDate, entity.ID);
return(MSSqlHelper.ExecuteNonQuery(MSSqlHelper.ConStr, CommandType.Text, sql, null) > 0);
}
public static void DeleteSendMail(this MSSqlHelper db, Mail mail)
{
try
{
db.ExcuteSQL("DELETE FROM QueuedItem Where RowID=" + mail.MailID);
}
catch (Exception)
{
}
}
public static void DeleteUserMail(this MSSqlHelper db, Mail mail)
{
try
{
db.ExcuteSQL("delete from mailItem where mailID = " + mail.MailID);
db.ExcuteSQL("delete from mail where mailID = " + mail.MailID);
}
catch (Exception)
{
}
}
public static List <long> GetTitles(this MSSqlHelper db, User user)
{
List <long> titles = new List <long>();
using (DbDataReader reader = db.GetReader("select TitleID from Title where Acquired =1 and CID=" + user.CID)){
while (reader != null && reader.Read())
{
titles.Add(reader.ReadInt64("TitleID"));
}
}
return(titles);
}
void Init()
{
if (Db != null)
{
Db.Close();
}
Db = new MSSqlHelper(Config.ConnectionString);
if (DataHelper == null)
{
DataHelper = new DataHelper(Config);
}
this.PostTask(InitTask);
}
public Model.ManufacturerModel AddEntity(Model.ManufacturerModel entity)
{
string sql = string.Format(@"insert into manufacturer(name,person,telephone,address)
values({0},'{1}','{2}','{3}')", entity.Name, entity.Person, entity.Telphone, entity.Address);
if (MSSqlHelper.ExecuteNonQuery(MSSqlHelper.ConStr, CommandType.Text, sql, null) > 0)
{
return(entity);
}
else
{
return(null);
}
}
public Model.SalesCommodityModel AddEntity(Model.SalesCommodityModel entity)
{
string sql = string.Format("insert into salesCommodity(orderId,commodityId,count,salesPrice,totalPrice) values({0},{1},{2},{3},{4})",
entity.SalesOrderID, entity.CommodityID, entity.Count, entity.SalesPrice, entity.TotalPrice);
if (MSSqlHelper.ExecuteNonQuery(MSSqlHelper.ConStr, System.Data.CommandType.Text, sql, null) > 0)
{
return(entity);
}
else
{
return(null);
}
}
//private int GetNewID()
//{
// string sql = "select s_Customer.nextval from dual";
// return int.Parse(MSSqlHelper.ExecuteScalar(MSSqlHelper.ConStr, CommandType.Text, sql, null).ToString());
//}
public Model.CustomerModel AddEntity(Model.CustomerModel entity)
{
string sql = string.Format(@"insert into customer(name,birthday,address,telphone,totalMoney,remark)
values({0},'{1}','{2}','{3}',{4},{5})", entity.Name, entity.Birthday, entity.Address, entity.Telphone, entity.TotalMoney, entity.Mark);
if (MSSqlHelper.ExecuteNonQuery(MSSqlHelper.ConStr, CommandType.Text, sql, null) > 0)
{
return(entity);
}
else
{
return(null);
}
}
public static bool connectDataBase(this MainForm main, string server, string user, string pwd, string dbname)
{
string connect = MSSqlHelper.MakeConnectString(server, user, pwd, dbname);
bool rs = db.Open(connect);
if (rs)
{
IniHelper helper = new IniHelper(Environment.CurrentDirectory + "/DBIni.ini");
helper.WriteValue("mssql", "server", server.Trim());
helper.WriteValue("mssql", "database", dbname.Trim());
helper.WriteValue("mssql", "user", user.Trim());
helper.WriteValue("mssql", "password", pwd.Trim());
}
return(rs);
}
public List <Trays> Search(System.Collections.Hashtable conditions)
{
using (IUnitOfWork unit = MSSqlHelper.DataContext())
{
try
{
ITraysRep tr = new TraysRep(unit);
return(tr.GetByConditions(conditions));
}
catch
{
return(null);
}
}
}
public Model.CommodityModel AddEntity(Model.CommodityModel entity)
{
entity.ID = GetNewID();
string sql = string.Format(@"insert into Commodity(ID,Name,Type,Manufacturer,Inventory,UnitPrice,Unit)
values({0},'{1}','{2}','{3}',{4},{5},'{6}')", entity.ID, entity.Name, entity.Type, entity.Manufacturer, entity.Inventory, entity.UnitPrice, entity.UnitPrice);
if (MSSqlHelper.ExecuteNonQuery(MSSqlHelper.ConStr, CommandType.Text, sql, null) > 0)
{
return(entity);
}
else
{
return(null);
}
}
//its for web
public DataTable GetAllImagesByVID(string Gid)
{
DataTable dt = new DataTable();
try
{
MSSqlHelper objMSSqlHelper = new MSSqlHelper();
objMSSqlHelper.Parameters.Add(new SqlParameter("@Vid", Gid));
dt = objMSSqlHelper.ExecuteDataSet("GetAllImagesByVID").Tables[0];
}
catch (Exception ex)
{
throw ex;
}
return(dt);
}
public DataTable IsExistEmail(string emailID)
{
DataTable dt = new DataTable();
try
{
MSSqlHelper objMSSqlHelper = new MSSqlHelper();
objMSSqlHelper.Parameters.Add(new SqlParameter("@EmailId", emailID));
dt = objMSSqlHelper.ExecuteDataSet("IsExistEmail").Tables[0];
}
catch (Exception ex)
{
throw ex;
}
return(dt);
}
public Model.PurchaseCommodityModel AddEntity(Model.PurchaseCommodityModel entity)
{
string sql = string.Format("insert into purchaseCommodity(orderId,commodityId,count,purchasePrice,totalPrice) values({0},{1},{2},{3},{4})",
entity.PurchaseOrderID, entity.CommodityID, entity.Count, entity.PurchasePrice, entity.TotalPrice);
if (MSSqlHelper.ExecuteNonQuery(MSSqlHelper.ConStr, System.Data.CommandType.Text, sql, null) > 0)
{
//int curID = Convert.ToInt32(MSSqlHelper.ExecuteScalar(MSSqlHelper.ConStr, CommandType.Text, "select ident_current('purchasecommodity')", null).ToString());
//entity.ID = curID;
return(entity);
}
else
{
return(null);
}
}
public SalesOrdersModel AddEntity(SalesOrdersModel entity)
{
string sql = string.Format("insert into salesOrders(orderNumber,customerId,customerName,telphone,address,contract,orderDate) values('{0}',{1},'{2}','{3}','{4}','{5}','{6}')",
entity.OrderNumber, entity.CustomerId, entity.CustomerName, entity.Tel, entity.Address, entity.Contract, entity.OrderDate);
if (MSSqlHelper.ExecuteNonQuery(MSSqlHelper.ConStr, CommandType.Text, sql, null) > 0)
{
int curID = Convert.ToInt32(MSSqlHelper.ExecuteScalar(MSSqlHelper.ConStr, CommandType.Text, "select ident_current('salesOrders')", null).ToString());
entity.ID = curID;
return(entity);
}
else
{
return(null);
}
}
public int DeleteTourByVID(string vid, int userid)
{
int result = 0;
try
{
MSSqlHelper objMSSqlHelper = new MSSqlHelper();
objMSSqlHelper.Parameters.Add(new SqlParameter("@Vid", vid));
objMSSqlHelper.Parameters.Add(new SqlParameter("@UserId", userid));
result = Convert.ToInt32(objMSSqlHelper.ExecuteNonQuery("DeleteTourByVid"));
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public DataTable GetAutoCompleteData_Condo(string City_PostalCode)
{
DataTable result;
try
{
MSSqlHelper objMSSqlHelper = new MSSqlHelper();
objMSSqlHelper.Parameters.Add(new SqlParameter("@City_PostalCode", City_PostalCode));
DataTable dt = objMSSqlHelper.ExecuteDataSet("GetSearchList_Condo").Tables[0];
result = dt;
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public static void UpdateTraysStatus(List <string> trayIds, TrayStatus status)
{
using (IUnitOfWork unit = MSSqlHelper.DataContext())
{
ValidateMsg <Trays> msg = new ValidateMsg <Trays>()
{
Valid = false
};
ITraysRep tr = new TraysRep(unit);
List <Trays> ts = tr.GetByIds(trayIds);
foreach (Trays t in ts)
{
t.status = (int)status;
}
unit.Submit();
}
}
public DataTable GetFeatures()
{
DataTable result;
try
{
MSSqlHelper objMSSqlHelper = new MSSqlHelper();
string strQuery = "sp_Features";
DataTable dt = objMSSqlHelper.ExecuteDataSet(strQuery).Tables[0];
result = dt;
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public DataTable GetSaleLease_Residential()
{
DataTable result;
try
{
MSSqlHelper objMSSqlHelper = new MSSqlHelper();
string strQuery = "GetSaleLease_Residential";
DataTable dt = objMSSqlHelper.ExecuteDataSet(strQuery).Tables[0];
result = dt;
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public DataTable LoginCheck(string emailid, string Password)
{
DataTable dt = new DataTable();
try
{
MSSqlHelper objMSSqlHelper = new MSSqlHelper();
objMSSqlHelper.Parameters.Add(new SqlParameter("@EmailId", emailid));
objMSSqlHelper.Parameters.Add(new SqlParameter("@Password", Password));
dt = objMSSqlHelper.ExecuteDataSet("SpLoginCheck").Tables[0];
}
catch (Exception ex)
{
throw ex;
}
return(dt);
}
public Model.PurchaseOrdersModel AddEntity(Model.PurchaseOrdersModel entity)
{
string sql = string.Format("insert into purchaseOrders(orderNumber,supplier,person,telphone,address,contract,OrderDate) values({0},'{1}','{2}','{3}','{4}','{5}','{6}')",
entity.OrderNumber, entity.SupplierName, entity.Person, entity.Tel, entity.Address, entity.Contract, entity.OrderDate);
int resRows = MSSqlHelper.ExecuteNonQuery(MSSqlHelper.ConStr, CommandType.Text, sql, null);
if (resRows > 0)
{
int curID = Convert.ToInt32(MSSqlHelper.ExecuteScalar(MSSqlHelper.ConStr, CommandType.Text, "select ident_current('purchaseOrders')", null).ToString());
entity.ID = curID;
return(entity);
}
else
{
return(null);
}
}
public DataTable GetPropertyTypes_Condo()
{
DataTable result;
try
{
MSSqlHelper objMSSqlHelper = new MSSqlHelper();
string strQuery = "GetPropertyTypes_Condo";
DataTable dt = objMSSqlHelper.ExecuteDataSet(strQuery).Tables[0];
result = dt;
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public DataTable FeatureListingTop3(string MlsIDs)
{
DataTable result;
try
{
MSSqlHelper objMSSqlHelper = new MSSqlHelper();
string strQuery = "GetFeatureListingByMLSIDsTop3";
objMSSqlHelper.Parameters.Add(new SqlParameter("@MLSID", MlsIDs));
DataTable dt = objMSSqlHelper.ExecuteDataSet(strQuery).Tables[0];
result = dt;
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
private static void SelectDemo(SqlConnection cn)
{
SqlCommand command = new SqlCommand();
string cmd = @"
SELECT
*
FROM
dbo.Employees
";
SqlDataReader reader = MSSqlHelper.GetMsSqlHelper(cn.ConnectionString).ExecuteReader(CommandType.Text, cmd);
while (reader.Read())
{
Console.WriteLine(String.Format("{0}, {1}", reader[0], reader[1]));
}
reader.Close();
}
public DataTable GetIsValidMLS(string mlsid)
{
DataTable result;
try
{
MSSqlHelper objMSSqlHelper = new MSSqlHelper();
string strQuery = "GetIsValidMLSID";
objMSSqlHelper.Parameters.Add(new SqlParameter("@MLSID", mlsid));
DataTable dt = objMSSqlHelper.ExecuteDataSet(strQuery).Tables[0];
result = dt;
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public int InsertVirtualToolImage(string mlsid, string Vimage)
{
int result = 0;
try
{
MSSqlHelper objMSSqlHelper = new MSSqlHelper();
string strQuery = "Sp_InsertVirtualTour";
objMSSqlHelper.Parameters.Add(new SqlParameter("@MLSID", mlsid));
objMSSqlHelper.Parameters.Add(new SqlParameter("@Image", Vimage));
result = Convert.ToInt32(objMSSqlHelper.ExecuteNonQuery(strQuery));
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
public static List <Mail> ReadRecvMailList(this MSSqlHelper db, User user)
{
List <Mail> mails = new List <Mail>();
if (user == null)
{
return(mails);
}
using (DbDataReader reader = db.GetReader("select mi.itemCount,m.* from Mail as m LEFT JOIN MailItem as mi ON mi.MailID = m.MailID"
+ " where m.ToCID =" + user.CID))
{
while (reader != null && reader.Read())
{
mails.Add(new Mail().AttachBox(reader));
}
}
return(mails);
}
public static List <Mail> ReadSendMailList(this MSSqlHelper db, User user)
{
List <Mail> mails = new List <Mail>();
if (user == null)
{
return(mails);
}
using (DbDataReader reader = db.GetReader("SELECT * FROM QueuedItem WHERE CID =" + user.CID))
{
while (reader != null && reader.Read())
{
Mail item = new Mail(reader);
mails.Add(item);
}
}
return(mails);
}
public DataTable GetAllOrSingleVirtualRecord(string Gid, int userid, int startindex, int endindex)
{
DataTable dt = new DataTable();
try
{
MSSqlHelper objMSSqlHelper = new MSSqlHelper();
objMSSqlHelper.Parameters.Add(new SqlParameter("@Vid", Gid));
objMSSqlHelper.Parameters.Add(new SqlParameter("@UserID", userid));
objMSSqlHelper.Parameters.Add(new SqlParameter("@StartIndex", startindex));
objMSSqlHelper.Parameters.Add(new SqlParameter("@EndIndex", endindex));
dt = objMSSqlHelper.ExecuteDataSet("SpGetAllTourDetails").Tables[0];
}
catch (Exception ex)
{
throw ex;
}
return(dt);
}
