Create entities by using MS Sql in asp.net

July 25, 2013

Hello,

Sometime it is very difficult stuff to create entities for each table.

Here is a stored procedure,where we have to pass just table name only and it will automatically create entities(output as a string message)  as an output.

I’ve table Database named MyTest,which is containing table dbo.tblSLtest which structure is like a

structure

For create the entities for this table execute this stored procedure

DECLARE @TableName VARCHAR(MAX) = ‘tblSLtest’
DECLARE @TableSchema VARCHAR(MAX) = ‘dbo’
DECLARE @result varchar(max) = ”

SET @result = @result + ‘using System;’ + CHAR(13) + CHAR(13)

IF (@TableSchema IS NOT NULL)
BEGIN
SET @result = @result + ‘namespace ‘ + @TableSchema  + CHAR(13) + ‘{‘ + CHAR(13)
END

SET @result = @result + ‘public class ‘ + @TableName + CHAR(13) + ‘{‘ + CHAR(13)

SET @result = @result + ‘#region Instance Properties’ + CHAR(13)

SELECT @result = @result + CHAR(13)
+ ‘ public ‘ + ColumnType + ‘ ‘ + ColumnName + ‘ { get; set; } ‘ + CHAR(13)
FROM
(
SELECT  c.COLUMN_NAME   AS ColumnName
, CASE c.DATA_TYPE
WHEN ‘bigint’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘Int64?’ ELSE ‘Int64’ END
WHEN ‘binary’ THEN ‘Byte[]’
WHEN ‘bit’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘Boolean?’ ELSE ‘Boolean’ END
WHEN ‘char’ THEN ‘String’
WHEN ‘date’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘DateTime?’ ELSE ‘DateTime’ END
WHEN ‘datetime’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘DateTime?’ ELSE ‘DateTime’ END
WHEN ‘datetime2’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘DateTime?’ ELSE ‘DateTime’ END
WHEN ‘datetimeoffset’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘DateTimeOffset?’ ELSE ‘DateTimeOffset’ END
WHEN ‘decimal’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘Decimal?’ ELSE ‘Decimal’ END
WHEN ‘float’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘Single?’ ELSE ‘Single’ END
WHEN ‘image’ THEN ‘Byte[]’
WHEN ‘int’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘Int32?’ ELSE ‘Int32’ END
WHEN ‘money’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘Decimal?’ ELSE ‘Decimal’ END
WHEN ‘nchar’ THEN ‘String’
WHEN ‘ntext’ THEN ‘String’
WHEN ‘numeric’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘Decimal?’ ELSE ‘Decimal’ END
WHEN ‘nvarchar’ THEN ‘String’
WHEN ‘real’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘Double?’ ELSE ‘Double’ END
WHEN ‘smalldatetime’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘DateTime?’ ELSE ‘DateTime’ END
WHEN ‘smallint’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘Int16?’ ELSE ‘Int16’END
WHEN ‘smallmoney’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘Decimal?’ ELSE ‘Decimal’ END
WHEN ‘text’ THEN ‘String’
WHEN ‘time’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘TimeSpan?’ ELSE ‘TimeSpan’ END
WHEN ‘timestamp’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘DateTime?’ ELSE ‘DateTime’ END
WHEN ‘tinyint’ THEN
CASE C.IS_NULLABLE
WHEN ‘YES’ THEN ‘Byte?’ ELSE ‘Byte’ END
WHEN ‘uniqueidentifier’ THEN ‘Guid’
WHEN ‘varbinary’ THEN ‘Byte[]’
WHEN ‘varchar’ THEN ‘String’
ELSE ‘Object’
END AS ColumnType
, c.ORDINAL_POSITION
FROM    INFORMATION_SCHEMA.COLUMNS c
WHERE   c.TABLE_NAME = @TableName and ISNULL(@TableSchema, c.TABLE_SCHEMA) = c.TABLE_SCHEMA
) t
ORDER BY t.ORDINAL_POSITION

SET @result = @result + CHAR(13) + ‘#endregion Instance Properties’ + CHAR(13)

SET @result = @result  + ‘}’ + CHAR(13)

IF (@TableSchema IS NOT NULL)
BEGIN
SET @result = @result + CHAR(13) + ‘}’
END

PRINT @result

and you we have output like(with corresponding datatypes)

output

So now enjoy with entities…


Get day count for particular month using MS Sql

July 24, 2013

Hello,

