Create session in database in MSSQL

Find the folder containing aspnet_regsql file, probably C:\Windows\Microsoft.NET\Framework64\v4.0.30319\aspnet_regsql.go to this folder from command prompt(CMD)

cd C:\Windows\Microsoft.NET\Framework64\v4.0.30319

execute the command : ASPNET_REGSQL.EXE -S localhost -U sa -P MyPassword -ssadd -sstype p

replace ‘sa’ with database username and ‘MyPassword’ with database password.

Then go to IIS and select the website and go to Session State (in ASP.NET tab).

select SQL Server option and set the connection string. Provide the database username password of the ASPState database.

APPLY the settigns and browse your website.

if a new record is entered in ASPState database >ASPStateTempSessions table, your session works perfect!

 

• if you want to increase the session time out, go to the Application pool of your website in IIS > Advanced Settings and set the Idle-Timeout (Minutes) in Process Model tab to desired value. (default value is 20)

 

 

Get data dictionary from sql server

SELECT
TableName,
ColName,
DataType,
[DataLength] / typestat AS [DataLength],
ISNULL([Description], ”) AS [Description]
FROM
(
SELECT
sysobjects.id AS Major_id,
syscolumns.colid AS Minor_id,
sysobjects.name AS TableName,
syscolumns.name AS ColName,
systypes.name AS DataType,
syscolumns.length AS DataLength,
CASE syscolumns.typestat WHEN 0 THEN 1 ELSE syscolumns.typestat END AS typestat,
syscolumns.colorder AS ColOrder,
sys.extended_properties.value AS [Description]
FROM syscolumns INNER JOIN sysobjects
ON syscolumns.id = sysobjects.id
INNER JOIN systypes
ON syscolumns.xtype = systypes.xtype
LEFT OUTER JOIN sys.extended_properties
ON sysobjects.id = sys.extended_properties.major_id
AND syscolumns.colid = sys.extended_properties.minor_id
WHERE sysobjects.type = ‘U’
) Table_Details
WHERE DataType <> ‘sysname’
ORDER BY TableName, ColOrder;

 

how-to-get-n-rows-starting-from-row-m-from-sorted-table-in-SQL

SELECT *
FROM MyTable
ORDER BY MyColumn
OFFSET @N ROWS
FETCH NEXT @M ROWS ONLY;

 

case sensitive comparsion in sql query

Select * from Tb_Customers where Username = 'raminSHA' COLLATE Latin1_General_CS_AS 
and Password='QWERTy123' COLLATE Latin1_General_CS_AS


Happy coding...

 

Database connection string in aspspider.com free asp hosting server

Data Source=.\SQLExpress;Persist Security Info=True;Integrated Security=SSPI;Initial Catalog=YourUserId_DatabaseName

 

convert data from sql server database to json format

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class samplejson : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
//get json string
string jsonString = GetAllUsers();

Response.Write(jsonString);
}
//
//retrieve data from table
public string GetAllUsers()
{
//declare a string builder
StringBuilder sb=new StringBuilder ();
//database connection
SqlConnection cn = new SqlConnection(@”Data Source=.\SQLEXPRESS;AttachDbFilename=D:\Test\WebSiteJasonTest\App_Data\jasonDB.mdf;Integrated Security=True;User Instance=True”);
//sql query command
SqlCommand cmd = new SqlCommand(“select * from UserDetails”, cn);
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
//convert data in data reader to json format
string jsonString = WriteReaderToJSON(sb, dr);
dr.Close();
cn.Close();
return jasonString;
}
//
//sqldataReader to json string
private string WriteReaderToJSON(StringBuilder sb, IDataReader reader)
{

if (reader == null || reader.FieldCount == 0)
{
sb.Append(“null”);
return ” “;
}

int rowCount = 0;
//”Consumer” is the name of the array
sb.Append(@”{“”Consumers””:[“);

while (reader.Read())
{
sb.Append(“{“);

for (int i = 0; i < reader.FieldCount; i++)
{
sb.Append(“\”” + reader.GetName(i) + “\”:”);
sb.Append(“\”” + reader[i] + “\””);

sb.Append(i == reader.FieldCount -1 ? “” : “,”);

}

sb.Append(“},”);

rowCount++;
}

if (rowCount > 0)
{
int index = sb.ToString().LastIndexOf(“,”);
sb.Remove(index, 1);
}

sb.Append(“]}”);

return sb.ToString();

}
}

 

storing and retrieving of binary file in sql server

create a database with required fields and make sure you mark the data type of the file as varbinary(MAX).

Using System.IO;

Using System.Data.Sqlclient

Using System.Data;

 

 

//to add new user
public void AddUser(string name)
{
// Read the fingerprint file(fpt file) and convert it to Byte Array
string filePath = @”D:\10.fpt”;
string filename = Path.GetFileName(filePath);
FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
BinaryReader br = new BinaryReader(fs);
Byte[] bytes = br.ReadBytes((Int32)fs.Length);
br.Close();
fs.Close();

//insert the file into database
cn = new SqlConnection(connstring);  //connstring is my connection string
cmd = new SqlCommand(“adduser”, cn);  //adduser is the stored procedure
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(“@name”, SqlDbType.VarChar).Value = name;
cmd.Parameters.Add(“@fp”, SqlDbType.Binary).Value = bytes;

cn.Open(); cmd.ExecuteNonQuery();
cn.Close();

}

//retrieve fingerprint from database
public void FingerPrintRtvl(string uid)
{

//retrieving the file from the database
cn = new SqlConnection(connstring);
adp = new SqlDataAdapter(“fingerprintrtvl”, cn);
adp.SelectCommand.CommandType = CommandType.StoredProcedure;
adp.SelectCommand.Parameters.AddWithValue(“@uid”, Convert.ToInt32(uid));
DataSet ds = new DataSet(“MyImages”);
cn.Open();
adp.Fill(ds, “MyImages”);
cn.Close();

//storing the file in byte array
byte[] MyData = new byte[0];

DataRow myRow;
myRow = ds.Tables[“MyImages”].Rows[0];

MyData = (byte[])myRow[“fp”];
int ArraySize = new int();
ArraySize = MyData.GetUpperBound(0);
string temp = System.IO.Path.GetTempPath();
string fpFile = “D:\\” + “Aadhaarfingerprint.fpt”;  //file will be retrieved to this location
//saving the byte array in the local drive
FileStream fs = new FileStream(fpFile, FileMode.OpenOrCreate, FileAccess.Write);
fs.Write(MyData, 0, ArraySize);
fs.Close();

}