关闭

excel宏调用webservice使用存储过程同步excel数据的方法

标签: excel宏 webservice 存储BI数据仓库
1227人阅读 评论(0) 收藏 举报
分类:

excel宏:

随后更新


webservice:

1.创建空应用程序

2.添加web服务

3.创建数据库访问类库DataHelper


sqlserver:

创建数据同步的存储过程



以下是一些需要的代码,比较杂乱,有空再整理整理。

DataFactory.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;

namespace DataHelper
{
    public class DataFactory
    {
        /// <summary>
        /// 获取数据类型1为sqlserver,2为access
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        public static DbHelper GetHelper()
        {
            string ConnStr = ConfigurationManager.AppSettings["ConnStr"].ToString();
            int Dbtype = 1;
            Int32.TryParse(ConfigurationManager.AppSettings["DbType"].ToString(), out Dbtype);
            switch (Dbtype)
            {
                case 1:
                    return new SqlHelper(ConnStr);
                case 2:
                    return new OledbHelper(ConnStr);
                default:
                    return new SqlHelper(ConnStr);
            }
        }

        public static DbHelper GetHelper1()
        {
            string ConnStr = ConfigurationManager.AppSettings["ConnStr1"].ToString();
            int Dbtype = 1;

            Int32.TryParse(ConfigurationManager.AppSettings["DbType"].ToString(), out Dbtype);
            switch (Dbtype)
            {
                case 1:
                    return new SqlHelper(ConnStr);
                case 2:
                    return new OledbHelper(ConnStr);
                default:
                    return new SqlHelper(ConnStr);
            }
        }



        public static DbHelper GetYellowPageHelper()
        {
            string ConnStr = ConfigurationManager.AppSettings["ConnStrYP"].ToString();
            int Dbtype = 1;

            Int32.TryParse(ConfigurationManager.AppSettings["DbType"].ToString(), out Dbtype);
            switch (Dbtype)
            {
                case 1:
                    return new SqlHelper(ConnStr);
                case 2:
                    return new OledbHelper(ConnStr);
                default:
                    return new SqlHelper(ConnStr);
            }
        }


        /// <summary>
        /// Discuz数据库
        /// </summary>
        /// <returns></returns>
        public static DbHelper GetYellowPageHelperDNT()
        {
            string ConnStr = ConfigurationManager.AppSettings["ConnStrDNT"].ToString();
            int Dbtype = 1;

            Int32.TryParse(ConfigurationManager.AppSettings["DbType"].ToString(), out Dbtype);
            switch (Dbtype)
            {
                case 1:
                    return new SqlHelper(ConnStr);
                case 2:
                    return new OledbHelper(ConnStr);
                default:
                    return new SqlHelper(ConnStr);
            }
        }
    }
}


DbHelper.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Collections.Specialized;

namespace DataHelper
{
    public abstract class DbHelper
    {
        /// <summary>
        /// 得到数据库链接
        /// </summary>
        public abstract IDbConnection Connection { get; }
        /// <summary>
        /// 打开数据库连接;  
        /// </summary>
        public abstract void Open();
        /// <summary>
        /// 关闭数据库链接;
        /// </summary>
        public abstract void Close();
        /// <summary>
        /// 开始一个事务;
        /// </summary>
        public abstract void BeginTrans();
        /// <summary>
        /// 提交一个事务;
        /// </summary>
        public abstract void CommitTrans();
        /// <summary>
        /// 回滚一个事务;
        /// </summary>
        public abstract void RollBackTrans();
        /// <summary>
        /// 执行sql语句,返回受影响集合数
        /// </summary>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdText">命令字符串</param>
        /// <param name="commandParameters">命令参数</param>
        /// <returns></returns>
        public abstract int ExecuteNonQuery(CommandType cmdType, string cmdText, NameValueCollection pars);
        /// <summary>
        /// 执行sql语句,返回IDataReader
        /// </summary>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdText">命令字符串</param>
        /// <param name="commandParameters">命令参数</param>
        /// <returns></returns>
        public abstract DbDataReader ExecuteReader(CommandType cmdType, string cmdText, NameValueCollection pars);
        /// <summary>
        /// 执行sql语句,返回结构的第一行,第一列的值
        /// </summary>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdText">命令字符串</param>
        /// <param name="commandParameters">命令参数</param>
        /// <returns></returns>
        public abstract object ExecuteScalar(CommandType cmdType, string cmdText, NameValueCollection pars);
        /// <summary>
        /// 执行sql语句,获得datatable
        /// </summary>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdText">命令字符串</param>
        /// <param name="commandParameters">命令参数</param>
        /// <returns></returns>
        public abstract DataTable GetDataTable(CommandType cmdType, string cmdText, NameValueCollection pars);
    }
}