By using below code you can find the day count-number of days in particular month

So here it is,

DECLARE @dtDate DATETIME
SET @dtDate = ‘8/18/2007’

select datediff(day, dateadd(day, 1-day(@dtDate), @dtDate),
dateadd(month, 1, dateadd(day, 1-day(@dtDate), @dtDate)))  as dayCount

you will get output like,

Capture

Here august month passed in dtDate parameter of sql so will have an output 31 day count.


Export datatable to excel

July 10, 2013

Using this code you can export data from datatable to excel

Create data table first then pass it to ExporttoExcel method   and you’ll get desired excel file for download.

protected void btnExport_Click(object sender, EventArgs e)
{
DataTable dt = CreateDataTable();
ExporttoExcel(dt);
}
private void ExporttoExcel(DataTable table)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.Write(@"<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">");
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=TestingReports.xls");
HttpContext.Current.Response.Charset = "utf-8";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250");
//sets font
HttpContext.Current.Response.Write("<font style='font-size:10.0pt; font-family:Calibri;'>");
HttpContext.Current.Response.Write("<BR><BR><BR>");
//sets the table border, cell spacing, border color, font of the text, background, foreground, font height
HttpContext.Current.Response.Write("<Table border='1' bgColor='#ffffff' " +
"borderColor='#000000' cellSpacing='0' cellPadding='0' " +
"style='font-size:10.0pt; font-family:Calibri; background:white;'> <TR>");
//am getting my grid's column headers
//int columnscount = GridView_Result.Columns.Count;

//for (int j = 0; j < columnscount; j++)
//{      //write in new column
//    HttpContext.Current.Response.Write("<Td>");
//    //Get column headers  and make it as bold in excel columns
//    HttpContext.Current.Response.Write("<B>");
//    HttpContext.Current.Response.Write(GridView_Result.Columns[j].HeaderText.ToString());
//    HttpContext.Current.Response.Write("</B>");
//    HttpContext.Current.Response.Write("</Td>");
//}

for (int j = 0; j < table.Columns.Count; j++)
{      //write in new column
HttpContext.Current.Response.Write("<Td>");
//Get column headers  and make it as bold in excel columns
HttpContext.Current.Response.Write("<B>");
HttpContext.Current.Response.Write(table.Columns[j].ColumnName.ToString());
HttpContext.Current.Response.Write("</B>");
HttpContext.Current.Response.Write("</Td>");
}

HttpContext.Current.Response.Write("</TR>");
foreach (DataRow row in table.Rows)
{//write in new row
HttpContext.Current.Response.Write("<TR>");
for (int i = 0; i < table.Columns.Count; i++)
{
HttpContext.Current.Response.Write("<Td>");
HttpContext.Current.Response.Write(row[i].ToString());
HttpContext.Current.Response.Write("</Td>");
}

HttpContext.Current.Response.Write("</TR>");
}
HttpContext.Current.Response.Write("</Table>");
HttpContext.Current.Response.Write("</font>");
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
}

private  DataTable CreateDataTable()
{
DataTable companies = new DataTable("Companies");
DataColumn column;

column = new DataColumn("CompanyName");
companies.Columns.Add(column);

column = new DataColumn("CompanyCountry");
companies.Columns.Add(column);

column = new DataColumn("YearSales");
companies.Columns.Add(column);

column = new DataColumn("GrossProfit");
companies.Columns.Add(column);

column = new DataColumn("CreationDate");
companies.Columns.Add(column);

companies.Rows.Add("Mexican Tequila", "Mexico", "10875600", "4975200", "03/24/1995 2:35:00PM");
companies.Rows.Add("Canadian Food", "Canada", "308450870", "103476200", "08/12/1983");
companies.Rows.Add("French Wines", "France", "285309567", "81650000", "11/21/1990 17:40:00");
companies.Rows.Add("German Beer", "Germany", "285309567", "81650000", "11/21/1990 17:40:00");
companies.Rows.Add("Russian Vodka", "Russia", "285309567", "81650000", "11/21/1990 17:40:00");
companies.Rows.Add("Spanish Paella", "Spain", "285309567", "81650000", "11/21/1990 17:40:00");
companies.Rows.Add("Argentinian Beef", "Argentina", "285309567", "81650000", "11/21/1990 17:40:00");
companies.Rows.Add("Italian Pasta", "Italy", "285309567", "81650000", "11/21/1990 17:40:00");

return companies;
}