how find list of all user in sql database

http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/thread/e6cfeeae-bd09-45af-b657-2392af0cf41a/

Export complete database to excel file

Introduction

First of all, I need to give some credit to Marc Brooks for publishing code which made it possible for me to make my code about a hundred times faster as it originaly was!


What does this code do:
As the title says, this code is capable of extracting all tables and it's data from any given SQL database and export it to Excel! Every table gets it's own worksheet. I was searching the net for a program like this, but I didn't come accross any (free) versions. So I decided to write it myself.
Using the code

To get this code to work, you need to add a reference to Excel.dll by using Add Reference on the project and selecting Microsoft Excel 9.0 (or 10.0) Object Library from the COM tab on the Add Reference dialog.

And then import the following namespace:
Collapse

Imports System.Runtime.InteropServices.Marshal

Now add the following class to your project:
Collapse

Private Sub create(ByVal sDatabaseName As String)
Dim dsTables As DataSet = New DataSet

'Get all Tables from database
dsTables = getAllTables(sDatabaseName)
'Create Excel Application, Workbook, and WorkSheets
Dim xlExcel As New Excel.Application
Dim xlBooks As Excel.Workbooks
Dim xlBook As Excel.Workbook
Dim tblSheet As Excel.Worksheet
Dim xlCells As Excel.Range
Dim sFile As String
'File name for the excel files
File = Server.MapPath(sDatabaseName & "_data.xls")
xlExcel.Visible = False : xlExcel.DisplayAlerts = False
xlBooks = xlExcel.Workbooks
xlBook = xlBooks.Add


For i As Integer = 0 To dsTables.Tables.Count - 1
tblSheet = xlBook.Worksheets.Add
tblSheet.Name = dsTables.Tables(i).TableName
xlCells = tblSheet.Cells
For iCol As Integer = 0 To dsTables.Tables(i).Columns.Count - 1
xlCells(1, iCol + 1) = dsTables.Tables(i).Columns(iCol).ToString
xlCells(1).EntireRow.Font.Bold = True
Next
If dsTables.Tables(i).Rows.Count > 0 Then
'With ConvertToRecordset the datatable is converted to a recordset
'Then with CopyFromRecordset the entire recordset can be inserted at once
tblSheet.Range("A2").CopyFromRecordset(ConvertToRecordset(dsTables.Tables(i)))
End If
xlCells.Columns.AutoFit()
Next

'Remove initial excel sheets. Within a try catch because the database
'could be empty (a workbook without worksheets is not allowed)
Try
Dim SheetCount As Integer = xlExcel.Sheets.Count
CType(xlExcel.Sheets(SheetCount - 0), Excel.Worksheet).Delete()
CType(xlExcel.Sheets(SheetCount - 1), Excel.Worksheet).Delete()
CType(xlExcel.Sheets(SheetCount - 2), Excel.Worksheet).Delete()
Catch ex As Exception
End Try

'Save the excel file
xlBook.SaveAs(sFile)

'Make sure all objects are disposed
xlBook.Close()
xlExcel.Quit()
ReleaseComObject(xlCells)
ReleaseComObject(tblSheet)
ReleaseComObject(xlBook)
ReleaseComObject(xlBooks)
ReleaseComObject(xlExcel)
xlExcel = Nothing
xlBooks = Nothing
xlBook = Nothing
tblSheet = Nothing
xlCells = Nothing

'Let the Garbage Collector know it can get to work
GC.Collect()

'Export Excel for download
Try
HttpContext.Current.Response.ContentType = "application/octet-stream"
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + System.IO.Path.GetFileName(sFile))
HttpContext.Current.Response.Clear()
HttpContext.Current.Response.WriteFile(sFile)
Catch ex As Exception
End Try
End Sub


To convert the DataTable to a recordset, the following two classes are used:
Collapse

Private Shared Function ConvertToRecordset(ByVal inTable As DataTable) As ADODB.Recordset
Dim result As New ADODB.Recordset()
result.CursorLocation = ADODB.CursorLocationEnum.adUseClient
Dim resultFields As ADODB.Fields = result.Fields
Dim inColumns As System.Data.DataColumnCollection = inTable.Columns

