2016年9月13日 星期二

[研究] SQL Server 資料庫 NULL 欄位調正(二) ASP.NET + C#

[研究] SQL Server 資料庫 NULL 欄位調正(二) ASP.NET + C#

2016-09-12

續這篇

[研究] SQL Server 資料庫 NULL 欄位調正(一)
http://shaurong.blogspot.com/2016/09/sql-server-null.html

後來打算寫個程式來處理，產生3個 SQL 語法，可以用來直接丟到 SQL Server Management Studio 2016 v16.3 上跑。
為何不寫程式跑完全部？為了可以在SQL Server Management Studio 2016上看到是否仍有其他問題。


WebForm1.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="WebApplication1.WebForm1" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Button ID="Button1" runat="server" Text="2. Generate SQL - Set NULL field Format the DEFAULT Value" OnClick="Button1_Click" /><br />
        <br />
        <asp:Button ID="Button2" runat="server" Text="3. Generate SQL - Give NULL field a Value" OnClick="Button2_Click" /><br />
        <br />
        <asp:Button ID="Button3" runat="server" Text="4. Generate SQL - Change NULL Format to NOT NULL Format" OnClick="Button3_Click" /><br />
        <br />
        <asp:Literal ID="Literal1" runat="server"></asp:Literal><br />
        <asp:Literal ID="Literal2" runat="server" Visible="false"></asp:Literal><br />
    </div>
    </form>
</body>
</html>



資料庫的帳號、密碼、主機IP、資料庫名稱請依照自己環境修改

