2022年7月21日 星期四

MS-SQL 格式化匯入和匯出資料的檔案 (SQL Server)

 參考來源:格式化匯入和匯出資料的檔案 (SQL Server)

---

*建立格式檔案*


建立非 XML 格式檔案

bcp TestDatabase.dbo.myFirstImport format nul -c -f D:\BCP\myFirstImport.fmt -t, -T


建立 XML 格式檔案

bcp TestDatabase.dbo.myFirstImport format nul -c -x -f D:\BCP\myFirstImport.xml -t, -T



*使用格式檔案大量匯入資料*


使用 bcp 和非 XML 格式檔案

REM Truncate table (for testing)

SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.MyFirstImport;"


REM Import data

bcp TestDatabase.dbo.myFirstImport IN D:\BCP\myFirstImport.bcp -f D:\BCP\myFirstImport.fmt -T


REM Review results

SQLCMD -Q "SELECT * FROM TestDatabase.dbo.MyFirstImport"



使用 bcp 和 XML 格式檔案

REM Truncate table (for testing)

SQLCMD -Q "TRUNCATE TABLE TestDatabase.dbo.MyFirstImport;"


REM Import data

bcp TestDatabase.dbo.myFirstImport IN D:\BCP\myFirstImport.bcp -f D:\BCP\myFirstImport.xml -T


REM Review results

SQLCMD -Q "SELECT * FROM TestDatabase.dbo.MyFirstImport;"


MS-SQL 觀察目前Update及Scan比例和壓縮

 觀察目前Update及Scan比例:

SELECT

    o.name AS [Table_Name]

    ,x.name AS [Index_Name]

    ,i.partition_number AS [Partition]

    ,i.index_id AS [Index_ID]

    ,x.type_desc AS [Index_Type]

    ,i.leaf_update_count * 100.0 /

    (i.range_scan_count + i.leaf_insert_count

    + i.leaf_delete_count + i.leaf_update_count

    + i.leaf_page_merge_count + i.singleton_lookup_count

    ) AS [Percent_Update]

    ,i.range_scan_count * 100.0 /

    (i.range_scan_count + i.leaf_insert_count

    + i.leaf_delete_count + i.leaf_update_count

    + i.leaf_page_merge_count + i.singleton_lookup_count

    ) AS [Percent_Scan]

FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) i

JOIN sys.objects o

    ON o.object_id = i.object_id

JOIN sys.indexes x

    ON x.object_id = i.object_id

         AND x.index_id = i.index_id

WHERE (i.range_scan_count + i.leaf_insert_count

+ i.leaf_delete_count + leaf_update_count

+ i.leaf_page_merge_count + i.singleton_lookup_count) != 0

AND OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1

ORDER BY [Percent_Update] ASC




壓縮語法:

語法也很簡單,可以針對資料表或索引分別啟用壓縮:


資料表

ALTER TABLE TableName REBUILD PARTITION = ALL

WITH (DATA_COMPRESSION = PAGE)


索引

ALTER INDEX IndexName ON TableName REBUILD PARTITION = ALL 

   WITH (DATA_COMPRESSION = PAGE)


2022年7月19日 星期二

Usando ListView com uma base de dados (語言開發和教學)

 Usando ListView com uma base de dados

===

網站有提供範例下載

該網站有非常的多語言開發和教學

想要大增功力必學   網站位置


Listview to datatable

 參考來源

---


1.

DataTable dtZeitplan = new DataTable();

foreach (ColumnHeader chZeitplan in lvZeitplan.Columns)

{

    dtZeitplan.Columns.Add(chZeitplan.Text);

}

foreach (ListViewItem item in lvZeitplan.Items)

{

    DataRow row = dtZeitplan.NewRow();

    for(int i = 0; i < item.SubItems.Count; i++)

    {

        row[i] = item.SubItems[i].Text;

    }

    dtZeitplan.Rows.Add(row);

}



2.

if (listView1.Items.Count > 0)

{

       dt.Columns.Add();

       foreach (ListViewItem.ListViewSubItem lvsi in listView1.Items[0].SubItems)

              dt.Columns.Add();

       //now we have all the columns that we need, let's add rows

       foreach (ListViewItem item in listView1.Items)

       {

              List<string> row = new List<string>();

              row.Add(item.ToString());

              foreach (var it in item.SubItems)

                   row.Add(it.ToString());

              //Add the row into the DataTable

              dt.Rows.Add(row.ToArray());

       }

}


3.

DataTable table = new DataTable();


            table.Columns.Add("MODUL", typeof(string));

            table.Columns.Add("ACIKLAMA", typeof(string));

            table.Columns.Add("UZUNLUK", typeof(string));

            table.Columns.Add("GENISLIK", typeof(string));

            table.Columns.Add("MIKTAR", typeof(string));


            for (int i = 0; i < listView2.Items.Count; i++)

            {

                table.Rows.Add(listView2.Items[i].SubItems[1].Text, listView2.Items[i].SubItems[2].Text, listView2.Items[i].SubItems[3].Text, listView2.Items[i].SubItems[4].Text, listView2.Items[i].SubItems[5].Text);

            }