For Each inColumn As DataColumn In inColumns
resultFields.Append(inColumn.ColumnName, TranslateType(inColumn.DataType), inColumn.MaxLength, IIf(inColumn.AllowDBNull, _
ADODB.FieldAttributeEnum.adFldIsNullable, ADODB.FieldAttributeEnum.adFldUnspecified), Nothing)
Next

result.Open(System.Reflection.Missing.Value, System.Reflection.Missing.Value, ADODB.CursorTypeEnum.adOpenStatic, _
ADODB.LockTypeEnum.adLockOptimistic, 0)

For Each dr As DataRow In inTable.Rows
result.AddNew(System.Reflection.Missing.Value, System.Reflection.Missing.Value)
For columnIndex As Integer = 0 To inColumns.Count - 1
resultFields(columnIndex).Value = dr(columnIndex)
Next
Next
Return result
End Function



Private Shared Function TranslateType(ByVal columnType As Type) As ADODB.DataTypeEnum
Select Case columnType.UnderlyingSystemType.ToString()
Case "System.Boolean"
Return ADODB.DataTypeEnum.adBoolean
Case "System.Byte"
Return ADODB.DataTypeEnum.adUnsignedTinyInt
Case "System.Char"
Return ADODB.DataTypeEnum.adChar
Case "System.DateTime"
Return ADODB.DataTypeEnum.adDate
Case "System.Decimal"
Return ADODB.DataTypeEnum.adCurrency
Case "System.Double"
Return ADODB.DataTypeEnum.adDouble
Case "System.Int16"
Return ADODB.DataTypeEnum.adSmallInt
Case "System.Int32"
Return ADODB.DataTypeEnum.adInteger
Case "System.Int64"
Return ADODB.DataTypeEnum.adBigInt
Case "System.SByte"
Return ADODB.DataTypeEnum.adTinyInt
Case "System.Single"
Return ADODB.DataTypeEnum.adSingle
Case "System.UInt16"
Return ADODB.DataTypeEnum.adUnsignedSmallInt
Case "System.UInt32"
Return ADODB.DataTypeEnum.adUnsignedInt
Case "System.UInt64"
Return ADODB.DataTypeEnum.adUnsignedBigInt
Case "System.String"
Return ADODB.DataTypeEnum.adVarChar
Case Else
Return ADODB.DataTypeEnum.adVarChar
End Select
End Function


And now the trick to getting all tables and data from a database:
Collapse

Public database as String


Public ReadOnly Property getAllTables(ByVal sDB As String) As DataSet
Get
database = sDB
Dim m_dshelp As DataSet = New DataSet
getRequestedAllTables(m_dshelp)
Return m_dshelp
End Get
End Property


Private Function getRequestedAllTables(ByRef p_dataset As DataSet) As Boolean
'Retrieve all tablenames from the database:
Dim sSQL As String
Dim dsTables As DataSet = New DataSet

sSQL = "SELECT [TableName] = so.name, [RowCount] = MAX(si.rows) " & _
"FROM sysobjects so, sysindexes si " & _
"WHERE so.xtype = 'U' AND si.id = OBJECT_ID(so.name) AND si.rows > 0 " & _
"GROUP BY so.name " & _
"ORDER BY 2 DESC"

getData(sSQL, "Tables", dsTables)
'Loop thrue all tables and do a SELECT *. Then add them to the dataset
For i As Integer = 0 To dsTables.Tables(0).Rows.Count - 1
sSQL = "SELECT * FROM " & dsTables.Tables(0).Rows(i).Item(0)
getData(sSQL, dsTables.Tables(0).Rows(i).Item(0), p_dataset)
Next
End Function


Private Function getData(ByVal p_sql As String, ByVal p_table As String, ByRef pdataset As DataSet) As Boolean
Dim objDataAdapter As SqlDataAdapter
Dim objcommand As SqlCommand
objcommand = New SqlCommand(p_sql, getConnection)
objDataAdapter = New SqlDataAdapter(objcommand)
objDataAdapter.Fill(pdataset, p_table)
End Function


