Database

Xmen wrote:

currently i'm using bigint and i think that bigint is taking more space than a string, just a think, cant say surely....

now i wanna know that what variable should i use, bigint or nvarchar ??

currently i'm using bigint and i think that bigint is taking more space than a string

so please suggest about it with a good reason

Eammonn Murray's article helped me to find the existing sql servers, 
but it didnt deducted the existing databases in the network,

        Private Function OS(ByVal Word As String) As String<br />
            Dim i As Integer = Word.IndexOf(".")<br />
            While i > -1<br />
                Word = Word.Remove(i, 1)<br />
                i = Word.IndexOf(".")<br />
            End While<br />
            Return Word<br />
        End Function<br />
<br />
        Public Sub ExportDatasetToExcel(ByVal Location As String, ByVal DS As DataSet)<br />
<br />
            If My.Computer.FileSystem.FileExists(Location) Then<br />
                Try<br />
                    My.Computer.FileSystem.DeleteFile(Location)<br />
                Catch ex As System.IO.IOException<br />
                    Dim MsgStr As String = "The file is in use by another process. Close all programs that might use the file and try again."<br />
                    MsgBox(MsgStr, MsgBoxStyle.Exclamation)<br />
                    Exit Sub<br />
                Catch ex As Exception<br />
                    Dim MsgStr As String = String.Format("This shouldn't happen, call support{0}Error message:{0}{1}{0}{0}Stacktrace:{0}{2}", Environment.NewLine, ex.Message, ex.StackTrace)<br />
                    MsgBox(MsgStr, MsgBoxStyle.Exclamation)<br />
                    Exit Sub<br />
                End Try<br />
            End If<br />
            Dim CreateString As New StringBuilder<br />
            Dim Columns As New StringBuilder<br />
            Dim Mark As New StringBuilder<br />
            Dim ConnectionStr As String<br />
            ConnectionStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Location & ";Extended Properties=""Excel 8.0;HDR=YES"""<br />
            Using Connection As New OleDb.OleDbConnection(ConnectionStr)<br />
                Connection.Open()<br />
                For Each table As DataTable In DS.Tables<br />
                    CreateString.AppendFormat("CREATE TABLE [{0}] (", table.TableName)<br />
                    Columns.Append("(")<br />
                    Mark.Append("(")<br />
                    For Each Column As DataColumn In table.Columns<br />
                        CreateString.Append(OS(Column.ColumnName))<br />
                        Select Case Column.DataType.Name<br />
                            Case "SByte", "Byte", "Int16", "Int32", "Int64", "Decimal", "Double", "Single"<br />
                                CreateString.Append(" Number, ")<br />
                            Case "Boolean"<br />
                                CreateString.Append(" Bit, ")<br />
                            Case "Char", "String"<br />
                                CreateString.Append(" Memo, ")<br />
                            Case "DateTime"<br />
                                CreateString.Append(" DateTime, ")<br />
                            Case Else<br />
                                CreateString.Append(" Text, ")<br />
                        End Select<br />
                        Columns.AppendFormat("{0}, ", OS(Column.ColumnName))<br />
                        Mark.Append("?,")<br />
                    Next<br />
                    CreateString = CreateString.Remove(CreateString.Length - 2, 2)<br />
                    CreateString.Append(")")<br />
                    Columns = Columns.Remove(Columns.Length - 2, 2)<br />
                    Columns.Append(")")<br />
                    Mark = Mark.Remove(Mark.Length - 1, 1)<br />
                    Mark.Append(")")<br />
                    Using Command As New OleDb.OleDbCommand(CreateString.ToString, Connection)<br />
                        Command.ExecuteNonQuery()<br />
                    End Using<br />
                    Using Adapter As New OleDb.OleDbDataAdapter("SELECT * FROM [" & table.TableName & "$]", Connection)<br />
                        Using ExcelDataset As New DataSet<br />
                            Debug.WriteLine("ExcelDataset.Locale.Name = " & ExcelDataset.Locale.Name)<br />
                            Adapter.Fill(ExcelDataset, table.TableName)<br />
                            Adapter.InsertCommand = New OleDb.OleDbCommand("INSERT INTO [" & table.TableName & "] " & Columns.ToString & " VALUES " & Mark.ToString, Connection)<br />
                            For Each Column As DataColumn In table.Columns<br />
                                Select Case Column.DataType.Name<br />
                                    Case "SByte", "Byte", "Int16", "Int32", "Int64", "Double", "Single" ', "Decimal"<br />
                                        Adapter.InsertCommand.Parameters.Add("@" & OS(Column.ColumnName), OleDb.OleDbType.Numeric, 100, OS(Column.ColumnName))<br />
                                    Case "Decimal"<br />
                                        'BIG, BIG Warning about this one, Inserting a a double intead of a Decimal is done to fix a bug(?) in OleDB <br />
                                        'when using another Language setting than en-US on the computer (decimal comma vs. decimal dot)<br />
                                        'Changing Cultureinfo on the CurrentThread doesn't help<br />
<br />
                                        Adapter.InsertCommand.Parameters.Add("@" & OS(Column.ColumnName), OleDb.OleDbType.Double, 100, OS(Column.ColumnName))<br />
<br />
                                        'Here's my original to use with en-US if anyone is interested:<br />
                                        'Dim myParameter As New OleDb.OleDbParameter("@" & OS(Column.ColumnName), OleDb.OleDbType.Decimal, 100, OS(Column.ColumnName))<br />
                                        'myParameter.Precision = 8<br />
                                        'myParameter.Scale = 4<br />
                                        'Adapter.InsertCommand.Parameters.Add(myParameter)<br />
<br />
                                    Case "Boolean"<br />
                                        Adapter.InsertCommand.Parameters.Add("@" & OS(Column.ColumnName), OleDb.OleDbType.Boolean, 100, OS(Column.ColumnName))<br />
                                    Case "Char", "String"<br />
                                        Adapter.InsertCommand.Parameters.Add("@" & OS(Column.ColumnName), OleDb.OleDbType.Char, 65536, OS(Column.ColumnName))<br />
                                    Case "DateTime"<br />
                                        Adapter.InsertCommand.Parameters.Add("@" & OS(Column.ColumnName), OleDb.OleDbType.DBTimeStamp, 100, OS(Column.ColumnName))<br />
                                    Case Else<br />
                                        Adapter.InsertCommand.Parameters.Add("@" & OS(Column.ColumnName), OleDb.OleDbType.Char, 65536, OS(Column.ColumnName))<br />
                                End Select<br />
                            Next<br />
                            For Each Row As DataRow In table.Rows<br />
                                If Row.RowState <> DataRowState.Deleted Then<br />
                                    Dim ExcelRow As DataRow = ExcelDataset.Tables(table.TableName).NewRow<br />
                                    For i As Integer = 0 To table.Columns.Count - 1<br />
                                        ExcelRow.Item(i) = Row.Item(i)<br />
                                    Next<br />
                                    ExcelDataset.Tables(table.TableName).Rows.Add(ExcelRow)<br />
                                End If<br />
                            Next<br />
                            Adapter.Update(ExcelDataset, table.TableName)<br />
                        End Using<br />
                    End Using<br />
                Next<br />
            End Using<br />
            System.GC.Collect()<br />
        End Sub