public int delPropertysByPnumber(List <string> propertyIDs, string delnote)
{
if (propertyIDs.Count <= 0)
{
return(0);
}
string sqlValue = "";
string sqlstr = "";
string propertyDelPC = Dns.GetHostName();
string propertyDelDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
for (int i = 0; i < propertyIDs.Count; i++)
{
sqlValue = sqlValue + "'" + propertyIDs[i].ToString() + "',";
}
sqlValue = sqlValue.Substring(0, sqlValue.Length - 1);
sqlstr = @"UPDATE propertys set propertyIsDel = 1 , propertyDelPC = '" + propertyDelPC + "' , propertyDelNote='" + delnote + "',propertyDelDate ='" + propertyDelDate + "' WHERE propertyID in (" + sqlValue + ")";
int result = 0;
result = Mysql_SqlHelper.ExecuteNonQuery(sqlstr);
/*
* if (MiddleWare == "1")
* {
* result = MyCatfsg_SqlHelper.ExecuteNonQuery(sqlstr);
* }
* else
* {
* result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sqlstr);
* }
*/
return(result);
}
public int upPrintPropertysByPnumber(string[] pId)
{
if (pId.Length <= 0)
{
return(0);
}
string sqlValue = "";
string sqlstr = "";
string propertyPrintPC = Dns.GetHostName();
for (int i = 0; i < pId.Length; i++)
{
sqlValue = sqlValue + "'" + pId[i].ToString() + "',";
}
sqlValue = sqlValue.Substring(0, sqlValue.Length - 1);
sqlstr = @"UPDATE propertys set propertyPrintTims = propertyPrintTims+1 ,propertyPrintPC ='" + propertyPrintPC + "' WHERE propertyID in (" + sqlValue + ")";
int result = 0;
result = Mysql_SqlHelper.ExecuteNonQuery(sqlstr);
/*
* if (MiddleWare == "1")
* {
* result = MyCatfsg_SqlHelper.ExecuteNonQuery(sqlstr);
* }
* else
* {
* result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sqlstr);
* }
*/
return(result);
}
public DataTable getPropertysByPnumberFromLocalHost(string propertyIDs)
{
string[] propertyID = propertyIDs.Split('|');
string sql = "";
string wherestr = "";
if (propertyID.Length > 0)
{
for (int i = 0; i < propertyID.Length; i++)
{
//('AE9O-0184','AE9O-0183','AE9O-0189')
wherestr = wherestr + "'" + propertyID[i] + "',";
}
}
wherestr = wherestr.Remove(wherestr.Length - 1, 1);
sql = @"SELECT * from propertys WHERE propertyID in (" + wherestr + ")";
DataTable dt = new DataTable();
dt = Mysql_SqlHelper.ExcuteTable(sql);
/*
* if (MiddleWare == "1")
* {
* dt = MyCatfsg_SqlHelper.ExcuteTable(sql);
* }
* else
* {
* dt = Mysqlfsg_SqlHelper.ExcuteTable(sql);
* }
*/
return(dt);
}
public int insertPropertys(DataTable dt)
{
if (dt.Rows.Count <= 0)
{
return(0);
}
//保存前查询是否已有此财编号
string sqlValue = "";
string sqlstr = "";
for (int i = 0; i < dt.Rows.Count; i++)
{
// str=str.Replace("abc","ABC");
sqlValue = sqlValue + "(\"" +
dt.Rows[i]["erpid"].ToString().Replace("\"", "'") + "\",\"" +
dt.Rows[i]["org"].ToString().Replace("\"", "'") + "\",\"" +
dt.Rows[i]["propertyID"].ToString().Replace("\"", "'") + "\",\"" +
dt.Rows[i]["propertyName"].ToString().Replace("\"", "'") + "\",\"" +
dt.Rows[i]["propertyMode"].ToString().Replace("\"", "'") + "\",\"" +
dt.Rows[i]["propertyType"].ToString().Replace("\"", "'") + "\",\"" +
dt.Rows[i]["buyDate"].ToString().Replace("\"", "'") + "\",\"" +
dt.Rows[i]["propertyDept"].ToString().Replace("\"", "'") + "\",\"" +
dt.Rows[i]["propertyLocal"].ToString().Replace("\"", "'") + "\",\"" +
dt.Rows[i]["propertyBuyID"].ToString().Replace("\"", "'") + "\",\"" +
dt.Rows[i]["propertySavePerson"].ToString().Replace("\"", "'") + "\",\"" +
dt.Rows[i]["propertyUnit"].ToString().Replace("\"", "'") + "\",\"" +
dt.Rows[i]["propertyPrintTims"].ToString().Replace("\"", "'") + "\",\"" +
dt.Rows[i]["propertyIsDel"].ToString().Replace("\"", "'") + "\"),";
}
sqlValue = sqlValue.Substring(0, sqlValue.Length - 1) + ";";
sqlstr = @"INSERT INTO propertys (
erpid,
org,
propertyID,
propertyName,
propertyMode,
propertyType,
buyDate,
propertyDept,
propertyLocal,
propertyBuyID,
propertySavePerson,
propertyUnit,
propertyPrintTims,
propertyIsDel
) VALUES " + sqlValue;
int result = 0;
result = Mysql_SqlHelper.ExecuteNonQuery(sqlstr); //MySqlconnStr MySqlconnStr
/*
*
* if (MiddleWare == "1")
* {
* result = MyCatfsg_SqlHelper.ExecuteNonQuery(sqlstr);
* }
* else
* {
* result = Mysqlfsg_SqlHelper.ExecuteNonQuery(sqlstr);
* }
*/
return(result);
}
/// <summary>
/// 数据库连接操作，可替换为你自己的程序
/// </summary>
/// <param name="ConnectionString">连接字符串</param>
/// <returns></returns>
public List <string> TestConnection(string serName)
{
List <string> lists = new List <string>();
switch (serName)
{
case "ERPconnStr":
try
{
string sql = "select TABLE_NAME from all_tab_comments where ROWNUM <20";
DataTable dt = ERP_SqlHelper.ExcuteTable(sql);
if (dt.Rows.Count <= 0)
{
lists.Add("连接数据库错误");
}
else
{
for (int i = 0; i < dt.Rows.Count; i++)
{
lists.Add(dt.Rows[i]["TABLE_NAME"].ToString());
}
}
return(lists);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
lists.Add("连接数据库错误");
return(lists);
}
case "BESTconnStr":
try
{
string sql = "Select Name TABLE_NAME From Master..SysDatabases order By Name";
DataTable dt = BEST_SqlHelper.ExcuteTable(sql, serName);
if (dt.Rows.Count <= 0)
{
lists.Add("连接数据库错误");
}
else
{
for (int i = 0; i < dt.Rows.Count; i++)
{
lists.Add(dt.Rows[i]["TABLE_NAME"].ToString());
}
}
return(lists);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
lists.Add("连接数据库错误");
return(lists);
}
case "BESTconnStr_KM":
try
{
string sql = "Select Name TABLE_NAME From Master..SysDatabases order By Name";
DataTable dt = BEST_SqlHelper.ExcuteTable(sql, serName);
if (dt.Rows.Count <= 0)
{
lists.Add("连接数据库错误");
}
else
{
for (int i = 0; i < dt.Rows.Count; i++)
{
lists.Add(dt.Rows[i]["TABLE_NAME"].ToString());
}
}
return(lists);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
lists.Add("连接数据库错误");
return(lists);
}
case "MySqlconnStr":
try
{
string sql = "SHOW TABLES; ";
DataTable dt = Mysql_SqlHelper.ExcuteTable(sql);
if (dt.Rows.Count <= 0)
{
lists.Add("连接数据库错误");
}
else
{
for (int i = 0; i < dt.Rows.Count; i++)
{
lists.Add(dt.Rows[i]["TABLE_NAME"].ToString());
}
}
return(lists);
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine(ex.ToString());
lists.Add("连接数据库错误");
return(lists);
}
default:
lists.Add("未知错误");
return(lists);
}
}