locked
Change definition for a DataTable RRS feed

  • Question

  • Hello,

    I have a DataTable and it has certain columns with "Double" data type. I am out putting the DataTable by using Out-DataTable found at http://gallery.technet.microsoft.com/ScriptCenter/4208a159-a52e-4b99-83d4-8048468d29dd/

    In SQL, Double data type is not available, instead float or decimal can be used. I was wondering if I can change the data type from double to float as I intend to then store the data from DataTable in to a SQL table. Below is the DataTable member details:

    Name        MemberType Definition                   
    ----        ---------- ----------                   
    Date        Property   string Date {get;set;}       
    DriveName   Property   string DriveName {get;set;}  
    Error       Property   string Error {get;set;}      
    FreeSpace   Property   double FreeSpace {get;set;}  
    PercentFree Property   double PercentFree {get;set;}
    ServerName  Property   string ServerName {get;set;} 
    Time        Property   string Time {get;set;}       
    TotalSpace  Property   double TotalSpace {get;set;} 
    UsedSpace   Property   double UsedSpace {get;set;}  
    

    Please advice, Thanks in advance.

    Rajiv

    Friday, July 11, 2014 4:57 AM

Answers

  • This is how to list all legitimate database types recognized by the framework.

    PS C:\scripts> [enum]::GetNames([System.Data.DBType])
    AnsiString
    Binary
    Byte
    Boolean
    Currency
    Date
    DateTime
    Decimal
    Double
    Guid
    Int16
    Int32
    Int64
    Object
    SByte
    Single
    String
    Time
    UInt16
    UInt32
    UInt64
    VarNumeric
    AnsiStringFixedLength
    StringFixedLength
    Xml
    DateTime2
    DateTimeOffset
    PS C:\scripts>

    Notice that "double" is listed.


    ¯\_(ツ)_/¯

    • Marked as answer by Rajiv IR Monday, July 14, 2014 4:09 AM
    Friday, July 11, 2014 9:13 AM

All replies

  • Windows will do this for you if needed.

    It is a default coercion.

    $x=[double]3.213
    $y=[float]$x
    $y


    ¯\_(ツ)_/¯

    Friday, July 11, 2014 8:46 AM
  • The example you gave is for a variable. How can I do it for the DataTable? Please help

    Friday, July 11, 2014 8:49 AM
  • Please guide me on any article through which I can get this done. This is creating a hurdle in my workflow.
    Friday, July 11, 2014 8:50 AM
  • Please guide me on any article through which I can get this done. This is creating a hurdle in my workflow.

    How are you updating the datatable?  What API or utility are you using?  SQLServer and most servers will coerce the change automatically assuming the data is in range.


    ¯\_(ツ)_/¯

    Friday, July 11, 2014 8:56 AM
  • Why do you say double is not available. It is a known mapping.

    See: http://msdn.microsoft.com/en-us/library/system.data.datacolumn.datatype(v=vs.110).aspx


    ¯\_(ツ)_/¯

    Friday, July 11, 2014 9:05 AM
  • This is how to list all legitimate database types recognized by the framework.

    PS C:\scripts> [enum]::GetNames([System.Data.DBType])
    AnsiString
    Binary
    Byte
    Boolean
    Currency
    Date
    DateTime
    Decimal
    Double
    Guid
    Int16
    Int32
    Int64
    Object
    SByte
    Single
    String
    Time
    UInt16
    UInt32
    UInt64
    VarNumeric
    AnsiStringFixedLength
    StringFixedLength
    Xml
    DateTime2
    DateTimeOffset
    PS C:\scripts>

    Notice that "double" is listed.


    ¯\_(ツ)_/¯

    • Marked as answer by Rajiv IR Monday, July 14, 2014 4:09 AM
    Friday, July 11, 2014 9:13 AM
  • Hello,

    I am using a PowerShell script to convert the data to a DATA TABLE and then write the data to SQL using Bulk Copy. The script uses .NET API for conversion.  Both the script can be found on the below link:

    Out-DataTable -  http://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd

    Write-DataTable - http://gallery.technet.microsoft.com/ScriptCenter/2fdeaf8d-b164-411c-9483-99413d6053ae/

    The SQL Server version is SQL Server 2008 R2 SP2. 

     

    The problem was from my side. I was referencing an incorrect data variable. It works now. Thanks very much for your advice.  

    Monday, July 14, 2014 4:09 AM
  • Also it would be worth to know if we can still change the Data Definition for a DataTable :) Please guide me if possible.

    Thanks in advance.

    Rajiv

    Monday, July 14, 2014 4:10 AM
  • Also it would be worth to know if we can still change the Data Definition for a DataTable :) Please guide me if possible.

    Thanks in advance.

    Rajiv

    You are doing this the absolutely hardest way possible. With Net BulkCopy you can jest add all columns and th types will be automatically converted if possible.

    Here is an example of how to bulk copy to SQLServer from a data table of any kind.  The example uses Excel but SQL or CSV data can be used.

    # Copy-SQLBulk.ps1
    # This show how to use BulCopy in PowerShell by uploading a spreadsheet ot an MSSQLServer data table.
    
    $filepath = 'C:\scripts\All.xlsx'
    $excelConnection="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$filepath;Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"
    $sqlConnection='Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=issue;Data Source=OMEGA\SQLEXPRESS;'
    $excelQuery='select * from [SearchResult$]'
    $tablename='SearchResult'
    
    Try{
        $conn = New-Object System.Data.OleDb.OleDbConnection($excelConnection) 
        $conn.open()
        $cmd=$conn.CreateCommand()
        $cmd.CommandText=$excelQuery
        $rdr=$cmd.ExecuteReader()
        
        # create the BC object
        $sqlbc=[System.Data.SqlClient.SqlBulkCopy]$sqlConnection
        $sqlbc.DestinationTableName=$tableName
        
        # add all columns - you can add as few  as you like.
        for($i=0; $i -lt $rdr.FieldCount;$i++){
            $fname=$rdr.GetName($i)
            Write-Host $fname -ForegroundColor green
            [void]$sqlbc.ColumnMappings.Add($fname, $fname)
        }
        
        # write all of teh data to the table
        $sqlbc.WriteToServer($rdr)
    }
    Catch{
        Write-Host "$_" -ForegroundColor red
    }
    
    $sqlbc.Close()
    $conn.Close()
    
    
    

    All types will be converted according to defaults except for NULLs and rand issues.  For example you cannot assign a null to a field that does not allow nulls and you cannot take a value like 65000 and copy it into a short int. or a byte.  You can filter errors by row and trap missed rows.

    Updating SQL data takes a good knowledge of SQL databases and is not a trivial task with other than very simple tables.


    ¯\_(ツ)_/¯

    Monday, July 14, 2014 4:22 AM