用C#更新SQLSERVER。

需求：SQLSERVER中有一张表需要被更新。

方法有三：

一，直接在SQL中操作。

二，在C#中编写函数，之后deploy到SQL中，SQL中再去调用这个函数。

三，直接在C#中对着数据库操作。

这三种方法由快到慢，能用一，不用二；能用二，不用三。

有个项目要用到web service，第二种方法deploy老是出错，也不知道为什么，于是只好用第三种。

以下例子从简，不包括webservice部分，涵盖要点：

1. 多线程操作（定义线程组，设置线程完毕触发事件，等待所有线程完毕）

2. dataset update回DB（为表设置主键）

3. 用计数器设置timeout（加Timer）

4. connection string 来自 configuration

以下分别为：

主程序 Program

线程程序threadtranslate

更新回程序updateback

配置文件config

先在BD中建10张临时表，把要更新的数据按模10插入这10张表中。

然后开10个线程分别对这10张表进行操作：先抓入dataset，再对row做update。

最后将10张表依次更新回原表。

另设计时器，防止超时，时间到了直接终止程序。

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using System.Data.SqlClient;using System.Threading;using System.Configuration;using System.Timers;namespace updatetest{    class Program    {        static public string strConn = ConfigurationManager.ConnectionStrings["constring"].ToString();        static public SqlConnection con = new SqlConnection(strConn);        static TimeSpan waitTime = new TimeSpan(0, 1, 0);        static public AutoResetEvent[] events = new AutoResetEvent[4];        static void Main(string[] args)        {            for (int j = 0; j < 4; j++)            {                events[j] = new AutoResetEvent(false);            }            con.Open();            DateTime dtstart = System.DateTime.Now;            QueryandUpdate();            updateback.updateback.updatefunction();            DateTime dtend = System.DateTime.Now;            Console.WriteLine("all spend: {0}", dtend - dtstart);            Console.Read();        }        public static void QueryandUpdate()        {            string sqlsplit = "";            for (int j = 0; j < 4; j++)            {                sqlsplit += " if exists (select * from dbo.sysobjects where id = object_id('translationtable" + j.ToString() + "')) drop table translationtable" + j.ToString()                + " select top 100000 TimeKey,ltrim(rtrim(DayNumberOfWeek)) AS DayNumberOfWeek into translationtable" + j.ToString() + " from testupdate where TimeKey%4=" + j.ToString()                + " ALTER TABLE translationtable" + j.ToString() + " ADD CONSTRAINT [PK_translationtable" + j.ToString() + "_IK] PRIMARY KEY CLUSTERED ([TimeKey] ASC)";            }            //string sqlsplit = "exec usp_splitsurvey";            SqlCommand commandsplit = new SqlCommand(sqlsplit, con);            int num;            num = commandsplit.ExecuteNonQuery();            con.Close();            System.Timers.Timer timer = new System.Timers.Timer();            timer.Interval = 60000;            timer.Elapsed += new ElapsedEventHandler(OnTime);            timer.Enabled = true;            timer.Start();            Thread[] thread = new Thread[4];            for (int j = 0; j < 4; j++)            {                thread[j] = new Thread(run);                thread[j].Start(j);            }            WaitHandle.WaitAll(events);        }        public static void run(object i)        {            threadtranslate.threadtranslate.th((int)i);            events[(int)i].Set();        }        public static void OnTime(Object source, ElapsedEventArgs e)        {            Console.WriteLine("Timeout");            Console.Read();            System.Diagnostics.Process.GetCurrentProcess().Kill();         }    }}

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using System.Data.SqlClient;using System.Configuration;namespace threadtranslate{    public class threadtranslate    {        static public void th(int i)        {            string constr = ConfigurationManager.ConnectionStrings["constring"].ToString();            SqlConnection conn = new SqlConnection(constr);            conn.Open();            DateTime dtstart = System.DateTime.Now;            DataSet ds = new DataSet();            SqlDataAdapter da;            string sql = "select * from translationtable" + i.ToString();            da = new SqlDataAdapter(sql, conn);            SqlCommandBuilder cb1 = new SqlCommandBuilder(da);            da.Fill(ds, "translationtable");            for (int retrycount = 0; retrycount < 3; retrycount++)            {                foreach (DataRow therow1 in ds.Tables["translationtable"].Select("DayNumberOfWeek<9"))                {                    string ch = therow1["DayNumberOfWeek"].ToString();                    therow1["DayNumberOfWeek"] = "1" + ch;                }            }            da.Update(ds, "translationtable");            conn.Close();            DateTime dtend = System.DateTime.Now;            Console.WriteLine("thread" + i.ToString() + " spend: {0}", dtend - dtstart);            //Console.Read();        }    }}

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using System.Data.SqlClient;using System.Configuration;namespace updateback{    public class updateback    {        static public void updatefunction()        {            string constr = ConfigurationManager.ConnectionStrings["constring"].ToString();            SqlConnection conn = new SqlConnection(constr);            conn.Open();            string sql = "";            for (int j = 0; j < 4; j++)            {                sql += " update f set f.DayNumberOfWeek = translationtable" + j.ToString() + ".DayNumberOfWeek from testupdate f inner join translationtable" + j.ToString() + " on f.TimeKey=translationtable" + j.ToString() + ".TimeKey "                + " drop table translationtable" + j.ToString();            }            SqlCommand command = new SqlCommand(sql, conn);            int num;            num = command.ExecuteNonQuery();            conn.Close();        }    }}

<?xml version="1.0" encoding="utf-8" ?><configuration><system.serviceModel><bindings><basicHttpBinding><binding name="BasicHttpBinding_LanguageService" closeTimeout="00:01:00"                    openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:01:00"                    allowCookies="false" bypassProxyOnLocal="false" hostNameComparisonMode="StrongWildcard"                    maxBufferSize="65536" maxBufferPoolSize="524288" maxReceivedMessageSize="65536"                    messageEncoding="Text" textEncoding="utf-8" transferMode="Buffered"                    useDefaultWebProxy="true"><readerQuotas maxDepth="32" maxStringContentLength="8192" maxArrayLength="16384"                        maxBytesPerRead="4096" maxNameTableCharCount="16384" /><security mode="None"><transport clientCredentialType="None" proxyCredentialType="None"                            realm="" /><message clientCredentialType="UserName" algorithmSuite="Default" /></security></binding></basicHttpBinding></bindings><client><endpoint address="http://api.microsofttranslator.com/V2/soap.svc"                binding="basicHttpBinding" bindingConfiguration="BasicHttpBinding_LanguageService"                contract="BingLanguageService.LanguageService" name="BasicHttpBinding_LanguageService" /></client></system.serviceModel><connectionStrings><add name="constring" connectionString= "Data Source=SIEGE;Initial Catalog=Payne;Integrated Security=True" providerName="System.Data.SqlClient"/></connectionStrings></configuration>