private DataTable dt; //表以及对应的描述信息
public OracleDAL(string conn)
{
help = new OracleHelp(conn);
var strSql = @"select ROWNUM 序号 ,ut.table_name 表名,utc.comments 表说明 from user_tables ut left join user_tab_comments utc on ut.table_name = utc.table_name order by ut.table_name";
dt = help.ExecuteSql(strSql);
strSql = @" select row_number()over( partition by utc.table_name order by utc.COLUMN_ID, ROWNUM ) as 序号,
utc.table_name as 表名,
utc.column_name as 列名,
utc.data_type as 数据类型,
utc.data_length as 长度,
utc.data_precision as 精度,
utc.data_Scale 小数位数,
case when exists ( select col.column_name from user_constraints con,user_cons_columns col
where con.constraint_name=col.constraint_name and con.constraint_type='P' and col.table_name=ucc.table_name and col.column_name = utc.column_name )
then '√' else '' end as 主键,
case when utc.nullable = 'Y' then '√' else '' end as 允许空,
utc.data_default as 默认值,
ucc.comments as 列说明
from
user_tab_columns utc,user_col_comments ucc
where utc.table_name = ucc.table_name and utc.column_name = ucc.column_name
order by utc.table_name,序号";
dtStruct = help.ExecuteSql(strSql);
}
public static DataTable getLastAWStable(string tableName, bool writeCachable = false)
{
lock (lockKey)
{
string ckey = "lastAWSiNFO#" + tableName;
DataTable table = MyCacheManager.Get(ckey) as DataTable;
if (table == null)
{
string select = string.Format(@"select * from(
SELECT ROW_NUMBER() OVER(PARTITION BY obtid ORDER BY ddatetime DESC) LEV,s.* from( select * from (
select * from {0} where WD3SMAXDF>=0 ORDER BY ddatetime desc
) where ROWNUM < 5000 ) s
) WHERE LEV=1", tableName);
table = OracleHelp.ExecuteDataTable(select, T_LOCALOBTDAYD.Tunnel.connString);
if (writeCachable)
{
MyCacheManager.Insert(ckey, table, DateTime.Now.AddMinutes(1), System.Web.Caching.CacheItemPriority.High, null, (string key, object value, CacheItemRemovedReason reason) =>
{
if (reason == CacheItemRemovedReason.Expired)
{
getLastAWStable(key.Split('#')[1], true);
}
});
}
}
return(table);
}
}
private void btnGetAttPrintRecord_Click(object sender, EventArgs e)
{
#region 打卡记录
//打卡记录时间格式带小时分钟，需特殊处理
string sql = @"select *
from smthrm.t_hr_employeeclockinrecord c
where c.employeeid='" + txtEmployeeId.Text + @"'
and c.punchdate>=to_date('" + GlobalParameters.StartDate + @"','yyyy-mm-dd')
and c.punchdate<=to_date('" + GlobalParameters.EndDate + @"','yyyy-mm-dd')
order by c.punchdate";
DataTable dt = OracleHelp.getTable(sql);
if (dt != null)
{
this.dtAttRecord.DataSource = dt;
foreach (DataRow dr in dt.Rows)
{
T_HR_EMPLOYEECLOCKINRECORD entTemp = new T_HR_EMPLOYEECLOCKINRECORD();
entTemp.CLOCKINRECORDID = dr["CLOCKINRECORDID"].ToString();
entTemp.FINGERPRINTID = dr["FINGERPRINTID"].ToString();
entTemp.CLOCKID = dr["CLOCKID"].ToString();
entTemp.PUNCHDATE = DateTime.Parse(dr["PUNCHDATE"].ToString());
entTemp.PUNCHTIME = dr["PUNCHTIME"].ToString();
entTempListAgain.Add(entTemp);
}
OracleHelp.close();
//txtMessagebox.Text = "查询员工打卡记录完成，共：" + dt.Rows.Count.ToString() + "条数据" + System.Environment.NewLine + txtMessagebox.Text;
}
else
{
//txtMessagebox.Text = "查询员工打卡记录完成，共：0条数据" + System.Environment.NewLine + txtMessagebox.Text;
}
#endregion
}
public static string GetHistory(string obtid, DateTime start, DateTime current, TimeMode timeMode, OBTField[] dataField, OBTField keyField)
{
OBTCODE obt = AWSCode.OBTCodeList[obtid];
StringBuilder sb = new StringBuilder();
string tableName = getTableName(start, obt.AREA, timeMode, keyField);
string selectField;
for (int i = 0; i < dataField.Length; i++)
{
if (i > 0)
{
sb.Append(",");
}
sb.Append(dataField[i].ToString());
}
selectField = sb.ToString();
sb.Clear();
sb.AppendFormat("select {0} from {1} where obtid=:obtId AND DDATETIME >= :dstart AND DDATETIME <= :dend and {2} is not null ORDER BY DDATETIME", selectField, tableName, keyField.ToString());
DataTable data = OracleHelp.ExecuteDataTable(sb.ToString(), T_LOCALOBTMIND.Tunnel.connString, new OracleParameter(":obtId", obtid), new OracleParameter(":dstart", start), new OracleParameter(":dend", current));
sb.Clear();
sb.Append("[");
int itemIndex;
int rows = 0;
foreach (DataRow row in data.Rows)
{
if (rows++ > 0)
{
sb.Append(",");
}
itemIndex = 0;
sb.Append("[");
foreach (var item in row.ItemArray)
{
if (itemIndex++ > 0)
{
sb.Append(",");
}
if (item is DateTime)
{
sb.Append("\"");
sb.Append(Utility.DateTimeToJson((DateTime)item));
sb.Append("\"");
}
else
{
sb.Append(item);
}
}
sb.Append("]");
}
sb.Append("]");
return(sb.ToString());
}
/// <summary>
/// 历史差变查询
/// </summary>
public static string GetHistory(string obtid, DateTime current, OBTField fieldName)
{
int offsetHours;
string field = getFieldName(fieldName, out offsetHours);
if (offsetHours == 0)
{
return(null);
}
DateTime start = current.AddHours(offsetHours);
OBTCODE obt = AWSCode.OBTCodeList[obtid];
string tableName = AWSItems.getTableName(start, obt.AREA, TimeMode.MINUTE);
StringBuilder sb = new StringBuilder();
sb.AppendFormat("select DDATETIME,{0} from {1} where obtid=:obtId AND DDATETIME >= :dstart AND DDATETIME<=:dend and {2} is not null ORDER BY DDATETIME", field, tableName, field);
DataTable data = OracleHelp.ExecuteDataTable(sb.ToString(), T_LOCALOBTMIND.Tunnel.connString, new OracleParameter(":obtId", obtid), new OracleParameter(":dstart", start), new OracleParameter(":dend", current));
List <AWSOffsetRow> offsetData = new List <AWSOffsetRow>();
foreach (DataRow row in data.Rows)
{
offsetData.Add(new AWSOffsetRow()
{
DDATETIME = (DateTime)row[0], VALUE = (decimal)row[1]
});
}
sb.Clear();
sb.Append("[");
if (offsetData.Count > 0)
{
int rows = 0;
decimal firstValue = offsetData[0].VALUE;
for (int i = 0; i < offsetData.Count; i++)
{
if (rows++ > 0)
{
sb.Append(",");
}
sb.Append("[");
sb.Append("\"");
sb.Append(Utility.DateTimeToJson(offsetData[i].DDATETIME));
sb.Append("\",");
sb.Append(offsetData[i].VALUE - firstValue);
sb.Append("]");
}
}
sb.Append("]");
return(sb.ToString());
}
public static string GetPHGG(string wlh)
{
AdoHelper oracleHelp = new OracleHelp();
DataSet ds = null;
try
{
ds = oracleHelp.ExecuteDataset(Common.NCDATASTRING, CommandType.Text, "select invtype,invspec from bd_invbasdoc where invcode='" + wlh + "'");
}
catch
{
return("");//NC数据库联接失败
}
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
return(ds.Tables[0].Rows[0][0].ToString() + "," + ds.Tables[0].Rows[0][1].ToString());
}
return("");
}
/// <summary>
/// 自动站属性变值
/// </summary>
List <AWSFieldValue> GetOffsetFromDB(DateTime?date, OBTArea area, OBTField fieldName, int accuracy)
{
int offsetHours;
string field = getFieldName(fieldName, out offsetHours);
if (offsetHours == 0)
{
return(null);
}
DateTime checkTime;
if (date == null)
{
checkTime = DateTime.Now.AddMinutes(-5);
checkTime = checkTime.AddMinutes(-checkTime.Minute % 5);
checkTime = checkTime.AddSeconds(-checkTime.Second);
}
else
{
checkTime = date.Value;
}
List <AWSFieldValue> result = new List <AWSFieldValue>();
string tableName = AWSItems.getTableName(checkTime, area, TimeMode.MINUTE, fieldName);
StringBuilder sb = new StringBuilder();
sb.AppendFormat(@"SELECT A.obtid, (A.{0}-B.{1})/" + accuracy + " FROM (select obtid,{2} from {3} where ddatetime=:dnow and {4} is not null) A inner join(select obtid, {5} from {6} where ddatetime = :dbefore and {7} is not null) B ON A.obtid = B.obtid",
field, field, field, tableName, field, field, tableName, field);
DataTable data = OracleHelp.ExecuteDataTable(sb.ToString(), T_LOCALOBTMIND.Tunnel.connString, new OracleParameter(":dnow", checkTime), new OracleParameter(":dbefore", checkTime.AddHours(offsetHours)));
foreach (DataRow row in data.Rows)
{
if ((double)(decimal)row[1] < 9999)
{
result.Add(new AWSFieldValue()
{
TM = checkTime, ID = row[0].ToString(), V0 = (double)(decimal)row[1]
});
}
}
return(result);
}
private void btnGetEmployeeId_Click(object sender, EventArgs e)
{
string sql = @"select e.employeeename,e.employeeid
from smthrm.t_hr_employee e
where
e.employeecname like '%" + txtEmployeeName.Text + @"%'";
OracleHelp.Connect();
DataTable dt = OracleHelp.getTable(sql);
if (dt.Rows.Count == 1)
{
txtEmployeeName.Text = dt.Rows[0]["employeeename"].ToString();
txtEmployeeId.Text = dt.Rows[0]["employeeid"].ToString();
}
else
{
MessageBox.Show("员工有同名！");
}
OracleHelp.close();
}
List <AWSFieldValue> GetAWSInfoFromDB(DateTime date, OBTArea area, TimeMode aType, OBTField dataField, int accuracy)
{
List <AWSFieldValue> result = new List <AWSFieldValue>();
string tableName = AWSItems.getTableName(date, area, aType, dataField);
if (aType == TimeMode.DAY)
{
date = date.Date;
}
string seleceField = dataField.ToString();
string sql = string.Format("select OBTID,{0}/" + accuracy + " from {1} where DDATETIME=:ddate and {2} is not null", seleceField, tableName, seleceField, seleceField);
DataTable data = OracleHelp.ExecuteDataTable(sql, T_LOCALOBTMIND.Tunnel.connString, new OracleParameter(":ddate", date));
foreach (DataRow row in data.Rows)
{
result.Add(new AWSFieldValue()
{
ID = row[0].ToString(), V0 = (double)(decimal)row[1]
});
}
return(result);
}
/// <summary>
/// 风速
/// </summary>
/// <param name="date"></param>
/// <returns></returns>
public static List <AWSFieldValue> GetAWSWindFromDB(DateTime date, OBTArea area, TimeMode aType, OBTField dataField)
{
string wdf = null, wdd = null;
getQueryField(dataField, out wdf, out wdd);
string tableName = AWSItems.getTableName(date, area, aType);
List <AWSFieldValue> result = new List <AWSFieldValue>();
string selectSQL = string.Format("select OBTID,{0},{1} from {2} where DDATETIME=:ddate AND {3}>=0 AND {4}>=0 ORDER BY {5} DESC", wdf, wdd, tableName, wdf, wdd, wdf);
DataTable data = OracleHelp.ExecuteDataTable(selectSQL, T_LOCALOBTMIND.Tunnel.connString, new OracleParameter(":ddate", aType == TimeMode.DAY ? date.Date : date));
foreach (DataRow row in data.Rows)
{
if ((double)(decimal)row[1] < 9999)
{
result.Add(new AWSFieldValue()
{
ID = row[0].ToString(), V0 = (double)(decimal)row[1], V1 = (double)(decimal)row[2]
});
}
}
return(result);
}
/// <summary>
/// 自动站变温
/// </summary>
public static Dictionary <string, decimal> GetOffsetFromDB(DateTime date, OBTArea area, OBTField fieldName)
{
int offsetHours;
string field = getFieldName(fieldName, out offsetHours);
if (offsetHours == 0)
{
return(null);
}
Dictionary <string, decimal> result = new Dictionary <string, decimal>();
string tableName = AWSItems.getTableName(date, area, TimeMode.MINUTE);
StringBuilder sb = new StringBuilder();
sb.AppendFormat(@"SELECT A.obtid, A.{0}-B.{1} FROM (select obtid,{2} from {3} where ddatetime=:dnow and {4} is not null) A inner join(select obtid, {5} from {6} where ddatetime = :dbefore and {7} is not null) B ON A.obtid = B.obtid",
field, field, field, tableName, field, field, tableName, field);
DataTable data = OracleHelp.ExecuteDataTable(sb.ToString(), T_LOCALOBTMIND.Tunnel.connString, new OracleParameter(":dnow", date), new OracleParameter(":dbefore", date.AddHours(offsetHours)));
foreach (DataRow row in data.Rows)
{
result.Add(row[0].ToString(), (decimal)row[1]);
}
return(result);
}
/// <summary>
/// 通过用户名获取用户信息
/// </summary>
/// <param name="userid">用户ID(工号)</param>
/// <returns></returns>
public HttpResponseMessage GetUserInfoByUserID(string userid)
{
string sql = "Select * From tblUser Where usercode=:userid";//:userid 参数名称必须以冒号开头
OracleParameter[] ops =
{
new OracleParameter(":userid", OracleDbType.Varchar2) //这里的冒号可以省略
};
ops[0].Value = userid;
DataTable dt = OracleHelp.GetDataTable(sql, ops);
string sql2 = "Select * From tblUser Where usercode=:userid or organiZationID=:Orgid";
List <OracleParameter> oracleParameters = new List <OracleParameter>();
OracleParameter oracleParameter1 = new OracleParameter(":userid", OracleDbType.Varchar2)
{
Value = userid
};
oracleParameters.Add(oracleParameter1);
OracleParameter oracleParameter2 = new OracleParameter(":ORGID", OracleDbType.Int32)
{
Value = 181
};
oracleParameters.Add(oracleParameter2);
DataTable dt2 = OracleHelp.GetDataTable(sql2, oracleParameters);
if (dt != dt2)
{
dt = dt2;
}
return(DBHelp.ConvertToHttpResponseMessage(dt));
}
/// <summary>
/// 补充滑动雨量
/// </summary>
void addOldRain(DateTime date, OBTArea area, TimeMode aType, List <AWSFieldValue> target, OBTField dataField)
{
string tableName = AWSItems.getTableName(date, area, aType, dataField);
string seleceField = dataField.ToString();
int sliderRainMinut = 0;
if (dataField == OBTField.R06M)
{
sliderRainMinut = 6;
}
else if (dataField == OBTField.R12M)
{
sliderRainMinut = 12;
}
else if (dataField == OBTField.R30M)
{
sliderRainMinut = 30;
}
else if (dataField == OBTField.R01H)
{
sliderRainMinut = 60;
}
else if (dataField == OBTField.R02H)
{
sliderRainMinut = 120;
}
else if (dataField == OBTField.R03H)
{
sliderRainMinut = 180;
}
else if (dataField == OBTField.R06H)
{
sliderRainMinut = 360;
}
else if (dataField == OBTField.R12H)
{
sliderRainMinut = 60 * 12;
}
else if (dataField == OBTField.R24H)
{
sliderRainMinut = 60 * 24;
}
else if (dataField == OBTField.R48H)
{
sliderRainMinut = 60 * 48;
}
else if (dataField == OBTField.R72H)
{
sliderRainMinut = 60 * 72;
}
if (sliderRainMinut > 0)
{
StringBuilder sb = new StringBuilder();
int noDataCounts = 0;
sb.AppendFormat("select DDATETIME,OBTID,{0} from {1} where DDATETIME>:ddate0 and {2}>0 and {2}<99999 and DDATETIME<:ddate1 and OBTID in(", seleceField, tableName, seleceField);
foreach (var code in AWSCode.OBTCodeList)
{
if (!target.Exists(t => t.ID == code.Key))
{
if (noDataCounts > 200)
{
break;
}
if (noDataCounts++ > 0)
{
sb.Append(",");
}
sb.Append("'");
sb.Append(code.Key);
sb.Append("'");
}
}
sb.Append(") order by DDATETIME desc");
if (noDataCounts > 0)
{
using (IDataReader reader = OracleHelp.ExecuteReader(sb.ToString(), T_LOCALOBTMIND.Tunnel.connString, new OracleParameter(":ddate0", date.AddMinutes(-sliderRainMinut)), new OracleParameter(":ddate1", date)))
{
string obtid;
int maxRead = 5000;
while (reader.Read())
{
if (--maxRead == 0)
{
break;
}
obtid = reader[1].ToString();
if (!target.Exists(t => t.ID == obtid))
{
double value = (double)(decimal)reader[2];
target.Add(new AWSFieldValue()
{
ID = obtid, V0 = value, TM = (DateTime)reader[0]
});
if (--noDataCounts == 0)
{
break;
}
}
}
}
}
}
}
/// <summary>
/// 导入物料基础数据
/// </summary>
/// <returns>-1：NC服务器连接错误 1：本地服务器错误 0:成功</returns>
public static int InportWLFromNC()
{
string strSele = Common.GetWLSql();
if (string.IsNullOrEmpty(strSele))
{
return(-1);//无配置信息
}
AdoHelper oracleHelp = new OracleHelp();
DataSet ds = null;
try
{
ds = oracleHelp.ExecuteDataset(Common.NCDATASTRING, CommandType.Text, strSele);
}
catch
{
return(-1);//NC数据库联接失败
}
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
AdoHelper sqlHelp = new SqlHelp();
StringBuilder strDel = new StringBuilder();
strDel.Append("delete from wms_bms_wl_sx");
SqlConnection con = new SqlConnection(Common.GetConnectString());
con.Open();
SqlTransaction tra = con.BeginTransaction();
try
{
sqlHelp.ExecuteNonQuery(tra, CommandType.Text, strDel.ToString());
foreach (DataRow row in ds.Tables[0].Rows)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("INSERT INTO WMS_BMS_WL_SX (wlh,sx) VALUES (@WLH,@SX)");
SqlParameter[] parameters =
{
new SqlParameter("@WLH", SqlDbType.VarChar),
new SqlParameter("@SX", SqlDbType.VarChar)
};
parameters[0].Value = row["invcode"].ToString();
parameters[1].Value = row["docname"].ToString();
sqlHelp.ExecuteNonQuery(tra, CommandType.Text, strSql.ToString(), parameters);
}
tra.Commit();
con.Close();
}
catch
{
tra.Rollback();
if (con.State == ConnectionState.Open)
{
con.Close();
}
return(1);
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
return(0);
}
private DataTable dt; //表以及对应的描述信息
//Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.22) (PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=pcuenca)));Persist Security Info=True;User Id=cawy_cas; Password=ECU911_db
public OracleDAL(string conn)
{
//conn = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=issdb.amb.ecu911.gob) (PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=r4th)));Persist Security Info=True;User Id=cawy_cas; Password=ECU911_db";
//conn = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=issdb.santd.ecu911.gob) (PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=pmorona)));Persist Security Info=True;User Id=cawy_cas; Password=ECU911_db";
//conn = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=issdb.esme.ECU911.GOB) (PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=pesmeral)));Persist Security Info=True;User Id=cawy_cas; Password=ECU911_db";
help = new OracleHelp(conn);
//var strSql = "select ROWNUM as \"序号\" ,ut.table_name \"表名\",utc.comments \"表说明\" from user_tables ut left join user_tab_comments utc on ut.table_name = utc.table_name order by ut.table_name";
//dt = help.ExecuteSql(strSql);
//strSql = " select row_number()over( partition by utc.table_name order by utc.COLUMN_ID, ROWNUM ) as \"序号\"," +
// " utc.table_name as \"表名\"," +
// " utc.column_name as \"列名\", " +
// " utc.data_type as \"数据类型\", " +
// " utc.data_length as \"长度\", " +
// " utc.data_precision as \"精度\"," +
// " utc.data_Scale \"小数位数\", " +
// " case when exists ( select col.column_name from user_constraints con,user_cons_columns col " +
// " where con.constraint_name=col.constraint_name and con.constraint_type='P' and col.table_name=ucc.table_name and col.column_name = utc.column_name ) " +
// " then '√' else '' end as \"主键\", " +
// " case when utc.nullable = 'Y' then '√' else '' end as \"允许空\", " +
// " utc.data_default as \"默认值\", " +
// " ucc.comments as \"列说明\" " +
// " from " +
// " user_tab_columns utc,user_col_comments ucc " +
// " where utc.table_name = ucc.table_name and utc.column_name = ucc.column_name " +
// " order by utc.table_name,\"序号\" ";
var strSql = "select ROWNUM as \"序号\" ,ut.table_name \"表名\",utc.comments \"表说明\" from user_tables ut left join user_tab_comments utc on ut.table_name = utc.table_name order by ut.table_name";
//var strSql = "select ROWNUM ,ut.table_name ,utc.comments from user_tables ut left join user_tab_comments utc on ut.table_name = utc.table_name order by ut.table_name";
dt = help.ExecuteSql(strSql);
strSql = " select row_number()over( partition by utc.table_name order by utc.COLUMN_ID, ROWNUM ) as \"序号\"," +
" utc.table_name as \"表名\"," +
" utc.column_name as \"列名\", " +
" utc.data_type as \"数据类型\", " +
" utc.data_length as \"长度\", " +
" utc.data_precision as \"精度\"," +
" utc.data_Scale \"小数位数\", " +
" case when exists ( select col.column_name from user_constraints con,user_cons_columns col " +
" where con.constraint_name=col.constraint_name and con.constraint_type='P' and col.table_name=ucc.table_name and col.column_name = utc.column_name ) " +
" then '√' else '' end as \"主键\", " +
" case when utc.nullable = 'Y' then '√' else '' end as \"允许空\", " +
" utc.data_default as \"默认值\", " +
" ucc.comments as \"列说明\" " +
" from " +
" user_tab_columns utc,user_col_comments ucc " +
" where utc.table_name = ucc.table_name and utc.column_name = ucc.column_name " +
" order by utc.table_name,\"序号\" ";
// strSql = @" select row_number()over( partition by utc.table_name order by utc.COLUMN_ID, ROWNUM ) as xh,
// utc.table_name as bm,
// utc.column_name as lm,
// utc.data_type as sjlx,
// utc.data_length as cl,
// utc.data_precision as jl,
// utc.data_Scale xsws,
// case when exists ( select col.column_name from user_constraints con,user_cons_columns col
// where con.constraint_name=col.constraint_name and con.constraint_type='P' and col.table_name=ucc.table_name and col.column_name = utc.column_name )
// then 'Y' else '' end as zj,
// case when utc.nullable = 'Y' then 'Y' else '' end as yxk,
// utc.data_default as mrzh,
// ucc.comments as lshm
// from
// user_tab_columns utc,user_col_comments ucc
// where utc.table_name = ucc.table_name and utc.column_name = ucc.column_name
// order by utc.table_name,xh";
dtStruct = help.ExecuteSql(strSql);
}
/// <summary>
/// 导入客户数据
/// </summary>
/// <returns>-1：NC服务器连接错误 1：本地服务器错误 0:成功</returns>
public static int InportKHFromNC()
{
string strSelectNC = "select pk_cubasdoc,custcode,custname, saleaddr from bd_cubasdoc";
AdoHelper oracleHelp = new OracleHelp();
DataSet ds = null;
try
{
ds = oracleHelp.ExecuteDataset(Common.NCDATASTRING, CommandType.Text, strSelectNC);
}
catch
{
return(-1);//NC数据库联接失败
}
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
AdoHelper sqlHelp = new SqlHelp();
StringBuilder strDel = new StringBuilder();
strDel.Append("DELETE FROM WMS_Pub_Customer");
SqlConnection con = new SqlConnection(Common.GetConnectString());
con.Open();
SqlTransaction tra = con.BeginTransaction();
try
{
sqlHelp.ExecuteNonQuery(tra, CommandType.Text, strDel.ToString());
foreach (DataRow row in ds.Tables[0].Rows)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into WMS_Pub_Customer(");
strSql.Append("KHID,KHName,KHAdress,KHLB)");
strSql.Append(" values (");
strSql.Append("@KHID,@KHName,@KHAdress,@KHLB)");
SqlParameter[] parameters =
{
new SqlParameter("@KHID", SqlDbType.VarChar),
new SqlParameter("@KHName", SqlDbType.VarChar),
new SqlParameter("@KHAdress", SqlDbType.VarChar),
new SqlParameter("@KHLB", SqlDbType.VarChar)
};
parameters[0].Value = row["custcode"].ToString();
parameters[1].Value = row["custname"].ToString();
parameters[2].Value = row["saleaddr"].ToString();
parameters[3].Value = "1";
sqlHelp.ExecuteNonQuery(tra, CommandType.Text, strSql.ToString(), parameters);
}
tra.Commit();
con.Close();
}
catch
{
tra.Rollback();
if (con.State == ConnectionState.Open)
{
con.Close();
}
return(1);
}
finally
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
}
return(0);
}