Private Function getConnection() As SqlConnection
If (ConfigurationManager.AppSettings("SQLPW") <> "") Then
getConnection = New SqlConnection("Server=" & _
ConfigurationManager.AppSettings("SQLserver") & ";password=" & _
ConfigurationManager.AppSettings("SQLPW") & "; user=" & _
ConfigurationManager.AppSettings("SQLUser") & ";database=" & database)
Else
getConnection = New SqlConnection("Data Source=" & _
ConfigurationManager.AppSettings("SQLserver") & ";Initial Catalog=" & _
database & ";Integrated Security=True")
End If
End Function

link:
http://www.codeproject.com/KB/vb/exportdatabasetoexcel.aspx

datagridviewheadercheckboxcolumn_SELECTALL

Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.Drawing

Imports System.Text
Imports System.Windows.Forms

Namespace SelectAll
Public Partial Class frmSelectAll
Inherits Form
Private TotalCheckBoxes As Integer = 0
Private TotalCheckedCheckBoxes As Integer = 0
Private HeaderCheckBox As CheckBox = Nothing
Private IsHeaderCheckBoxClicked As Boolean = False

Public Sub New()
InitializeComponent()
End Sub

Private Sub frmSelectAll_Load(ByVal sender As Object, ByVal e As EventArgs)
AddHeaderCheckBox()

AddHandler HeaderCheckBox.KeyUp, AddressOf HeaderCheckBox_KeyUp
AddHandler HeaderCheckBox.MouseClick, AddressOf HeaderCheckBox_MouseClick
AddHandler dgvSelectAll.CellValueChanged, AddressOf dgvSelectAll_CellValueChanged
AddHandler dgvSelectAll.CurrentCellDirtyStateChanged, AddressOf dgvSelectAll_CurrentCellDirtyStateChanged
AddHandler dgvSelectAll.CellPainting, AddressOf dgvSelectAll_CellPainting

BindGridView()
End Sub

Private Sub BindGridView()
dgvSelectAll.DataSource = GetDataSource()

TotalCheckBoxes = dgvSelectAll.RowCount
TotalCheckedCheckBoxes = 0
End Sub

Private Function GetDataSource() As DataTable
Dim dTable As New DataTable()

Dim dRow As DataRow = Nothing
Dim dTime As DateTime
Dim rnd As New Random()

dTable.Columns.Add("IsChecked", System.Type.[GetType]("System.Boolean"))
dTable.Columns.Add("RandomNo")
dTable.Columns.Add("Date")
dTable.Columns.Add("Time")

For n As Integer = 0 To 9
dRow = dTable.NewRow()
dTime = DateTime.Now

dRow("IsChecked") = "false"
dRow("RandomNo") = rnd.NextDouble()
dRow("Date") = dTime.ToString("MM/dd/yyyy")
dRow("Time") = dTime.ToString("hh:mm:ss tt")

dTable.Rows.Add(dRow)
dTable.AcceptChanges()
Next

Return dTable
End Function

Private Sub dgvSelectAll_CellValueChanged(ByVal sender As Object, ByVal e As DataGridViewCellEventArgs)
If Not IsHeaderCheckBoxClicked Then
RowCheckBoxClick(DirectCast(dgvSelectAll(e.ColumnIndex, e.RowIndex), DataGridViewCheckBoxCell))
End If
End Sub

Private Sub dgvSelectAll_CurrentCellDirtyStateChanged(ByVal sender As Object, ByVal e As EventArgs)
If TypeOf dgvSelectAll.CurrentCell Is DataGridViewCheckBoxCell Then
dgvSelectAll.CommitEdit(DataGridViewDataErrorContexts.Commit)
End If
End Sub

Private Sub HeaderCheckBox_MouseClick(ByVal sender As Object, ByVal e As MouseEventArgs)
HeaderCheckBoxClick(DirectCast(sender, CheckBox))
End Sub

Private Sub HeaderCheckBox_KeyUp(ByVal sender As Object, ByVal e As KeyEventArgs)
If e.KeyCode = Keys.Space Then
HeaderCheckBoxClick(DirectCast(sender, CheckBox))
End If
End Sub

