Monday, January 7, 2013

the file you are trying to open is in a different format excel asp.net

Hi,
This code is for removing prompt on open excel.
For understanding this code copy paste in your c#, asp.net application and
download lib from following link:

http://code.google.com/p/excellibrary/downloads/list

Copy Paste code in your application:


      protected void Page_Load(object sender, System.EventArgs e)
{
//Create a new DataSet
DataSet ds = new DataSet();
ds.Tables.Add(GetUserJobs());
//Create excel file
bool result = WriteXLSFile("mahesh.xls", ds);
if (result == true) {
Response.Write("Write Excel file is successful");
} else {
Response.Write("Write Excel file is failed");
}
}
       public DataTable GetUserJobs()
       {
           var dtJob = new DataTable();
           dtJob.Columns.Add(new DataColumn("ID"));
           dtJob.Columns.Add(new DataColumn("Name"));
           dtJob.Columns.Add(new DataColumn("Designation"));
           dtJob.Columns.Add(new DataColumn("MonthlyIncome"));
           dtJob.Columns.Add(new DataColumn("Deductions"));

           var row1 = dtJob.NewRow();
           row1["ID"] = "1";
           row1["Name"] = "Sam Kumar";
           row1["Designation"] = "TL";
           row1["MonthlyIncome"] = "$3098";
           row1["Deductions"] = "$201";
           dtJob.Rows.Add(row1);

           row1 = dtJob.NewRow();
           row1["ID"] = "2";
           row1["Name"] = "Mahesh Kumar";
           row1["Designation"] = "Software Developer";
           row1["MonthlyIncome"] = "$5490";
           row1["Deductions"] = "$50";

           dtJob.Rows.Add(row1);

           row1 = dtJob.NewRow();
           row1["ID"] = "3";
           row1["Name"] = "Rakesh Kumar";
           row1["Designation"] = "Developer";
           row1["MonthlyIncome"] = "$2945";
           row1["Deductions"] = "$89";

           dtJob.Rows.Add(row1);

           row1 = dtJob.NewRow();
           row1["ID"] = "3";
           row1["Name"] = "Sunesh Kumar";
           row1["Designation"] = "Developer";
           row1["MonthlyIncome"] = "$5000";
           row1["Deductions"] = "$71";

           dtJob.Rows.Add(row1);
           row1 = dtJob.NewRow();
           row1["ID"] = "3";
           row1["Name"] = "Amit Kumar";
           row1["Designation"] = "Developer";
           row1["MonthlyIncome"] = "$1000";
           row1["Deductions"] = "$29";

           dtJob.Rows.Add(row1);
           row1 = dtJob.NewRow();
           row1["ID"] = "3";
           row1["Name"] = "Nabish Kumar";
           row1["Designation"] = "Developer";
           row1["MonthlyIncome"] = "$6198";
           row1["Deductions"] = "$60";

           dtJob.Rows.Add(row1);

           return dtJob;
       }
public bool WriteXLSFile(string pFileName, DataSet pDataSet)
{
try {
//This function CreateWorkbook will cause xls file cannot be opened
//normally when file size below 7Kb, see my work around below
//ExcelLibrary.DataSetHelper.CreateWorkbook(pFileName, pDataSet)

//Create a workbook instance
Workbook workbook = new Workbook();
Worksheet worksheet = default(Worksheet);
int iRow = 0;
int iCol = 0;
string sTemp = string.Empty;
double dTemp = 0;
int iTemp = 0;
DateTime dtTemp = default(DateTime);
int count = 0;
int iTotalRows = 0;
int iSheetCount = 0;

//Read DataSet

if ((pDataSet != null) & pDataSet.Tables.Count > 0) {
//Traverse DataTable inside the DataSet

foreach (DataTable dt in pDataSet.Tables) {
//Create a worksheet instance
iSheetCount = iSheetCount + 1;
worksheet = new Worksheet("Sheet " + iSheetCount.ToString());

//Write Table Header
foreach (DataColumn dc in dt.Columns) {
worksheet.Cells[iRow, iCol] = new Cell(dc.ColumnName);
iCol = iCol + 1;
}

//Write Table Body
iRow = 1;
foreach (DataRow dr in dt.Rows) {
iCol = 0;
foreach (DataColumn dc in dt.Columns) {
sTemp = dr[dc.ColumnName].ToString();
switch (dc.DataType.ToString()) {
case "date"://ToDo Need to fix
DateTime.TryParse(sTemp, out dtTemp);
worksheet.Cells[iRow, iCol] = new Cell(dtTemp, "MM/DD/YYYY");
break;
                                case "double"://ToDo Need to fix
double.TryParse(sTemp, out dTemp);
worksheet.Cells[iRow, iCol] = new Cell(dTemp, "#,##0.00");
break;
default:
worksheet.Cells[iRow, iCol] = new Cell(sTemp);
break;
}
iCol = iCol + 1;
}
iRow = iRow + 1;
}

//Attach worksheet to workbook
workbook.Worksheets.Add(worksheet);
iTotalRows = iTotalRows + iRow;
}
}

//Bug on Excel Library, min file size must be 7 Kb
//thus we need to add empty row for safety
if (iTotalRows < 100) {
worksheet = new Worksheet("Sheet X");
count = 1;
while (count < 100) {
worksheet.Cells[count, 0] = new Cell(" ");
count = count + 1;
}
workbook.Worksheets.Add(worksheet);
}
            //new code
            workbook.Save(Request.PhysicalApplicationPath+ pFileName);
            Response.ContentType = "application/vnd.ms-excel";
            Response.AppendHeader("Content-Disposition", "attachment; filename=sharma.xls");

            // Write the file to the Response
            const int bufferLength = 10000;
            byte[] buffer = new Byte[bufferLength];
            int length = 0;
            Stream download = null;
            try
            {
                download = new FileStream(Server.MapPath(pFileName),
                                                               FileMode.Open,
                                                               FileAccess.Read);
                do
                {
                    if (Response.IsClientConnected)
                    {
                        length = download.Read(buffer, 0, bufferLength);
                        Response.OutputStream.Write(buffer, 0, length);
                        buffer = new Byte[bufferLength];
                    }
                    else
                    {
                        length = -1;
                    }
                }
                while (length > 0);
                Response.Flush();
                Response.End();
                File.Delete(Server.MapPath(pFileName));
            }
            finally
            {
                if (download != null)
                    download.Close();
            }
            //end new code
//workbook.Save(pFileName);
return true;
} catch (Exception ex) {
return false;
}
}

Friday, January 4, 2013

How to get list of last updated stored procedures


How to get last updated Stored procedure in database: 
Run following command.


SELECT name,modify_date
FROM sys.objects
WHERE type = 'P' order by modify_date desc


AND DATEDIFF(D,modify_date, GETDATE()) < 5







How to get open connection in SQL Server for each database.


SELECT
    DB_NAME(dbid) as DBName,
    COUNT(dbid) as NumberOfConnections,
    loginame as LoginName
FROM
    sys.sysprocesses
WHERE
    dbid > 0
GROUP BY
    dbid, loginame