📄 interface.asmx.cs
字号:
m_DbDelCmd.Connection=m_DbConn;
string DelSql="";
for(int i=0;i<DtDbf.Rows.Count;i++)
{
if(i>99)
{
if(i.ToString().Substring(i.ToString().Length-2,2)=="00")
{
if(DelSql!="")
{
DelSql=DelSql.Substring(0,DelSql.Length-3);
m_DbDelCmd.CommandText="delete from "+TableName+" where "+DelSql;
m_DbDelCmd.CommandType=CommandType.Text;
m_DbDelCmd.ExecuteNonQuery();
DelSql="";
m_DbDelCmd.Parameters.Clear();
}
m_DbCmd.CommandText=AllSql;
m_DbCmd.CommandType=CommandType.Text;
m_DbCmd.ExecuteNonQuery();
AllSql="";
m_DbCmd.Parameters.Clear();
}
}
if(sKeyField!="")
{
string sWhere="";
for(int j=0;j<sKey.Length;j++)
{
sWhere+=sKey[j]+"=? and ";
m_DbDelCmd.Parameters.Add(sKey[j]+"Del"+i+j,DtDbf.Rows[i][sKey[j]]);
}
sWhere=sWhere.Substring(0,sWhere.Length-4);
DelSql+="("+sWhere+") or ";
//
// StrSql="delete from "+TableName+" where "+sWhere;
// AllSql+=StrSql+";";
}
string FieldSql="";
string ValueSql="";
for(int j=0;j<DtDbf.Columns.Count;j++)
{
FieldSql+=DtDbf.Columns[j].ColumnName+",";
ValueSql+="?,";
m_DbCmd.Parameters.Add(DtDbf.Columns[j].ColumnName+i+j,DtDbf.Rows[i][j]);
}
FieldSql=FieldSql.Substring(0,FieldSql.Length-1);
ValueSql=ValueSql.Substring(0,ValueSql.Length-1);
StrSql="Insert into "+TableName+"("+FieldSql+")";
StrSql+=" Values("+ValueSql+")";
AllSql+=StrSql+";";
}
if(DelSql!="")
{
DelSql=DelSql.Substring(0,DelSql.Length-3);
m_DbDelCmd.CommandText="delete from "+TableName+" where "+DelSql;
m_DbDelCmd.CommandType=CommandType.Text;
m_DbDelCmd.ExecuteNonQuery();
DelSql="";
m_DbDelCmd.Parameters.Clear();
}
m_DbCmd.CommandText=AllSql;
m_DbCmd.CommandType=CommandType.Text;
m_DbCmd.ExecuteNonQuery();
}
catch(System.Exception ex)
{
string sErr=ex.Message;
sErr=sErr;
}
}
// m_DbTrans.Commit();
}
catch(Exception e)
{
// m_DbTrans.Rollback();
string str=e.Message.ToString();
}
m_DbConn.Close();
}
private void AppendFields(string TableName,OleDbCommand OleCmd,Hashtable hsValue)
{
string FieldSql="";
string ValueSql="";
OleCmd.Parameters.Clear();
foreach(DictionaryEntry entry in hsValue)
{
FieldSql+=entry.Key.ToString()+",";
ValueSql+="?,";
OleCmd.Parameters.Add(entry.Key.ToString(),entry.Value);
}
FieldSql=FieldSql.Substring(0,FieldSql.Length-1);
ValueSql=ValueSql.Substring(0,ValueSql.Length-1);
string StrSql="";
StrSql="Insert into "+TableName+"("+FieldSql+")";
StrSql+=" Values("+ValueSql+")";
OleCmd.CommandText=StrSql;
OleCmd.CommandType=CommandType.Text;
OleCmd.ExecuteNonQuery();
}
private void UpdateExcelTable(string TransName,string LocalConnection)
{
string ExcelName=TransName;
DateTime DqDt=DateTime.Now;
if(TransName.ToUpper().Substring(0,4)=="JGGX"||TransName.ToUpper().Substring(0,4)=="JGXX")
{
TransName=TransName.Substring(0,7);
if(TransName.ToUpper().Substring(0,4)=="JGXX")
DqDt=DateTime.Parse(ExcelName.Substring(13,4)+"-"+ExcelName.Substring(17,2)+"-"+ExcelName.Substring(19,2));
else
DqDt=DateTime.Parse(ExcelName.Substring(8,4)+"-"+ExcelName.Substring(12,2)+"-"+ExcelName.Substring(14,2));
}
LocalConnection=LocalConnection.Replace("(file)",ExcelName);
string TableName="";
bool bNeedDelete=true;
string sKeyField="";
string MakeResultTableID="";
this.GetTransTableConfig(TransName,ref TableName,ref bNeedDelete,ref sKeyField,ref MakeResultTableID);
if(sKeyField==null)
sKeyField="";
if(TableName==null)
TableName=TransName;
string StrSql="";
OleDbConnection m_DbConn;
// OleDbTransaction m_DbTrans;
OleDbCommand m_DbCmd=new OleDbCommand();
try
{
m_DbConn=new OleDbConnection(WebConnection);
m_DbConn.Open();
}
catch(Exception e)
{
string str=e.Message.ToString();
return;
}
// m_DbTrans=m_DbConn.BeginTransaction();
m_DbCmd.CommandTimeout=0;
m_DbCmd.Connection=m_DbConn;
// m_DbCmd.Transaction=m_DbTrans;
try
{
DataTable DtDbf;
if(TransName.ToUpper().Substring(0,4)=="JGGX"||TransName.ToUpper().Substring(0,4)=="JGXX")
{
if(TransName.Substring(5,2)=="00")
{
DtDbf=new DataTable();
DtDbf.Columns.Add("DqRq");
DtDbf.Columns.Add("FileName");
DtDbf.Columns.Add("Hydm");
DataRow dr=DtDbf.NewRow();
dr["DqRq"]=DqDt;
dr["FileName"]=ExcelName;
dr["Hydm"]=ExcelName.Substring(8,4);
DtDbf.Rows.Add(dr);
}
// else if(TransName.Substring(5,2)=="24")
// {
// DtDbf=new DataTable();
// DtDbf.Columns.Add("DqRq");
// DtDbf.Columns.Add("FileName");
// DataRow dr=DtDbf.NewRow();
// dr["DqRq"]=DqDt;
// dr["FileName"]=ExcelName;
// DtDbf.Rows.Add(dr);
// }
else
{
StrSql="select * from DataTable1";
DtDbf=this.GetDataTable(StrSql,LocalConnection);
DtDbf.Columns.Add("DqRq");
DtDbf.Columns.Add("FileName");
for(int i=0;i<DtDbf.Rows.Count;i++)
{
DtDbf.Rows[i]["DqRq"]=DqDt;
DtDbf.Rows[i]["FileName"]=ExcelName;
}
}
}
else
{
StrSql="select * from " + "OpenDataSource("+LocalConnection+")...[Sheet1$]";
DtDbf=this.GetDataTable(StrSql,WebConnection);
}
if(DtDbf.Rows.Count>0)
{
if(bNeedDelete||sKeyField=="")
{
StrSql="delete from "+TableName;
m_DbCmd.CommandText=StrSql;
m_DbCmd.CommandType=CommandType.Text;
m_DbCmd.ExecuteNonQuery();
}
try
{
string[] sKey=sKeyField.Split(',');
string AllSql="";
m_DbCmd.Parameters.Clear();
OleDbCommand m_DbDelCmd=new OleDbCommand();
m_DbDelCmd.CommandTimeout=0;
m_DbDelCmd.Connection=m_DbConn;
string DelSql="";
for(int i=0;i<DtDbf.Rows.Count;i++)
{
if(i>99)
{
if(i.ToString().Substring(i.ToString().Length-2,2)=="00")
{
if(DelSql!="")
{
DelSql=DelSql.Substring(0,DelSql.Length-3);
m_DbDelCmd.CommandText="delete from "+TableName+" where "+DelSql;
m_DbDelCmd.CommandType=CommandType.Text;
m_DbDelCmd.ExecuteNonQuery();
DelSql="";
m_DbDelCmd.Parameters.Clear();
}
m_DbCmd.CommandText=AllSql;
m_DbCmd.CommandType=CommandType.Text;
m_DbCmd.ExecuteNonQuery();
AllSql="";
m_DbCmd.Parameters.Clear();
}
}
if(sKeyField!="")
{
string sWhere="";
for(int j=0;j<sKey.Length;j++)
{
sWhere+=sKey[j]+"=? and ";
m_DbDelCmd.Parameters.Add(sKey[j]+"Del"+i+j,DtDbf.Rows[i][sKey[j]]);
}
sWhere=sWhere.Substring(0,sWhere.Length-4);
DelSql+="("+sWhere+") or ";
//
// StrSql="delete from "+TableName+" where "+sWhere;
// AllSql+=StrSql+";";
}
string FieldSql="";
string ValueSql="";
for(int j=0;j<DtDbf.Columns.Count;j++)
{
FieldSql+="["+DtDbf.Columns[j].ColumnName+"],";
// if(DtDbf.Columns[j].ColumnName.IndexOf("/")!=-1||DtDbf.Columns[j].ColumnName.IndexOf("－")!=-1)
// FieldSql+="["+DtDbf.Columns[j].ColumnName+"],";
// else
// FieldSql+=DtDbf.Columns[j].ColumnName+",";
ValueSql+="?,";
m_DbCmd.Parameters.Add(DtDbf.Columns[j].ColumnName+i+j,DtDbf.Rows[i][j]);
}
FieldSql=FieldSql.Substring(0,FieldSql.Length-1);
ValueSql=ValueSql.Substring(0,ValueSql.Length-1);
StrSql="Insert into "+TableName+"("+FieldSql+")";
StrSql+=" Values("+ValueSql+")";
AllSql+=StrSql+";";
}
if(DelSql!="")
{
DelSql=DelSql.Substring(0,DelSql.Length-3);
m_DbDelCmd.CommandText="delete from "+TableName+" where "+DelSql;
m_DbDelCmd.CommandType=CommandType.Text;
m_DbDelCmd.ExecuteNonQuery();
DelSql="";
m_DbDelCmd.Parameters.Clear();
}
m_DbCmd.CommandText=AllSql;
m_DbCmd.CommandType=CommandType.Text;
m_DbCmd.ExecuteNonQuery();
}
catch(System.Exception ex)
{
string sErr=ex.Message;
sErr=sErr;
}
}
// m_DbTrans.Commit();
}
catch(Exception e)
{
// m_DbTrans.Rollback();
string str=e.Message.ToString();
}
m_DbConn.Close();
}
private void UpdateExcelTable_Old(string TransName,string LocalConnection)
{
string ExcelName=TransName;
LocalConnection=LocalConnection.Replace("(file)",ExcelName);
string TableName=ConfigurationSettings.AppSettings[TransName];
if(TableName==null)
TableName=TransName;
string StrSql="";
OleDbConnection Conn;
OleDbTransaction MyTrans;
try
{
Conn=new OleDbConnection(WebConnection);
Conn.Open();
}
catch(Exception e)
{
string str=e.Message.ToString();
return;
}
// MyTrans=Conn.BeginTransaction();
try
{
StrSql="sp_columns @table_name ='"+TableName+"'";
DataTable DtField=GetDataTable(StrSql,WebConnection);
StrSql="select * from MyRange";
StrSql="select * from " + "OpenDataSource("+LocalConnection+")...[Sheet1$]";
DataTable DtDbf=this.GetDataTable(StrSql,WebConnection);
if(DtDbf.Rows.Count>0)
{
string bNeedDelete=ConfigurationSettings.AppSettings[TableName+"_Delete"];
if(bNeedDelete==null)
{
bNeedDelete="true";
}
if(bNeedDelete=="true")
{
StrSql="delete from "+TableName;
}
else
{
try
{
string sKeyField=ConfigurationSettings.AppSettings[TableName+"_Key"];
if(sKeyField!=null)
{
StrSql="Select DISTINCT "+sKeyField+" from "+TransName;
DataTable DtKey=this.GetDataTable(StrSql,LocalConnection);
string sWhere="";
if(DtKey!=null&&DtKey.Rows.Count>0)
{
for(int i=0;i<DtKey.Rows.Count;i++)
{
sWhere+="'"+DtKey.Rows[i][sKeyField].ToString()+"',";
}
}
sWhere=sWhere.Substring(0,sWhere.Length-1);
StrSql="delete from "+TableName+" where "+sKeyField+" in ("+sWhere+")";
}
}
catch
{}
}
string ExeSql="";
CSqlBuilder cls=new CSqlBuilder();
cls.CreateSQL(TableName,this.WebConnection);
Hashtable hs=new Hashtable();
for(int i=0;i<DtDbf.Rows.Count;i++)
{
Hashtable hsEntry=new Hashtable();
for(int j=0;j<DtField.Rows.Count;j++)
{
hsEntry[DtField.Rows[j]["Column_Name"].ToString()]=DtDbf.Rows[i][DtField.Rows[j]["Column_Name"].ToString()];
}
hs.Add("Entry"+i,hsEntry);
}
if(hs.Count>0)
{
if(StrSql!="")
cls.SQLInsertMutiRow(hs,StrSql);
else
cls.SQLInsertMutiRow(hs);
}
}
// MyTrans.Commit();
}
catch(Exception e)
{
// MyTrans.Rollback();
string str=e.Message.ToString();
}
Conn.Close();
}
#region Component Designer generated code
//Required by the Web Services Designer
private IContainer components = null;
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
}
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
if(disposing && components != null)
{
components.Dispose();
}
base.Dispose(disposing);
}
#endregion
// WEB SERVICE EXAMPLE
// The HelloWorld() example service returns the string Hello World
// To build, uncomment the following lines then save and build the project
// To test this web service, press F5
// [WebMethod]
// public string HelloWorld()
// {
// return "Hello World";
// }
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -