How to get list of sql server database objects?

Problem: How to get list of database objects?

 Solution: You can get list of objects in two ways.

1. Getting the list from sys.objects table. Ex:
 
List of tables:

SELECT *
FROM sys.objects where type='U'

List of stored procedures:

SELECT *
FROM sys.objects where type='P'

List of views:

SELECT *
FROM sys.objects where type='V'


2. Getting the list query from object related tables. Ex:


List of tables:


SELECT *
FROM sys.Tables


List of stored procedures:


SELECT *
FROM sys.procedures


List of views:


SELECT *
FROM sys.views

How to remove all data from table and reseed identity column to initial value?

Problem: How to remove all data from table and reseed identity column to initial value?

 Solution: For this purpose best solution is to use truncate sql command when you want to delete all data and reset identity column value to initial value.
Ex. TRUNCATE TABLE tableName

Somebody uses the following command which results same output:

DELETE FROM tableName
DBCC CHECKIDENT (tableName,RESEED, 0)

New blog on Ext.Net

I have started writing on Ext.Net in a new blog(http://practicalextnet.blogspot.com/) that will be dedicated to Ext.Net technologies. Now a day I am working on Ext.Net and developing all my web application with highly used Ext.Net. I have faced many problems while started working. So I would like to share my knowledge and finding with ext.net developers on the community so that they get help in their work. Hope this will be helpful.

Thanks

Display Gridpanel cell tooltip in Ext.Net

You often want to display tooltip using cell data when data length overflows column width in your gridpanel. You will get an example to show tooltip with gridpanel cell data here: http://examples1.ext.net/#/Miscellaneous/ToolTips/GridPanel_Cell_Tooltip/

But this is not working properly when it is included in some project especially if you have master page based design. I have tried this in my project and it worked properly with little bit changes. Here I want to share the codes with you guys:

<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="Server">

    <link href="../Style/examples.css" rel="stylesheet" type="text/css" />

    <ext:XScript ID="XScript1" runat="server">

        <script type="text/javascript">

            var showTip = function () {

                var rowIndex = #{gpList}.view.findRowIndex(this.triggerElement),

                cellIndex = #{gpList}.view.findCellIndex(this.triggerElement),

                record = #{gpListStore}.getAt(rowIndex),

                fieldName = #{gpList}.getColumnModel().getDataIndex(cellIndex),

               

                data = record.get(fieldName);

                if (data ==null){

                this.body.dom.innerHTML = "";

                }

                else{

                this.body.dom.innerHTML = data;

                };

            };

        </script>

    </ext:XScript>

</asp:Content>

<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server">

    <ext:ResourceManager ID="rcm" runat="server">

    </ext:ResourceManager>

    <ext:Hidden ID="hfPK" runat="server">

    </ext:Hidden>

    <ext:TabPanel ID="tPnlMain" runat="server" ActiveTabIndex="0" AnchorHorizontal="100%"

        Height="500" Plain="true" Visible="true" Width="1024">

        <Items>

<ext:Panel ID="Tab2" runat="server" Title="Employee Search" Padding="1" AutoScroll="true">

                <Items>

                    <ext:RowLayout runat="server">

                        <Rows>

                            <ext:LayoutRow RowHeight=".25">

                                <ext:Panel ID="Panel1" runat="server" Header="false" Padding="1" AutoScroll="true">

                                    <Items>

                                    </Items>

                                </ext:Panel>

                            </ext:LayoutRow>

                            <ext:LayoutRow RowHeight=".75">

                                <ext:Panel ID="Panel3" runat="server" Title="Employee List" Padding="1" AutoScroll="true">

                                    <Items>

                                        <ext:GridPanel ID="gpList" runat="server" StripeRows="true" AutoExpandColumn="EmployeeName"

                                            Collapsible="true" AnchorHorizontal="100%" Height="322" TrackMouseOver="true">

                                            <Store>

                                                <ext:Store ID="gpListStore" runat="server">

                                                    <Reader>

                                                        <ext:JsonReader IDProperty="EmployeeId">

                                                            <Fields>

                                                                <ext:RecordField Name="EmployeeId" />

                                                                <ext:RecordField Name="EmployeeName" />

                                                                <ext:RecordField Name="EnrollId" />

                                                                <ext:RecordField Name="FirstName" />

                                                                <ext:RecordField Name="MiddleName" />

                                                                <ext:RecordField Name="LastName" />

                                                                <ext:RecordField Name="AddressLine1" />

                                                                <ext:RecordField Name="DepartmentId" />

                                                                <ext:RecordField Name="DesignationId" />

                                                                <ext:RecordField Name="PolicyId" />

                                                                <ext:RecordField Name="CardNumber" />

                                                                <ext:RecordField Name="DesignationName" />

                                                                <ext:RecordField Name="DeptName" />

                                                                <ext:RecordField Name="SbuName" />

                                                            </Fields>

                                                        </ext:JsonReader>

                                                    </Reader>

                                                </ext:Store>

                                            </Store>

                                            <ColumnModel ID="ColumnModel1" runat="server">

                                                <Columns>

                                                    <ext:RowNumbererColumn />

                                                    <ext:CommandColumn Header="Action" Width="65">

                                                        <Commands>

                                                            <ext:GridCommand Icon="ApplicationEdit" CommandName="Edit">

                                                                <ToolTip Text="Edit" />

                                                            </ext:GridCommand>

                                                        </Commands>

                                                    </ext:CommandColumn>

                                                    <ext:Column ColumnID="cEnrollId" Header="Enroll Id" DataIndex="EnrollId" Width="50" />

                                                    <ext:Column ColumnID="cStaffCode" Header="Employee Code" DataIndex="StaffCode" Width="70" />

                                                    <ext:Column ColumnID="cEmployeeName" Header="EmployeeName" DataIndex="EmployeeName"

                                                        Width="130" />

                                                    <ext:Column ColumnID="cDesignationName" Header="Designation" DataIndex="DesignationName"

                                                        Width="150" />

                                                    <ext:Column ColumnID="cDepartmentName" Header="Department" DataIndex="DeptName" Width="100" />

                                                    <ext:Column ColumnID="cSbuId" Header="Branch" DataIndex="SbuName" />

                                                    <ext:Column ColumnID="cAddressLine1" Header="Address" DataIndex="AddressLine1" Width="180" />

                                                </Columns>

                                            </ColumnModel>

                                            <SelectionModel>

                                                <ext:RowSelectionModel ID="RowSelectionModel1" runat="server" SingleSelect="true" />

                                            </SelectionModel>

                                            <BottomBar>

                                                <ext:PagingToolbar ID="gpListPager" runat="server" PageSize="20" />

                                            </BottomBar>

                                            <Listeners>

                                                <Command Handler="Ext.net.DirectMethods.ExecuteActionCommand(command, record.data.EmployeeId, record.data.EnrollId);" />

                                            </Listeners>

                                            <ToolTips>

                                                <ext:ToolTip ID="RowTip" runat="server" Target="#{gpList}.getView().mainBody" Delegate=".x-grid3-cell" ShowDelay="0"

                                                    TrackMouse="true">

                                                    <Listeners>

                                                        <Show Fn="showTip" />

                                                    </Listeners>

                                                </ext:ToolTip>

                                            </ToolTips>

                                        </ext:GridPanel>

                                    </Items>

                                </ext:Panel>

                            </ext:LayoutRow>

                        </Rows>

                    </ext:RowLayout>

                </Items>

            </ext:Panel>

        </Items>

    </ext:TabPanel>

</asp:Content>

Hope this will help my friends.

How to export data to excel?

Recently, I have worked on export data to excel and import data from excel. I have face many problems while working on this. So I have searched for different ways for doing export and import from excel. I would like to share with you guys different export methods so that you can easily try it.

Way 1: using Open XML when working with Office 2007 or later version

protected void ExportToExcel(DataTable dt, List<string> columnNames, string fileName)
    {
        MemoryStream stream = DocumentFormat.OpenXml.Extensions.SpreadsheetReader.Create();
        DocumentFormat.OpenXml.Packaging.SpreadsheetDocument doc = DocumentFormat.OpenXml.Packaging.SpreadsheetDocument.Open(stream, true);
        DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart = DocumentFormat.OpenXml.Extensions.SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
        DocumentFormat.OpenXml.Extensions.WorksheetWriter writer = new DocumentFormat.OpenXml.Extensions.WorksheetWriter(doc, worksheetPart);

        //writer.PasteText("B2", "Hello World");
        DataTable dtc = new DataTable();
        for (int i = 0; i < columnNames.Count; i++)
        {
            dtc.Columns.Add(new DataColumn(columnNames[i]));
        }
        DataRow dr = dt.NewRow();
        for (int i = 0; i < columnNames.Count; i++)
        {
            dr[i] = columnNames[i];
        }
        dt.Rows.InsertAt(dr, 0);
        writer.InsertDataTable(dt, "A1", columnNames);
        //writer.PasteDataTable(dtc, "A1");
        //Save to the memory stream
        DocumentFormat.OpenXml.Extensions.SpreadsheetWriter.Save(doc);

        //Write to response stream
        this.Response.Clear();
        this.Response.AddHeader("content-disposition", String.Format("attachment;filename={0}", fileName));
        this.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

        stream.WriteTo(this.Response.OutputStream);
        this.Response.End();
    }

Way 2: working with office automation

protected void Exporttoexcel2(DataTable dt, string fileName)
    {
        off.Application excel = new off.Application();
        off.Workbook workbook = excel.Application.Workbooks.Add(true);
        int iCol = 0;
        foreach (DataColumn c in dt.Columns)
        {
            iCol++;
            excel.Cells[1, iCol] = c.ColumnName;
        }

        int iRow = 0;
        foreach (DataRow r in dt.Rows)
        {
            iRow++;
            iCol = 0;
            foreach (DataColumn c in dt.Columns)
            {
                iCol++;
                excel.Cells[iRow + 1, iCol] = r[c.ColumnName];
            }
        }

        string path = string.Empty;
        path = Server.MapPath("~/TempFiles/");
        path = path + fileName;

        object missing = System.Reflection.Missing.Value;
        workbook.SaveAs(path, off.XlFileFormat.xlXMLSpreadsheet, missing, missing, false, false, off.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
        excel.Visible = true;
        off.Worksheet worksheet = (off.Worksheet)excel.ActiveSheet;
        ((off._Worksheet)worksheet).Activate();
        ((off._Application)excel).Quit();

        this.Response.ContentType = "application/vnd.ms-excel";
        this.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName + "");
        this.Response.TransmitFile(path);
        this.Response.End();
    }

Way 3: working with ExcelPackage library

protected void Exporttoexcel3(DataTable dt, string fileName)
    {
        FileInfo newFile = new FileInfo(fileName);
        using (ExcelPackage xlPackage = new ExcelPackage(newFile))
        {
            xlPackage.DebugMode = true;
            ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add(fileName);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                worksheet.Cell(1, i + 1).Value = dt.Columns[i].ColumnName;
            }

            for (int r = 0; r < dt.Rows.Count; r++)
            {
                for (int i = 0; i < dt.Columns.Count; i++)
                {

                    worksheet.Cell(r + 2, i + 1).Value = Convert.ToString(dt.Rows[r][i]);
                }
            }
            xlPackage.Save();
        }
    }