4.

DataView theDataView = (DataView)theListView.ItemsSource;

DataTable theDataTable = theDataView.Table;


5.

 Dim it As Integer = 0

        Dim dt As New DataTable

        For it = 0 To LVActions.Items(0).SubItems.Count ‑ 1

            Dim DCOL As New DataColumn(LVActions.Columns(it).Text)

            dt.Columns.Add(DCOL)

        Next

        For it = 0 To LVActions.Items.Count ‑ 1

            Dim DROW As DataRow = dt.NewRow

            For j As Integer = 0 To LVActions.Items(it).SubItems.Count ‑ 1

                DROW(LVActions.Columns(j).Text) = LVActions.Items(it).SubItems(j).Text

            Next

            dt.Rows.Add(DROW)

        Next


HOW TO CONVERT OF LISTVIEW TO DATATABLE IN VB.NET

參考來源

---

 Public for Function CONVRSION_LISTVIEW_TO_DATATABLE(ByVal LS_VIEW As ListView) As DataTable

'LS_VIEW.Items.Clear()

Dim DT_TAB As New DataTable

If LS_VIEW.Items.Count < 1 Then

Return DT_TAB

Else

For i As Integer = 0 To LS_VIEW.Items(0).SubItems.Count - 1

Dim DCOL As New DataColumn(LS_VIEW.Columns(i).Text)

DT_TAB.Columns.Add(DCOL)

Next

End If

For i As Integer = 0 To LS_VIEW.Items.Count - 1

Dim DROW As DataRow = DT_TAB.NewRow

For j As Integer = 0 To LS_VIEW.Items(i).SubItems.Count - 1

DROW(LS_VIEW.Columns(j).Text) = LS_VIEW.Items(i).SubItems(j).Text

Next

DT_TAB.Rows.Add(DROW)

Next

Return DT_TAB

End Function

HOW TO FILL UP A LISTVIEW FROM DATABASE IN VB.NET USING A DATATABLE

 參考網址

--


Sub FillListViewByTable(ByVal listView As ListView, ByVal dt As DataTable)

        Dim ii As Integer

        Dim iColCount As Integer = dt.Columns.Count

        Dim iRowCount As Integer = dt.Rows.Count

        listView.Clear()

        For ii = 0 To iColCount – 1

            listView.Columns.Add(dt.Columns(ii).ColumnName, 250, HorizontalAlignment.Left)

          

        Next

   

        Dim i As Integer

        Dim x As Integer

        For i = 0 To iRowCount – 1  ‘ for each row in the table

            Dim lst As New ListViewItem

            lst.Text = dt.Rows(i).Item(0)

            lst.UseItemStyleForSubItems = True

            For x = 1 To iColCount – 1 ‘ for each column in the row of the table

                Application.DoEvents()

                lst.SubItems.Add(SNZ(dt.Rows(i).Item(x)))

             

            Next


               Application.DoEvents()

           ‘    If you want to display the progress then you can do it here

           ‘ myStatusLabel.text = “Displaying Record ” & i + 1 & “/” & iRowCount

            listView.Items.Add(lst)

            lst = Nothing

        Next

    

    End Sub

Crear y LLenar DataTable y Transferir Datos a Listview

 參考來源(不錯的網站)

----


Crear y LLenar DataTable y Transferir Datos a Listview

    Public Sub AGREGAR(ByVal Archivo_ As String, ByVal URL_ As String, ByVal Destino_ As String)


        Dim dt As New DataTable()

        dt.Columns.Add("Archivo")

        dt.Columns.Add("URL")

        dt.Columns.Add("Destino")

        Dim row As DataRow = dt.NewRow()

        row("Archivo") = Archivo_

        row("URL") = URL_

        row("Destino") = Destino_

        dt.Rows.Add(row)


        Dim lvItem As ListViewItem

        For y = 0 To dt.Rows.Count - 1

            lvItem = ListView1.Items.Add(dt.Rows(y)(0))

            'FILA DESCRIPCIÓN

            lvItem.SubItems.Add(dt.Rows(y)(1))

            lvItem.SubItems.Add(dt.Rows(y)(2))

        Next

    End Sub

Bind ListView to DataTable

 Bind ListView to DataTable


T-SQL:


    CREATE PROCEDURE GetMembers


    AS

    SET NOCOUNT ON

    SELECT

        FirstName,

        LastName,

        NickName,

        FavoriteLanguage

    FROM

        Members


VB.NET


Private Function GetMembers() As DataTable

    Dim connection As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)

    Dim command As SqlCommand = connection.CreateCommand

    command.CommandText = "GetMembers"

    command.CommandType = Data.CommandType.StoredProcedure

 

    Dim table As New DataTable

    connection.Open()

    table.Load(command.ExecuteReader(Data.CommandBehavior.CloseConnection))

 

    Return table

End Function


Protected Sub Page_Load(ByVal sender As Object, _

    ByVal e As System.EventArgs) Handles Me.Load

    Me.ListView1.DataSource = GetMembers()

    Me.ListView1.DataBind()

End Sub