用于项目的SQL写法

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Inspur.DataExchange.Logging;
using Inspur.Finix.ExceptionManagement;
using System.Configuration;
using LangChao.ECGAP.Common;
using WenZhouZJ.Exchange;
using LangChao.ECGAP.DAL;
using LangChao.ECGAP.DAL.SQL;
using WenZhouZJ.Exchange.Entities;

namespace WenZhouZJ.Exchange.FromNW
{
    /// <summary>
    /// 审批数据往综合监察前置库里交换数据类
    /// </summary>
    public class DataExchange
    {

        /// <summary>
        /// 执行交换
        /// </summary>
        /// <returns></returns>
        public static bool DoExchange()
        {           
            try
            {
                Logger.LogEntry("", "---------- 开始获取南威系统中的收件信息 --------------");

                Exchange();


                Logger.LogEntry("", "----------- 获取南威系统中的收件信息完毕！-----------------");
            }
            catch (Exception ex)
            {
                ExceptionManager.Handle(ex);
                Logger.LogEntry("", "----------- 收件信息获取错误:"+ex );
            }
            return true;
        }

        /// <summary>
        /// 交换处理
        /// </summary>
        private static void Exchange()
        {
            string username = ConfigurationSettings.AppSettings["username"];
            string validate = ConfigurationSettings.AppSettings["validate"];
            string strsql = "select * from NW_LastGainTime";
            DataSet ds = cSqlHelper.ExecuteDataset(cConnectionString.CONN_STRING, CommandType.Text, strsql);
            bool flag = ds.Tables[0].Rows.Count == 0 ? true : false;
            string xml = string.Empty;
            xml = NwWs.GetInfoByTimeline(username, validate, flag ? "1900-01-01 00:00:00" : ds.Tables[0].Rows[0][0].ToString(), DateTime.Now.ToString());
            #region 更新数据库中最后获取的时间
            cTable selectTable = new cTable();
            selectTable.Name = "NW_LastGainTime";
            DataSet dsTime = cSqlDelegater.SelectDataSet(selectTable, cConnectionString.CONN_STRING);
            selectTable = new cTable();
            selectTable.Columns.Add("LastGainTime", DateTime.Now);
            selectTable.Name = "NW_LastGainTime";
            if (!flag)
            {                
                cSqlDelegater.Update(selectTable, cConnectionString.CONN_STRING);
            }
            else
            {
                cSqlDelegater.Insert(selectTable, cConnectionString.CONN_STRING);
            }

            #endregion
            
            #region 将获取的办件插入数据库中
            ItemInfo info = (ItemInfo)Helper.Deserialize(typeof(ItemInfo), xml);
            List<Apas_Info> apasInfos = info.Apas_Infos;
            foreach (var apasInfo in apasInfos)
            {
                DataTable tempDt = GetDate(apasInfo).Tables[0];
                selectTable = new cTable();                
                selectTable.Columns.Add("Unid", apasInfo.Unid);
                selectTable.Columns.Add("Subject", "关于"+apasInfo.Applyname+"的申请");
                selectTable.Columns.Add("SERVICEID", apasInfo.Serviceid);
                selectTable.Columns.Add("SERVICENAME", apasInfo.Servicename);
                selectTable.Columns.Add("APPLYNAME", apasInfo.Applyname);
                selectTable.Columns.Add("MOBILE", apasInfo.Mobile);
                selectTable.Columns.Add("PHONE", apasInfo.Phone);
                selectTable.Columns.Add("MOBILE", apasInfo.Mobile);
                selectTable.Columns.Add("ADDRESS", apasInfo.Address);
                selectTable.Columns.Add("POSTCODE", apasInfo.Postcode);
                selectTable.Columns.Add("EMAIL", apasInfo.Email);
                selectTable.Columns.Add("CONTACTMAN", apasInfo.Contactman);
                selectTable.Columns.Add("LEGALMAN", apasInfo.Legalman);
                selectTable.Columns.Add("APPLYFROM", apasInfo.Applyfrom);
                selectTable.Columns.Add("CREATE_TIME", apasInfo.Create_Time);
                selectTable.Columns.Add("RECEIVE_TIME", apasInfo.Receive_Time);
                selectTable.Columns.Add("PREJUDGE_TIME", apasInfo.Prejudge_Time);
                selectTable.Columns.Add("ACCEPT_TIME", apasInfo.Accept_Time);
                selectTable.Columns.Add("GREEN_WAY", apasInfo.Green_Way);
                selectTable.Columns.Add("APPLYCOUNT", apasInfo.Applycount);
                selectTable.Columns.Add("IsAccept", "0");
                selectTable.Columns.Add("ActivityModelGuid", tempDt.Rows[0]["ActivityModelGuid"]);
                selectTable.Columns.Add("ActivitySchemeGuid", tempDt.Rows[0]["ActivitySchemeGuid"]);
                selectTable.Columns.Add("FormID", tempDt.Rows[0]["TaskFormID"]);
                selectTable.Columns.Add("AppPieceId", tempDt.Rows[0]["AppPieceId"]);
                selectTable.Columns.Add("MEMO", apasInfo.Memo);
                selectTable.Name = "NW_Apas_Info";
                cSqlDelegater.Insert(selectTable, cConnectionString.CONN_STRING);
            }
            #endregion
            
        }

        /// <summary>
        /// 获取流程、方案、表单ID数据
        /// </summary>
        /// <param name="apasInfo"></param>
        /// <returns></returns>
        private static DataSet GetDate(Apas_Info apasInfo)
        {
            string strSql = string.Empty;
            strSql = string.Format(@"SELECT AppPieceId
                                    FROM    dbo.NW_ServiceRelationship
                                    WHERE   Unid = {0}", apasInfo.Unid);
            DataSet pieceDs = cSqlHelper.ExecuteDataset(cConnectionString.CONN_STRING, CommandType.Text, strSql);
            strSql = string.Format(@"SELECT  DISTINCT ApproveItemCode , 
                                                    AppPieceId,
                                                    ActivityModelGuid ,
                                                    ActivitySchemeGuid ,
                                                    ActivityModelStepId ,
                                                    TaskFormID
                                            FROM    viewschemevisitor
                                            WHERE   AppPieceId='{0}'", pieceDs.Tables[0].Rows[0][0].ToString());
            DataSet ds = cSqlHelper.ExecuteDataset(cConnectionString.CONN_STRING2, CommandType.Text, strSql);
            return ds;
        }





    }
}

 

审批中的写法：

 

 

 

 

综合管理中的写法：

查询：

 SelectSQL select = new SelectSQL();
            select.DataBaseAlias = "newecgap";
            string strSql = string.Empty;
            strSql = string.Format(@"SELECT AppPieceId
                                    FROM    dbo.NW_ServiceRelationship
                                    WHERE   Unid = '{0}'", apasInfo.Serviceid);
            select.CommandText = strSql;

            DataSet pieceDs = select.ExecuteDataSet();

 

 

修改：

 UpdateSQL update = new UpdateSQL();
                update.DataBaseAlias = "newecgap";
                update.UpdateTable = "NW_LastGainTime";
                update.AddFieldValue("LastGainTime", DateTime.Now);
                update.ExecuteNonQuery();

 

插入：

InsertSQL insertSql = new InsertSQL("NW_LastGainTime");
                insertSql.DataBaseAlias = "newecgap";
                insertSql.AddFieldValue("LastGainTime",DateTime.Now);
                insertSql.ExecuteNonQuery();