DataTable数据集录入到数据库表

   此方法在自己最近写的这个程序中经常用到，感觉非常好用。尤其是将TreeView、Menu等，保存到数据库相对的表中时。将些记录下来，以供大家参考。
 

            /// DataTable记录集导入到数据库表
            /// </summary>
            /// <param name="connString">数据库联结串</param>
            /// <param name="SouseDataTable">要导入的数据DataTable</param>
            /// <param name="DestTableName">目的表名</param>
            /// <param name="IsAppendRow">是否追加记录</param>
            /// <returns></returns>
            public static bool DataTableInputDataBase(string connString, DataTable SouseDataTable, string DestTableName, bool IsAppendRow)
            {
                if (SouseDataTable.Rows.Count > 0)
                {
                    if (IsAppendRow == false)
                    {
                        HR.AuditSystem.DBUtility.DbHelperSQL.ExecuteSql("delete from " + DestTableName);
                    }
                    SqlConnection SqlCnn = new SqlConnection(connString);
                    SqlCnn.Open();
                    DataTable NewTable = OrdinalTable(connString, SouseDataTable, DestTableName);
                    SqlBulkCopy Bcp = new SqlBulkCopy(SqlCnn);
                    Bcp.DestinationTableName = DestTableName;
                    Bcp.WriteToServer(NewTable);
                    Bcp.Close();
                    SqlCnn.Close();
                }
                SouseDataTable.Reset();
                SouseDataTable.Dispose();
                return true;
            }

            /// <summary>
            /// 调整表字段顺序
            /// </summary>
            /// <param name="SqlConnstring">数据库联结串</param>
            /// <param name="DTS">源DataTable</param>
            /// <param name="DestTableName">目标表名</param>
            /// <returns></returns>
            private static DataTable OrdinalTable(string SqlConnstring, DataTable DTS, string DestTableName)
            {
                System.Data.SqlClient.SqlConnection SqldbCnn = new System.Data.SqlClient.SqlConnection(SqlConnstring);
                DataTable DTD = new DataTable();
                System.Data.SqlClient.SqlDataAdapter SqlDbDA = new System.Data.SqlClient.SqlDataAdapter("select top 1 * from " + DestTableName, SqldbCnn);
                SqldbCnn.Open();
                SqlDbDA.Fill(DTD);
                SqlDbDA.Dispose();
                SqldbCnn.Close();
                DTD.Clear();
                int I = 0;
                int J = 0;
                string CNS = "";
                string CND = "";
                int MaxCol = 0;
                for (I = 0; I < DTS.Columns.Count; I++)
                {
                    CNS = DTS.Columns[I].ColumnName;
                    for (J = 0; J <= DTD.Columns.Count - 1; J++)
                    {
                        CND = DTD.Columns[J].ColumnName;
                        if (CND.ToUpper() == CNS.ToUpper())
                        {
                            if (MaxCol < J)
                            {
                                MaxCol = J;
                                break;
                            }
                        }
                    }
                }
                for (J = 0; J <= MaxCol; J++)
                {
                    if (DTS.Columns.IndexOf(DTD.Columns[J].ColumnName) < 0)
                    {
                        DTS.Columns.Add(DTD.Columns[J].ColumnName.ToUpper(), DTD.Columns[J].DataType);
                    }
                }

                for (I = 0; I <= DTD.Columns.Count - 1; I++)
                {
                    CND = DTD.Columns[I].ColumnName;
                    for (J = 0; J <= DTS.Columns.Count - 1; J++)
                    {
                        CNS = DTS.Columns[J].ColumnName;
                        if (CNS.ToUpper() == CND.ToUpper())
                        {
                            DTS.Columns[J].SetOrdinal(I);
                            break;
                        }
                    }
                }
                return DTS;
            }