Private Sub dgvSelectAll_CellPainting(ByVal sender As Object, ByVal e As DataGridViewCellPaintingEventArgs)
If e.RowIndex = -1 AndAlso e.ColumnIndex = 0 Then
ResetHeaderCheckBoxLocation(e.ColumnIndex, e.RowIndex)
End If
End Sub

Private Sub AddHeaderCheckBox()
HeaderCheckBox = New CheckBox()

HeaderCheckBox.Size = New Size(15, 15)

'Add the CheckBox into the DataGridView
Me.dgvSelectAll.Controls.Add(HeaderCheckBox)
End Sub

Private Sub ResetHeaderCheckBoxLocation(ByVal ColumnIndex As Integer, ByVal RowIndex As Integer)
'Get the column header cell bounds
Dim oRectangle As Rectangle = Me.dgvSelectAll.GetCellDisplayRectangle(ColumnIndex, RowIndex, True)

Dim oPoint As New Point()

oPoint.X = oRectangle.Location.X + (oRectangle.Width - HeaderCheckBox.Width) / 2 + 1
oPoint.Y = oRectangle.Location.Y + (oRectangle.Height - HeaderCheckBox.Height) / 2 + 1

'Change the location of the CheckBox to make it stay on the header
HeaderCheckBox.Location = oPoint
End Sub

Private Sub HeaderCheckBoxClick(ByVal HCheckBox As CheckBox)
IsHeaderCheckBoxClicked = True

For Each Row As DataGridViewRow In dgvSelectAll.Rows
DirectCast(Row.Cells("chkBxSelect"), DataGridViewCheckBoxCell).Value = HCheckBox.Checked
Next

dgvSelectAll.RefreshEdit()

TotalCheckedCheckBoxes = If(HCheckBox.Checked, TotalCheckBoxes, 0)

IsHeaderCheckBoxClicked = False
End Sub

Private Sub RowCheckBoxClick(ByVal RCheckBox As DataGridViewCheckBoxCell)
If RCheckBox IsNot Nothing Then
'Modifiy Counter;
If CBool(RCheckBox.Value) AndAlso TotalCheckedCheckBoxes < TotalCheckBoxes Then
TotalCheckedCheckBoxes += 1
ElseIf TotalCheckedCheckBoxes > 0 Then
TotalCheckedCheckBoxes -= 1
End If

'Change state of the header CheckBox.
If TotalCheckedCheckBoxes < TotalCheckBoxes Then
HeaderCheckBox.Checked = False
ElseIf TotalCheckedCheckBoxes = TotalCheckBoxes Then
HeaderCheckBox.Checked = True
End If
End If
End Sub
End Class
End Namespace

Dynamic Cell Formatting

FORM_LOAD


For i As Integer = 0 To Me.DataGridView1.Columns.Count - 1
Me.DataGridView1.Columns(i).Name = Me.DataGridView1.Columns(i).DataPropertyName
Next


Private Sub dataGridView1_CellFormatting(ByVal sender As Object, _
ByVal e As DataGridViewCellFormattingEventArgs) _
Handles DataGridView1.CellFormatting
If Me.DataGridView1.Columns(e.ColumnIndex).Name = "SHORT_NAME" Then

If e.Value IsNot Nothing Then
' If the value of the cell is "Inactive" AND this form's inactiveCustomersCheckBox control is checked..
If e.Value.ToString = "LHH" Then
' Set the BackColor of the cell to yellow.

e.CellStyle.BackColor = Color.Red
ElseIf e.Value.ToString = "LLH" Then

e.CellStyle.BackColor = Color.Yellow
ElseIf e.Value.ToString = "LLO" Then
e.CellStyle.BackColor = Color.Red
Else
e.CellStyle.BackColor = Color.White
End If
End If
End If

End Sub

link:

"http://www.devx.com/dotnet/Article/34845/0/page/3

Realted Links:

www.devx.com/dotnet/Article/33748

datagridview reorder rows(drag drop) and possible using defualt control

The bellow dragdropgrid is useful for reorder of rows

working: This can be work well for without databound to dragdropgrid

problems: This is work for forword direction only when Dragdropgrid using databounds(bindingsource,dataset)





http://dragdropgrid.codeplex.com/