WebForm1.aspx.cs

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace WebApplication1
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        const string connectionString = "Data Source=.;Initial Catalog=pms;User ID=sa;Password=P@ssw0rd";
        protected void Page_Load(object sender, EventArgs e)
        {
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            Literal1.Text = "";
            using (SqlConnection connection = new SqlConnection(
                connectionString))
            {
                SqlCommand command = new SqlCommand(@"

SELECT 'ALTER  TABLE ['+ [TABLE_NAME] + '] ADD  CONSTRAINT [DF_' + [TABLE_NAME] + '_' + [COLUMN_NAME] +'] DEFAULT ('''') FOR [' + [COLUMN_NAME] + '];'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
 and  [TABLE_NAME] not like '%zDel%'
 and  [TABLE_NAME] not like '%aspnet%'
 and  [TABLE_NAME] not like '%__MigrationHistory%'
 and  [TABLE_NAME] not like 'View%'
 and  [TABLE_NAME] <> '%ELMAH_Error%'
 and  [TABLE_NAME] <> 'Sessions'
 and  [TABLE_NAME] <> 'sysdiagrams'
 and IS_NULLABLE ='YES'
 and COLUMN_DEFAULT is NULL
 and ( DATA_TYPE='ntext'  or DATA_TYPE='nvarchar' )

union

SELECT 'ALTER  TABLE ['+ [TABLE_NAME] + '] ADD  CONSTRAINT [DF_' + [TABLE_NAME] + '_' + [COLUMN_NAME] +'] DEFAULT (getdate()) FOR [' + [COLUMN_NAME] + '];'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
 and  [TABLE_NAME] not like '%zDel%'
 and  [TABLE_NAME] not like '%aspnet%'
 and  [TABLE_NAME] not like '%__MigrationHistory%'
 and  [TABLE_NAME] not like 'View%'
 and  [TABLE_NAME] <> '%ELMAH_Error%'
 and  [TABLE_NAME] <> 'Sessions'
 and  [TABLE_NAME] <> 'sysdiagrams'
 and IS_NULLABLE ='YES'
 and COLUMN_DEFAULT is NULL
 and ( DATA_TYPE='datetime'  or DATA_TYPE='datetime2' )

union

SELECT 'ALTER  TABLE ['+ [TABLE_NAME] + '] ADD  CONSTRAINT [DF_' + [TABLE_NAME] + '_' + [COLUMN_NAME] +'] DEFAULT (0) FOR [' + [COLUMN_NAME] + '];'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
 and  [TABLE_NAME] not like '%zDel%'
 and  [TABLE_NAME] not like '%aspnet%'
 and  [TABLE_NAME] not like '%__MigrationHistory%'
 and  [TABLE_NAME] not like 'View%'
 and  [TABLE_NAME] <> '%ELMAH_Error%'
 and  [TABLE_NAME] <> 'Sessions'
 and  [TABLE_NAME] <> 'sysdiagrams'
 and IS_NULLABLE ='YES'
 and COLUMN_DEFAULT is NULL
 and ( DATA_TYPE='int')

union

SELECT 'ALTER  TABLE ['+ [TABLE_NAME] + '] ADD  CONSTRAINT [DF_' + [TABLE_NAME] + '_' + [COLUMN_NAME] +'] DEFAULT (1) FOR [' + [COLUMN_NAME] + '];'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
 and  [TABLE_NAME] not like '%zDel%'
 and  [TABLE_NAME] not like '%aspnet%'
 and  [TABLE_NAME] not like '%__MigrationHistory%'
 and  [TABLE_NAME] not like 'View%'
 and  [TABLE_NAME] <> '%ELMAH_Error%'
 and  [TABLE_NAME] <> 'Sessions'
 and  [TABLE_NAME] <> 'sysdiagrams'
 and IS_NULLABLE ='YES'
 and COLUMN_DEFAULT is NULL
 and ( DATA_TYPE='bit')
 and ( COLUMN_NAME='IsValid')

union

SELECT 'ALTER  TABLE ['+ [TABLE_NAME] + '] ADD  CONSTRAINT [DF_' + [TABLE_NAME] + '_' + [COLUMN_NAME] +'] DEFAULT (0) FOR [' + [COLUMN_NAME] + '];'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
 and  [TABLE_NAME] not like '%zDel%'
 and  [TABLE_NAME] not like '%aspnet%'
 and  [TABLE_NAME] not like '%__MigrationHistory%'
 and  [TABLE_NAME] not like 'View%'
 and  [TABLE_NAME] <> '%ELMAH_Error%'
 and  [TABLE_NAME] <> 'Sessions'
 and  [TABLE_NAME] <> 'sysdiagrams'
 and IS_NULLABLE ='YES'
 and COLUMN_DEFAULT is NULL
 and ( DATA_TYPE='bit')
 and ( COLUMN_NAME<>'IsValid')
;",
                  connection);
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                string str = "";
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {

                        str = reader[0].ToString();
                        if (Literal1.Text == "")
                        {
                            Literal1.Text = "use pms;" + "<br />GO<br /><br />";
                            Literal1.Text = Literal1.Text + "delete InformationSystem where todoid is null;" + "<br />GO<br /><br />";   // 修正 Bug
                        }
                        else
                            Literal1.Text = Literal1.Text + str + "<br />GO<br /><br />";
                    }
                }
                else
                {
                    //Console.WriteLine("No rows found.");
                }
                reader.Close();
                connection.Close();
            }
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            Literal1.Text = "";
            using (SqlConnection connection = new SqlConnection(
                connectionString))
            {
                SqlCommand command = new SqlCommand(@"

-- 若 ntext, nvarchar 欄位允許 null ，先更新欄位內容
SELECT 'UPDATE ['+ [TABLE_NAME] + '] SET [' + [COLUMN_NAME] +']='''' WHERE [' + [COLUMN_NAME] + '] is NULL;'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
 and  [TABLE_NAME] not like '%zDel%'
 and  [TABLE_NAME] not like '%aspnet%'
 and  [TABLE_NAME] not like '%__MigrationHistory%'
 and  [TABLE_NAME] not like 'View%'
 and  [TABLE_NAME] <> '%ELMAH_Error%'
 and  [TABLE_NAME] <> 'Sessions'
 and  [TABLE_NAME] <> 'sysdiagrams'
 and IS_NULLABLE ='YES'
 and ( DATA_TYPE='ntext'  or DATA_TYPE='nvarchar' )
group by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE
--order by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE
union
-- 若 datetime, datetime2 欄位允許 null ，先更新欄位內容
SELECT 'UPDATE ['+ [TABLE_NAME] + '] SET [' + [COLUMN_NAME] +']=''0-1-1'' WHERE [' + [COLUMN_NAME] + '] is NULL;'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
 and  [TABLE_NAME] not like '%zDel%'
 and  [TABLE_NAME] not like '%aspnet%'
 and  [TABLE_NAME] not like '%__MigrationHistory%'
 and  [TABLE_NAME] not like 'View%'
 and  [TABLE_NAME] <> '%ELMAH_Error%'
 and  [TABLE_NAME] <> 'Sessions'
 and  [TABLE_NAME] <> 'sysdiagrams'
 and IS_NULLABLE ='YES'
 and ( DATA_TYPE='datetime'  or DATA_TYPE='datetime2' )
group by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE
--order by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE
union
-- 若 int 欄位允許 null ，先更新欄位內容
SELECT 'UPDATE ['+ [TABLE_NAME] + '] SET [' + [COLUMN_NAME] +']=''0'' WHERE [' + [COLUMN_NAME] + '] is NULL;'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
 and  [TABLE_NAME] not like '%zDel%'
 and  [TABLE_NAME] not like '%aspnet%'
 and  [TABLE_NAME] not like '%__MigrationHistory%'
 and  [TABLE_NAME] not like 'View%'
 and  [TABLE_NAME] <> '%ELMAH_Error%'
 and  [TABLE_NAME] <> 'Sessions'
 and  [TABLE_NAME] <> 'sysdiagrams'
 and IS_NULLABLE ='YES'
 and ( DATA_TYPE='int')
group by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE
--order by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE

union
-- 若 bit 欄位允許 null ，且欄位為 IsValid，先更新欄位內容
SELECT 'UPDATE ['+ [TABLE_NAME] + '] SET [' + [COLUMN_NAME] +']=''1'' WHERE [' + [COLUMN_NAME] + '] is NULL;'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
 and  [TABLE_NAME] not like '%zDel%'
 and  [TABLE_NAME] not like '%aspnet%'
 and  [TABLE_NAME] not like '%__MigrationHistory%'
 and  [TABLE_NAME] not like 'View%'
 and  [TABLE_NAME] <> '%ELMAH_Error%'
 and  [TABLE_NAME] <> 'Sessions'
 and  [TABLE_NAME] <> 'sysdiagrams'
 and IS_NULLABLE ='YES'
 and ( DATA_TYPE='bit')
 and ( COLUMN_NAME='IsValid')
group by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE
--order by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE

union
-- 若 bit 欄位允許 null ，且欄位為 IsValid，先更新欄位內容
SELECT 'UPDATE ['+ [TABLE_NAME] + '] SET [' + [COLUMN_NAME] +']=''0'' WHERE [' + [COLUMN_NAME] + '] is NULL;'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
 and  [TABLE_NAME] not like '%zDel%'
 and  [TABLE_NAME] not like '%aspnet%'
 and  [TABLE_NAME] not like '%__MigrationHistory%'
 and  [TABLE_NAME] not like 'View%'
 and  [TABLE_NAME] <> '%ELMAH_Error%'
 and  [TABLE_NAME] <> 'Sessions'
 and  [TABLE_NAME] <> 'sysdiagrams'
 and IS_NULLABLE ='YES'
 and ( DATA_TYPE='bit')
 and ( COLUMN_NAME<>'IsValid')
group by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE
--order by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE
;",
                  connection);
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                string str = "";
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {

                        str = reader[0].ToString();
                        if (Literal1.Text == "")
                        {
                            Literal1.Text = "use pms;" + "<br />GO<br /><br />";
                        }
                        else
                            Literal1.Text = Literal1.Text + str + "<br />GO<br /><br />";
                    }
                }
                else
                {
                    //Console.WriteLine("No rows found.");
                }
                reader.Close();
                connection.Close();
            }
        }

        protected void Button3_Click(object sender, EventArgs e)
        {
            Literal1.Text = "";
            Literal2.Text = "";
            using (SqlConnection connection = new SqlConnection(
                connectionString))
            {
                SqlCommand command = new SqlCommand(
                  @"select DISTINCT [TABLE_NAME] FROM information_schema.columns
                  WHERE TABLE_CATALOG = 'pms'

    and[TABLE_NAME] not like '%zDel%'

    and[TABLE_NAME] not like '%aspnet%'

    and[TABLE_NAME] not like '%__MigrationHistory%'

    and[TABLE_NAME] not like 'View%'

    and[TABLE_NAME] <> '%ELMAH_Error%'

    and[TABLE_NAME] <> 'Sessions'

    and[TABLE_NAME] <> 'sysdiagrams'
    ; ",
                  connection);
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                string str = "";
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {

                        str = @" select 'ALTER TABLE [" + reader[0].ToString() + @"] ALTER COLUMN ' +
QUOTENAME(c.name) + ' ' + t.name +
case when t.name in ('nvarchar','nchar') and c.max_length <>-1
        then '('+ cast(c.max_length/2 as nvarchar) +')'
     when t.name in ('nvarchar','nchar') and c.max_length =-1
        then '(MAX)'
    when t.name in ('varchar','binary', 'char', 'varbinary')
        then '('+ cast(c.max_length as nvarchar) +')'
else '' end + ' NOT NULL; '
from sys.columns c join sys.types t on c.system_type_id = t.system_type_id
where object_id = object_id('dbo." + reader[0].ToString() + @"') and t.name <> 'sysname'
and t.is_nullable =1
";
                        //order by column_id";
                        if (Literal1.Text == "")
                        {
                            Literal1.Text = "use pms;" + "<br />GO<br /><br />";
                            Literal1.Text = "(" + str + ")";
                            Literal2.Text = "use pms;\nGO\n";
                            Literal2.Text = "(" + str + ")\n";
                        }
                        else
                            Literal1.Text = Literal1.Text + "<br /><br />union<br /><br />" + "(" + str + ")";
                            Literal2.Text = Literal2.Text + "\nunion\n" + "(" + str + ")\n";
                    }
                }
                else
                {
                    //Console.WriteLine("No rows found.");
                }
                reader.Close();
            }
            //

            Literal1.Text = "";
            using (SqlConnection connection = new SqlConnection(
                connectionString))
            {
                SqlCommand command = new SqlCommand(Literal2.Text,connection);
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();
                string str = "";
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {

                        str = reader[0].ToString();
                        if (Literal1.Text == "")
                        {
                            Literal1.Text = "use pms;" + "<br />GO<br /><br />";
                        }
                        else
                            Literal1.Text = Literal1.Text + str + "<br /><br />GO<br /><br />";
                    }
                }
                else
                {
                    //Console.WriteLine("No rows found.");
                }
                reader.Close();
                connection.Close();
            }
        }
    }
}



(完)

相關

[研究] 用SQL指令找出資料庫的資料表、欄位名、PK欄位、資料筆數
http://shaurong.blogspot.tw/2016/02/sqlpk.html

[研究] SQL Server 資料庫 NULL 欄位調正(一)
http://shaurong.blogspot.com/2016/09/sql-server-null.html

[研究] SQL Server 資料庫 NULL 欄位調正(二) ASP.NET + C#
http://shaurong.blogspot.com/2016/09/sql-server-null-aspnet-c.html


張貼者： 於

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)