SqlServer 版本DataSet和TableName获得新增SQL脚本的代码

2014-06-17 17:06:36 访问(3209) 赞(0) 踩(0)


    /// 
    ///  SqlServer 版本DataSet和TableName获得新增SQL脚本的代码
    /// 
    /// 
    /// 
    /// 
    protected string GetInsertSQL(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();
    }

输出SQL样例

insert into UTB_WEB_TOOL_SORT (ID,TheName,TheCode,PID,Layer,Path,ShowSeq,IsDelete,IsShow,Remark,CreateTime,UpdateTime) values (1,'常用工具','常用工具',0,1,',0,1,',1,'0','1',null,convert(datetime, '2014-02-15 18:57:23', 20),convert(datetime, '2014-02-15 18:57:23', 20)) ;
insert into UTB_WEB_TOOL_SORT (ID,TheName,TheCode,PID,Layer,Path,ShowSeq,IsDelete,IsShow,Remark,CreateTime,UpdateTime) values (2,'数据库工具','数据库工具',0,1,',0,2,',2,'0','1',null,convert(datetime, '2014-02-15 23:11:40', 20),convert(datetime, '2014-02-15 23:11:40', 20)) ;