OledbHelper.cs

using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;

namespace DataHelper
{
    class OledbHelper : DbHelper
    {
        private OleDbConnection conn;
        private OleDbTransaction trans;

        private bool inTransaction = false; //指示当前是否正处于事务中

        /// <summary>
        /// 构造函数,初始OledbConnection对象
        /// </summary>
        /// <param name="StrConnection"></param>
        public OledbHelper(string StrConnection)
        {
            this.conn = new OleDbConnection(StrConnection);
        }

        /// <summary>
        /// 获取Conneciton
        /// </summary>
        public override IDbConnection Connection
        {
            get { return this.conn; }
        }

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public override void Open()
        {
            if (conn.State != ConnectionState.Open)
            {
                this.conn.Open();
            }
        }


        /// <summary>
        /// 关闭数据库连接,释放资源
        /// </summary>
        public override void Close()
        {
            if (this.trans != null)
            {
                this.trans.Dispose();
            }

            if (conn.State != ConnectionState.Closed)
            {
                conn.Close();
            }


            conn.Dispose();
        }

        public override void BeginTrans()
        {
            trans = conn.BeginTransaction();
            inTransaction = true;
        }
        public override void CommitTrans()
        {
            trans.Commit();
            inTransaction = false;
        }

        public override void RollBackTrans()
        {
            trans.Rollback();
            inTransaction = false;
        }


        /// <summary>
        /// 参数准备
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        public void PrepareCommand(OleDbCommand cmd, CommandType cmdType, string cmdText, NameValueCollection pars)
        {
            if (this.trans != null)
            {
                cmd.Transaction = this.trans;
            }
            cmd.Connection = conn;
            cmd.CommandType = cmdType;
            cmd.CommandText = cmdText;

            if (pars != null && pars.Count > 0)
            {
                string[] keys = pars.AllKeys;
                for (int i = 0; i < pars.Count; i++)
                {
                    cmd.Parameters.AddWithValue(keys[i], pars[i]);
                }
            }

        }



        public override int ExecuteNonQuery(CommandType cmdType, string cmdText, NameValueCollection pars)
        {

            using (OleDbCommand cmd = new OleDbCommand())
            {
                this.PrepareCommand(cmd, cmdType, cmdText, pars);

                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }

        }

        public override DbDataReader ExecuteReader(CommandType cmdType, string cmdText, NameValueCollection pars)
        {
            using (OleDbCommand cmd = new OleDbCommand())
            {
                this.PrepareCommand(cmd, cmdType, cmdText, pars);
                DbDataReader dr = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                return dr;
            }

        }

