SqlServer 版本获得新增SQL语句的代码

2015-04-14 09:28:33 访问(1654) 赞(0) 踩(0)



        /// <summary>
        /// SqlServer 版本获得新增SQL语句的代码
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="ds"></param>
        /// <returns></returns>
        protected string SqlServerGetInsertSQL
            (
                string tableName, 
                DataSet ds
            )
        {
            DataTable dt = ds.Tables[0];

            DataColumnCollection dcc = dt.Columns;

            StringBuilder theResult = new StringBuilder();
            StringBuilder sqlItem = null;
            StringBuilder sqlParams = null;

            Type typeofDateTime = typeof(DateTime);
            Type typeofint = typeof(int);
            Type typeoflong = typeof(long);
            Type typeoffloat = typeof(float);
            Type typeofdecimal = typeof(decimal);
            Type typeofdouble = typeof(double);


            bool isFirst = true;
            object oValue = null;
            string strValue = "";
            foreach (DataRow dr in dt.Rows)
            {
                if (dr == null)
                    continue;

                isFirst = true;
                sqlItem = new StringBuilder();
                sqlParams = new StringBuilder();

                foreach (DataColumn dc in dcc)
                {
                    if (isFirst)
                    {
                        isFirst = false;
                    }
                    else
                    {
                        sqlItem.Append(",");
                        sqlParams.Append(",");
                    }

                    oValue = dr[dc.ColumnName];

                    if (oValue == null || oValue == DBNull.Value)
                        strValue = "null";
                    else if (dc.DataType == typeofDateTime)
                    {
                        strValue = "convert(datetime, '" + Convert.ToDateTime(oValue).ToString("yyyy-MM-dd HH:mm:ss") + "', 20)";
                    }
                    else if (dc.DataType == typeofint || dc.DataType == typeoflong
                        || dc.DataType == typeoffloat || dc.DataType == typeofdouble
                        || dc.DataType == typeofdecimal)
                    {
                        strValue = oValue.ToString();
                    }
                    else
                    {
                        strValue = "'" + oValue.ToString().Replace("'", "''") + "'";
                    }

                    sqlItem.Append(dc.ColumnName);
                    sqlParams.Append(strValue);
                }

                theResult.AppendLine("insert into " + tableName + " (" + sqlItem.ToString() + ") values (" + sqlParams.ToString() + ") ;");
            }

            return theResult.ToString();
        }