SqlDataAdapter 更新带有自增种子的数据表

LGame 2015-01-14 05:16:24

因为需要将后台数据更新到前台数据库,但数据表中的主件事自增ID

抛出了下面的异常
{System.Data.SqlClient.SqlException (0x80131904): 当 IDENTITY_INSERT 设置为 ON 或某个复制用户向 NOT FOR REPLICATION 标识列中插入内容时，必须为表 't_MultipleSale' 中的标识列指定显式值。

不能修改前台数据库的表结构,

谢谢大家

数据表:



CREATE TABLE [dbo].[t_MultipleSale](

	[F_ID] [bigint] IDENTITY(1,1) NOT NULL,

	[F_Name] [nvarchar](60) NULL,

	[F_Start] [datetime] NULL,

	[F_End] [datetime] NULL,

 CONSTRAINT [PK_t_MultipleSale] PRIMARY KEY CLUSTERED 

(

	[F_ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

测试代码.



using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

using System.Collections;



namespace WindowsFormsApplication1

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }



        private void button1_Click(object sender, EventArgs e)

        {

            DataSet dsData = GetDs("SELECT F_ID,F_Name,F_Start,F_End FROM t_MultipleSale where 1=2");



            Hashtable htParm = new Hashtable();

            htParm.Add("1", "SELECT F_ID,F_Name,F_Start,F_End FROM t_MultipleSale where 1=2 ");



            DataTable dtUpd = dsData.Tables[0].Copy();



            DataRow drNew = dtUpd.NewRow();

            drNew["F_ID"] = 11;

            drNew["F_Name"] = "test";

            dtUpd.Rows.Add(drNew);



            DataSet dsSync = new DataSet();

            dtUpd.TableName = "1";

            dsSync.Tables.Add(dtUpd.Copy());

            dsSync.Tables[0].TableName = "1";



            SaveData(dsSync, htParm);

            

        }



        /// <summary>

        /// 取得连接字符串

        /// </summary>

        /// <returns></returns>

        public string GetConStr()

        {

            string strCon = "";

            string strFile = AppDomain.CurrentDomain.BaseDirectory + "Set.ini";

            string strServer = @"DBSERVER\SQL2008R2";

            string strLogID = "sa";

            string strLogPsw = "Avail8941";

            string strDB = "NewCloth_Sync";



            //Data Source=20090911-1332\OK;Initial Catalog=water;Persist Security Info=True;User ID=sa;Password=1



            strCon = @"Data Source=" + strServer + ";Initial Catalog=" + strDB + ";User ID=" + strLogID + ";Password=" + strLogPsw;

            //strCon = "Data Source=" + strServer + ";Initial Catalog=" + strDB + ";Persist Security Info=True;User ID=" + strLogID + ";Password=" + strLogPsw + ";Network Library=dbnmpntw";

            return strCon;

        }

        

        public string SaveData(DataSet ds, Hashtable htParm)

        {

            string sSQL;

            int iCnt = 0;

            SqlConnection con = new SqlConnection(GetConStr());

            con.Open();

            SqlTransaction tran = con.BeginTransaction();

            try

            {

                iCnt = htParm.Count;



                for (int i = 1; i <= iCnt; i++)

                {

                    sSQL = htParm[i.ToString()].ToString();



                    DataTable dt = ds.Tables[i.ToString()];



                    SqlCommand myComm = new SqlCommand();

                    myComm.Connection = con;

                    myComm.Transaction = tran;





                    myComm.CommandText = " SET IDENTITY_INSERT t_MultipleSale  ON ";

                    myComm.ExecuteNonQuery();



                    //myComm.CommandText = " SET IDENTITY_INSERT t_MultipleSale  ON ; " + sSQL;

                    myComm.CommandText = sSQL;



                    myComm.CommandTimeout = 3000;



                    SqlDataAdapter myAdt = new SqlDataAdapter();

                    myAdt.SelectCommand = myComm;



                    SqlCommandBuilder bd = new SqlCommandBuilder(myAdt);

                    myAdt.Update(dt);



                    myComm.CommandText = " SET IDENTITY_INSERT t_MultipleSale  OFF ";

                    myComm.ExecuteNonQuery();

                }

                tran.Commit();

                return "";

            }

            catch (SqlException ex)

            {

                tran.Rollback();

                if (ex.Number == 2627)

                    return "数据重复，请检查!!";

                else

                    return ex.Message;

            }

        }





        public DataSet GetDs(string strSQL)

        {

            SqlConnection con = new SqlConnection(GetConStr());

            SqlDataAdapter adt = new SqlDataAdapter(strSQL, con);

            DataSet ds = new DataSet();

            adt.Fill(ds);

            return ds;

            // DataSet ds = SqlHelper.ExecuteDataset(GetConStr(), CommandType.Text, strSQL);

            // return ds;

        }

    }

}