How to change date format at SQL Server?

How to change date format at MS SQL Server?

SET DATEFORMAT DMY


Note: DMY means Day-Month-Year, so you can use MDY or YDM, etc...

SQL Server stored procedure parameters decrease performance

When we use parametrs directly in query's WHERE parts,
performance of query decrease dramatically.

So we can use local values to copy these parametrs.

1 ALTER PROCEDURE [dbo].[CalculateSomething]
2     @myID INT
3 AS
4 BEGIN
5 DECLARE @myIDLocal INT
6 
7 SET @myIDLocal = @myID -- If you this performance of query
8                        -- will inctrease!
9 SELECT * FROM myTable WHERE ID = @myIDLocal

How to use Crystal reports?

How to connect to Crystal report?
How to use stored procedures at Crystal reports?
How to pass parameter to Crystal report?

 1 
 2 ReportDocument reportDocument = new ReportDocument();
 3 ParameterField paramField = new ParameterField();
 4 ParameterFields paramFields = new ParameterFields();
 5 ParameterDiscreteValue paramDiscreteValue = 
                          new ParameterDiscreteValue();
 6 
 7 /***********************************/
 8 
 9 paramField.Name = "@ID";
10 paramDiscreteValue.Value = textBox.Text;
11 paramField.CurrentValues.Add(paramDiscreteValue);
12 paramFields.Add(paramField);
13 CrystalReportViewer.ParameterFieldInfo = paramFields;
14 
15 reportDocument.Load(Server.MapPath("Report.rpt"));
16 CrystalReportViewer1.ReportSource = reportDocument;
17 reportDocument.SetDatabaseLogon("myUsername", "myPassword",
18                    "192.187.1.1", "myDatabase");

Reference

How to get set session variables in ASP?

How to get set session variables in ASP.NET?
How to get set session variables in ASP (c#)?
How to get set session variables at ASP?

1 //Get session value:
2 
3 int sessionValue = ((int)Context.Session.Contents["myValue"]);
4 
5 
6 //Set session value:
7 
8 Context.Session.Add("mySessionValue", 1001);

How to access connection string from code?

How to call connection string at ASP.NET (C#)?
How to call connection string in ASP?

1 ConfigurationManager.ConnectionStrings["MyConnectionString"]
.ConnectionString

How to create excel at ASP?

How to create excel at ASP.NET?
How to make excel file at ASP web Page?

In the example belove we open a template Excel file, change it content
and save it as an other file.

You add
using Microsoft.Office.Interop.Excel;
directive and the rest is straight forward.

You must also add Microsoft.Office.Interop.Excel as reference from Add Reference.

 1        Sheets WRss = null;
 2        _Worksheet WRws = null;
 3 
 4        string baseFile = Server.MapPath(".") + "\\MyTemplate.xls";
 5        string newFile = Server.MapPath(".") + "\\MyFile.xls";
 6 
 7        Microsoft.Office.Interop.Excel.Application oExcel =
 8        new Microsoft.Office.Interop.Excel.Application();
 9 
10        oExcel.Workbooks.Open(baseFile, Type.Missing, false,
11        Type.Missing, Type.Missing, Type.Missing,
12        Type.Missing, Type.Missing, Type.Missing,
13        Type.Missing, Type.Missing, Type.Missing,
14        Type.Missing, Type.Missing, Type.Missing);
15 
16        WRss = (Sheets)oExcel.Worksheets;
17        WRws = (_Worksheet)(WRss.get_Item(1));
18 
19        WRws.Cells.Font.Size = 12;
20 
21         WRws.Cells[7, 3] = "Write Sometyhing";
22 
23        if (System.IO.File.Exists(newFile))
24            System.IO.File.Delete(newFile);
25 
26        oExcel.Workbooks[1].SaveAs(newFile, Type.Missing,
27        Type.Missing, Type.Missing, Type.Missing, false,
28        Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
29        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
30 
31        oExcel.Workbooks.Close();
32        oExcel.Quit();
33 
34        Response.Clear();
35        Response.AppendHeader("Content-Type", "application/vnd.ms-excel");
36        Response.AppendHeader("Content-disposition",
37        "attachment; filename=MyFile.xls");
38        Response.TransmitFile(newFile);

NOTE: if you get the

How to download file at ASP?

How to download file at ASP.NET?
How to download file in ASP?

1 string newFile = Server.MapPath(".") + "\\MyFile.xls";
2 Response.Clear();
3 Response.AppendHeader("Content-Type", "application/vnd.ms-excel");
4 Response.AppendHeader("Content-disposition", "attachment; filename=Hesabat.xls");
5 Response.TransmitFile(newFile);

How to get table list by SQL?

We can get list of tables in our database:

1 SELECT
2 TABLE_SCHEMA,
3 TABLE_NAME,
4 OBJECTPROPERTY(object_id(TABLE_NAME),
5 N'IsUserTable') AS type
6 
7 FROM
8 
9 INFORMATION_SCHEMA.TABLES

Reference