        public override object ExecuteScalar(CommandType cmdType, string cmdText, NameValueCollection pars)
        {

            using (OleDbCommand cmd = new OleDbCommand())
            {
                this.PrepareCommand(cmd, cmdType, cmdText, pars);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

        public override DataTable GetDataTable(CommandType cmdType, string cmdText, NameValueCollection pars)
        {
            using (OleDbCommand cmd = new OleDbCommand())
            {
                DataTable dt = new DataTable();
                this.PrepareCommand(cmd, cmdType, cmdText, pars);

                using (DbDataAdapter da = new OleDbDataAdapter())
                {
                    da.SelectCommand = cmd;
                    da.Fill(dt);
                    cmd.Parameters.Clear();
                    return dt;
                }

            }

        }

    }
}

SqlHelper.cs

using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace DataHelper
{
    class SqlHelper : DbHelper
    {
        private SqlConnection conn;
        private SqlTransaction trans;
        private bool inTransaction = false; //指示当前是否正处于事务中

        /// <summary>
        /// 获取IDbConnection
        /// </summary>
        public override IDbConnection Connection
        {
            get { return this.conn; }
        }

        /// <summary>
        /// 构造函数,初始SqlConnection对象
        /// </summary>
        /// <param name="StrConnection"></param>
        public SqlHelper(string StrConnection)
        {
            this.conn = new SqlConnection(StrConnection);
        }

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public override void Open()
        {
            if (conn.State != ConnectionState.Open)
            {
                this.conn.Open();
            }
        }

        /// <summary>
        /// 关闭数据库连接,释放资源
        /// </summary>
        public override void Close()
        {
            if (this.trans != null)
            {
                this.trans.Dispose();
            }

            if (conn.State != ConnectionState.Closed)
            {
                conn.Close();
            }


            conn.Dispose();
        }

        /// <summary>
        /// 开始事务
        /// </summary>
        public override void BeginTrans()
        {
            trans = conn.BeginTransaction();
            inTransaction = true;
        }
        /// <summary>
        /// 提交事务
        /// </summary>
        public override void CommitTrans()
        {
            trans.Commit();
            inTransaction = false;
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        public override void RollBackTrans()
        {
            trans.Rollback();
            inTransaction = false;
        }


        /// <summary>
        /// 参数准备
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        public void PrepareCommand(SqlCommand cmd, CommandType cmdType, string cmdText, NameValueCollection pars)
        {
            if (this.trans != null)
            {
                cmd.Transaction = this.trans;
            }
            cmd.Connection = conn;
            cmd.CommandType = cmdType;
            cmd.CommandText = cmdText;

            if (pars != null && pars.Count > 0)
            {
                string[] keys = pars.AllKeys;
                for (int i = 0; i < pars.Count; i++)
                {
                    cmd.Parameters.AddWithValue(keys[i], pars[i]);
                }
            }
        }

        /// <summary>
        /// 执行sql命令,返回受影响行数
        /// </summary>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdText">命令</param>
        /// <param name="pars">参数组</param>
        /// <returns>受影响行数</returns>
        public override int ExecuteNonQuery(CommandType cmdType, string cmdText, NameValueCollection pars)
        {

            using (SqlCommand cmd = new SqlCommand())
            {
                this.PrepareCommand(cmd, cmdType, cmdText, pars);

                int val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }
        /// <summary>
        /// 执行sql命令,返回DbDataReader
        /// </summary>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdText">命令</param>
        /// <param name="pars">参数组</param>
        /// <returns>DbDataReader</returns>
        public override DbDataReader ExecuteReader(CommandType cmdType, string cmdText, NameValueCollection pars)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                this.PrepareCommand(cmd, cmdType, cmdText, pars);
                DbDataReader dr = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                return dr;
            }
        }

        /// <summary>
        /// 执行sql语句,返回第一行第一列
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public override object ExecuteScalar(CommandType cmdType, string cmdText, NameValueCollection pars)
        {

            using (SqlCommand cmd = new SqlCommand())
            {
                this.PrepareCommand(cmd, cmdType, cmdText, pars);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        /// 执行sql语句,返回DataTable
        /// </summary>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdText">命令</param>
        /// <param name="pars">参数组</param>
        /// <returns></returns>
        public override DataTable GetDataTable(CommandType cmdType, string cmdText, NameValueCollection pars)
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                DataTable dt = new DataTable();
                this.PrepareCommand(cmd, cmdType, cmdText, pars);

                using (DbDataAdapter da = new SqlDataAdapter())
                {
                    da.SelectCommand = cmd;
                    da.Fill(dt);
                    cmd.Parameters.Clear();
                    return dt;
                }

            }

        }

    }
}

WebService.asmx

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data.Common;
using System.Data;
using System.Collections.Specialized;
using DataHelper;

namespace myWebService
{
    /// <summary>
    /// WebService 的摘要说明
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // 若要允许使用 ASP.NET AJAX 从脚本中调用此 Web 服务,请取消对下行的注释。
    // [System.Web.Script.Services.ScriptService]
    public class WebService : System.Web.Services.WebService
    {
        [WebMethod]
        public string HelloWorld()
        {
            string uid = GetGuid();
            string test = "n7,5,20150701,10,20150701|n8,5,20150701,10,20150701|n9,5,20150701,9,20150701";
            string[] rows=test.Split('|');           
            int rowsCount=rows.Length;
            int colsCount=rows[0].Length;
            string cmd = string.Empty;
            string floorno=string.Empty;
            NameValueCollection nvc=new NameValueCollection();          
            string result = string.Empty;
            DataHelper.DbHelper dh = DataHelper.DataFactory.GetHelper();
            dh.Open();
            dh.BeginTrans();
            try
            {
                List<String> sql = new List<string>();
                for (int i = 0; i < rowsCount; i++)
                {
                    sql.Add("insert into TARGETHISTORY(EQNO,FLOORNO,RUNSTART,RUNTIME,UPDATETIME,UID) values(@EQNO,@FLOORNO,@RUNSTART,@RUNTIME,@UPDATETIME,@UID)");
                    nvc = new NameValueCollection();
                    nvc.Add("@EQNO", rows[i].Split(',')[0]);
                    nvc.Add("@FLOORNO", rows[i].Split(',')[1]);
                    nvc.Add("@RUNSTART", rows[i].Split(',')[2]);
                    nvc.Add("@RUNTIME", rows[i].Split(',')[3]);
                    nvc.Add("@UPDATETIME", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:fff"));
                    nvc.Add("@UID", uid);
                    floorno = rows[i].Split(',')[1];
                    dh.ExecuteNonQuery(CommandType.Text, sql[i], nvc);
                }
                nvc = new NameValueCollection();
                nvc.Add("@UID", uid);
                nvc.Add("@FLOORNO", floorno);
                dh.ExecuteNonQuery(CommandType.StoredProcedure, "DeviceShareLoad", nvc);
                dh.CommitTrans();
                result= "数据导入成功!";
            }
            catch (Exception ex)
            {
                dh.RollBackTrans();   
                dh.Close();
                result = "数据导入失败!请联系 IT! 错误原因:"+ex.ToString();                
            }
            finally
            {
                dh.Close();               
            }
            return result;
        }
        private static string GetGuid()
        {
            System.Guid guid = new Guid();
            guid = Guid.NewGuid();
            return guid.ToString();
        }
    }
}


webconfig

<?xml version="1.0" encoding="utf-8"?>

<!--
  有关如何配置 ASP.NET 应用程序的详细消息,请访问
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->

<configuration>
    <system.web>
        <compilation debug="true" targetFramework="4.0" />
    </system.web>
  <appSettings>
    <!-- 1为sqlserver 2为 access -->
    <add key="DbType" value="1"/>
    <!-- sql server连接字符串 -->
    <add key="ConnStr" value="server=.;uid=sa;pwd=891219;database=test;"/>
    <!-- access连接字符串  -->
    <!--<add key="ConnStr" value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|#data#.mdb"/>-->
  </appSettings>
</configuration>


存储过程

USE [test]
GO
/****** Object:  StoredProcedure [dbo].[DeviceShareLoad]    Script Date: 07/10/2015 02:49:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[DeviceShareLoad]
@floorno nvarchar(50),
@uid nvarchar(50)
AS
BEGIN
    merge into targettable as t
    using (select * from TARGETHISTORY where uid=@uid) as s
    on t.floorno=@floorno and s.floorno=@floorno and t.eqno=s.eqno
    when matched
    then update set t.eqno=s.eqno,t.floorno=s.floorno,t.runstart=s.runstart,t.runtime=s.runtime,t.updatetime=getdate()
    when not matched and s.floorno=@floorno
    then insert (eqno,floorno,runstart,runtime,updatetime) values(s.eqno,s.floorno,s.runstart,s.runtime,getdate())
    when not matched by source and t.floorno=@floorno
    then delete;
    SET NOCOUNT ON;
END








0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:10537次
    • 积分:212
    • 等级:
    • 排名:千里之外
    • 原创:9篇
    • 转载:3篇
    • 译文:0篇
    • 评论:3条
    文章分类
    文章存档