Dotnet Programing: April 2014

Thursday, April 24, 2014

Check total number of tables, procedures in sql server

Execute the query to get the total count of table and stored procedures.

SELECT

CASE TYPE

WHEN 'U'

THEN 'User Defined Tables'

WHEN 'S'

THEN 'System Tables'

WHEN 'IT'

THEN 'Internal Tables'

WHEN 'P'

THEN 'Stored Procedures'

WHEN 'PC'

THEN 'CLR Stored Procedures'

WHEN 'X'

THEN 'Extended Stored Procedures'

END,

COUNT(*)

FROM SYS.OBJECTS

WHERE TYPE IN ('U', 'P', 'PC', 'S', 'IT', 'X')

GROUP BY TYPE

Wednesday, April 16, 2014

Export to Excel form C# code behind

1. create a gridview and fetch data into it and then

insert this code to button click

string FileName = DateTime.Now.ToString();

Response.Clear();

Response.AddHeader("content-disposition", "attachment;filename="+FileName+".xls");

Response.Charset = "";

// If you want the option to open the Excel file without saving then

// comment out the line below

// Response.Cache.SetCacheability(HttpCacheability.NoCache);

Response.ContentType = "application/vnd.xls";

System.IO.StringWriter stringWrite = new System.IO.StringWriter();

System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

gvExport.RenderControl(htmlWrite);

Response.Write(stringWrite.ToString());

Response.End();

in above code file name is given datetime.

Export Gridview or html table to Excel

1. Create javascript function

var tableToExcel = (function () {

var uri = 'data:application/vnd.ms-excel;base64,'

, template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head></head><body><table>{table}</table></body></html>'

, base64 = function (s) { return window.btoa(unescape(encodeURIComponent(s))) }

, format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) }

return function (table, name) {

if (!table.nodeType) table = document.getElementById(table)

var ctx = { worksheet: name || 'Worksheet', table: table.innerHTML }

window.location.href = uri + base64(format(template, ctx))

}

})()

</script>

2. Call function

Pages

Thursday, April 24, 2014

Check total number of tables, procedures in sql server

Wednesday, April 16, 2014

Export to Excel form C# code behind

Export Gridview or html table to Excel