How to and Where to use Aspose.cells in DNN application?
DNK Gif
Thursday, 25 June 2015
Wednesday, 17 June 2015
INSERT/UPDATE/DELETE/SELECT into Table SQL
Insert
Insert statements are used to insert data into tables.
Syntax
INSERT INTO Table VALUES( “attribute value1”, “attribute value 2”....);
INSERT INTO Table(Coumn1,Column2,..) VALUES( “attribute value1”, “attribute
value 2”....);
Example
Insert into Customer VALUES(1,'Customer Name1','Male',40,'India')
Insert into Customer(CustomerID,CustomerName,Gender,Age,Location)
VALUES(1,'Customer Name1','Male',40,'India')
Update
UPDATE statement helps in modifying database records
Syntax
UPDATE Table set attribute2 = “new value” WHERE attribute1 = “xyz”;
Example
Update Customer set age=30 where CustomerID=100
Delete
Delete statement helps in deleting database records
Syntax
DELETE FROM Table WHERE attribute1 = “xyz”;
Example
Delete from Customer where CustomerID=100;
Select statements help in selecting data from the database
Syntax
SELECT * FROM Table ;
Example
SELECT * FROM Customer
Data can be retrieved from the database using different Select options which have been
discussed below
Syntax
SELECT attribute1,attribute2 FROM Table ;
Example
SELECT CustomerID,CustomerName from Customer
Shows the column values by removing the duplicate values.
Syntax
SELECT DISTINCT attribute1 FROM Table ;
Example
Select distinct Location from Customer
String functions such as Upper,Lower,Substring,CharIndex can be used in the Select statements
Syntax
SELECT UPPER( attribute1), LOWER( attribute1) FROM Table;
Example
SELECT UPPER(Location) from Customer
Syntax
SELECT SUBSTRING(attribute1, 1, CHARINDEX(' ', attribute1)) FROM Table;
The below example will retrieve the first name from the Customer Name in which the First and the Last Name are separated by spaces.
Select SUBSTRING(CustomerName,1, CHARINDEX(' ', CustomerName)) from Customer
The CharIndex function returns the position of the space character in Customer Name. Then using the Substring function we can retrieve the first name by providing the start position and the position returned by CharIndex function.
The Convert function is sued to display the date columns in the table in different styles
Syntax
SELECT CONVERT(data type, attribute1, format ) FROM Table;
Example
The below statement will display the DateOfBirth in mm/dd/yy format.
Select Convert(varchar(10),DOB,101) from Customer
Syntax
SELECT attribute1 FROM Table WHERE attribute2 = “xyz” ;
SELECT attribute1 FROM Table WHERE attribute2 = “xyz” AND attribute3 = “pqr”;
Example
Select CustomerName from Customer where CustomerID > 100;
Select CustomerName from Customer where CustomerID > 100 and CustomerID <200;
SELECT attribute1 FROM Table WHERE attribute2 NOT IN (“pqr”, “xyz”) ;
Displays the Customer details whose Location is either India or US.
Select CustomerID,CustomerName from Customer Where Location NOT in ('India','US')
Displays the Customer details whose Location is neither India or US.
Displays the Customer details whose Location is NULL.
SELECT attribute1 FROM Table ORDER BY attribute1 order by desc.
Select CustomerID,CustomerName from Customer order by Location desc
In the above example the records in Customer table will be grouped by location values. Suppose we have locations like India,US,UK, the records will be now grouped under US,UK and India. The column values used for grouping alone can be displayed using Select. So the above statement will display only US,UK and India.
The aggregate function used is the count function.
*Update the Employee name and Department of a particular Employee
* Delete an Employee based on Employee Id
* Select the Employee based on below criteria
1.Employees of a Department
2.Employees not assigned to department
3.Employees count grouped by department
2.Update the Department and Employee name using update statement
3.Delete an Employee using the delete statement by providing the Employeeid in the
where condition
4.List the employees of a department using select with where condition
5.List the employees who have not been assigned to department using null condition
6.List the count of employees using GroupBy
Update Employee set EmpName='Emp2',Department='Accounts' where Empid=2
Delete from Employee where Empid= 100
Select * from Employee where Department='Accounts'
Select * from Employee where Department is null
Select count(*) from Employee group by Department
2.The Department and EmpName has been updated using update statement based on Empid
3.An employee with empid is deleted using delete statements
4.The employees of Department Account have been listed
5.The employees not assigned to any department have been listed using null condition
6.The count of employees of each department is displayed using group by and aggregate function
Insert statements are used to insert data into tables.
Syntax
INSERT INTO Table VALUES( “attribute value1”, “attribute value 2”....);
INSERT INTO Table(Coumn1,Column2,..) VALUES( “attribute value1”, “attribute
value 2”....);
Example
Insert into Customer VALUES(1,'Customer Name1','Male',40,'India')
Insert into Customer(CustomerID,CustomerName,Gender,Age,Location)
VALUES(1,'Customer Name1','Male',40,'India')
Update
UPDATE statement helps in modifying database records
Syntax
UPDATE Table set attribute2 = “new value” WHERE attribute1 = “xyz”;
Example
Update Customer set age=30 where CustomerID=100
Delete
Delete statement helps in deleting database records
Syntax
DELETE FROM Table WHERE attribute1 = “xyz”;
Example
Delete from Customer where CustomerID=100;
Select
Select statements help in selecting data from the database
Syntax
SELECT * FROM Table ;
Example
SELECT * FROM Customer
Data can be retrieved from the database using different Select options which have been
discussed below
Select statement with projections
Selecting only particular columns from the databaseSyntax
SELECT attribute1,attribute2 FROM Table ;
Example
SELECT CustomerID,CustomerName from Customer
Select statement with Distinct
Shows the column values by removing the duplicate values.
Syntax
SELECT DISTINCT attribute1 FROM Table ;
Example
Select distinct Location from Customer
Select statement with String Functions
String functions such as Upper,Lower,Substring,CharIndex can be used in the Select statements
Syntax
SELECT UPPER( attribute1), LOWER( attribute1) FROM Table;
Example
SELECT UPPER(Location) from Customer
Syntax
SELECT SUBSTRING(attribute1, 1, CHARINDEX(' ', attribute1)) FROM Table;
The below example will retrieve the first name from the Customer Name in which the First and the Last Name are separated by spaces.
Select SUBSTRING(CustomerName,1, CHARINDEX(' ', CustomerName)) from Customer
The CharIndex function returns the position of the space character in Customer Name. Then using the Substring function we can retrieve the first name by providing the start position and the position returned by CharIndex function.
Select statement with Convert Functions
The Convert function is sued to display the date columns in the table in different styles
Syntax
SELECT CONVERT(data type, attribute1, format ) FROM Table;
Example
The below statement will display the DateOfBirth in mm/dd/yy format.
Select Convert(varchar(10),DOB,101) from Customer
Select statement with Filters
Select statements with Filters are used to select records based on conditionSELECT statement with WHERE
The filter condition is specified using the WHERE Clause.Syntax
SELECT attribute1 FROM Table WHERE attribute2 = “xyz” ;
SELECT attribute1 FROM Table WHERE attribute2 = “xyz” AND attribute3 = “pqr”;
Example
Select CustomerName from Customer where CustomerID > 100;
Select CustomerName from Customer where CustomerID > 100 and CustomerID <200;
SELECT statement with WHERE and IN/NOT IN
This filter is used for checking for matching and unmatched records given a list of valuesSyntax
SELECT attribute1 FROM Table WHERE attribute2 IN (“pqr”, “xyz”) ;SELECT attribute1 FROM Table WHERE attribute2 NOT IN (“pqr”, “xyz”) ;
Example
Select CustomerID,CustomerName from Customer Where Location in ('India','US')Displays the Customer details whose Location is either India or US.
Select CustomerID,CustomerName from Customer Where Location NOT in ('India','US')
Displays the Customer details whose Location is neither India or US.
SELECT statement with WHERE and NULL
This filter is used for selecting records for columns with NULL value.Syntax
SELECT attribute1 FROM Table WHERE attribute2 IS NULL ;Example
Select CustomerID,CustomerName from Customer where Location is NULLDisplays the Customer details whose Location is NULL.
SELECT statement with Order By
This filter is used to display the records based on column values either in ascending or descending orderSyntax
SELECT attribute1 FROM Table ORDER BY attribute1;SELECT attribute1 FROM Table ORDER BY attribute1 order by desc.
Example
Select CustomerID,CustomerName from Customer order by Location.Select CustomerID,CustomerName from Customer order by Location desc
SELECT statement with Group By
This filter is used for grouping records based on a column value. The column values used for grouping can be listed using the select statement.Syntax
SELECT attribute1 FROM Table GROUP BY attribute1;Example
SELECT Location FROM Customer GROUP BY Location;In the above example the records in Customer table will be grouped by location values. Suppose we have locations like India,US,UK, the records will be now grouped under US,UK and India. The column values used for grouping alone can be displayed using Select. So the above statement will display only US,UK and India.
SELECT statement with Group By and Having
The Having clause is used for displaying the column values used in group by clause based on a condition. In the above example if we want to display the locations which are having a count of more than 10 employees,then we can go for having clause. Having clause has to be used along with aggregate functions. Refer to Aggregate functions documents for understanding on Aggregate functions.Syntax
SELECT attribute1 FROM Table GROUP BY attribute1 HAVING aggregate_function(attribute1) > value ;Example
SELECT Location FROM Customer GROUP BY Location having count(Location) > 0The aggregate function used is the count function.
Problem Scenario
*Insert values into Employee table which has Columns like EmployeeID,Employee Name, Department,Date of Joining.*Update the Employee name and Department of a particular Employee
* Delete an Employee based on Employee Id
* Select the Employee based on below criteria
1.Employees of a Department
2.Employees not assigned to department
3.Employees count grouped by department
Approach to solve the problem
1.Insert the values into Employee table using Insert statement2.Update the Department and Employee name using update statement
3.Delete an Employee using the delete statement by providing the Employeeid in the
where condition
4.List the employees of a department using select with where condition
5.List the employees who have not been assigned to department using null condition
6.List the count of employees using GroupBy
Solution
Insert into Employee(1, 'Emp1','Finance','09-09-2000')Update Employee set EmpName='Emp2',Department='Accounts' where Empid=2
Delete from Employee where Empid= 100
Select * from Employee where Department='Accounts'
Select * from Employee where Department is null
Select count(*) from Employee group by Department
Explanation about the solution
1.Details are inserted using Insert statement.2.The Department and EmpName has been updated using update statement based on Empid
3.An employee with empid is deleted using delete statements
4.The employees of Department Account have been listed
5.The employees not assigned to any department have been listed using null condition
6.The count of employees of each department is displayed using group by and aggregate function
Tuesday, 16 June 2015
Constraints of SQL Table
Constraints helps to ensure that valid data goes into the database entity by specifying rules.
- NOT NULL : Ensures that attribute cannot be null.
- Primary Key : Represents a column or combination of columns as a unique identity for a row
- FOREIGN KEY : Used to establish relationships between tables using matching columns.
- UNIQUE : The value is unique (not repeated for other entities)
- Check : Used to allow values for a column based on a condition
- Default : Used for inserting default value into columns.
NOT NULL
The NOT NULL constraint will not allow a column to accept NULL values
Below is an example for creating NOT NULL constraint.
Create table Customer(CustomerID int NOT NULL,Name varchar(50) NOT
NULL,Age int NOT NULL,Gender varchar(10) NOT NULL,Occ varchar(10))
Primary Key
The PRIMARY KEY constraint identifies each record in a database table by allowing only unique values. Each table can have only ONE primary key
Below is an example for creating Primary Key constraint.
Create table Customer(CustomerID int NOT NULL Primary Key,Name varchar(50)
NOT NULL,Age int NOT NULL,Gender varchar(10) NOT NULL,Occ varchar(10))
Primary Key can also be added using alter statement as below
ALTER TABLE Customer
ADD PRIMARY KEY (CustomerID)
Foreign Key
Foreign Key is used to establish relationships between tables using matching columns. A FOREIGN KEY in one table refers to a PRIMARY KEY in another table. It ensures that data available in the Primary key column of one table can only be entered in the Foreign key column of another table.
Below is an example for creating Foreign Key constraint
CREATE TABLE Orders
OrderId int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
CustomerId int FOREIGN KEY REFERENCES Customer(CustomerID)
Foreign Key can also be added using alter statement as below
ALTER TABLE Orders
ADD FOREIGN KEY (CustomerId)
REFERENCES Customer(CustomerId)
UNIQUE
UNIQUE Constraint like Primary Key ensures uniqueness for a column. The UNIQUE Constraint is different from Primary Key in that a table can have only Primary Key but there can be many columns with Unique Constraint.
Below is an example for creating Unique Constraint
Create table Customer(CustomerID int NOT NULL Primary Key,Name varchar(50)
NOT NULL,Age int NOT NULL,Gender varchar(10) NOT NULL,Occ
varchar(10),Contact int UNIQUE)
Check
Check constraint is used to allow values for a column based on a condition.
Below is an example for creating Check Constraint
We can create a Check Constraint on Age column to allow only values greater than 0.
Create table Customer(CustomerID int NOT NULL Primary Key,Name varchar(50)
NOT NULL,Age int NOT NULL,Gender varchar(10) NOT NULL,Occ
varchar(10),CHECK (Age >0))
Default
Default constraint is used for inserting default value into columns.
Below is an example for creating Default Constraint
We can create a Default Constraint on Location column to enter the default location as 'India' if location is not inserted while entering Customer details.
Create table Customer(CustomerID int NOT NULL Primary Key,Name varchar(50)
NOT NULL,Age int NOT NULL,Gender varchar(10) NOT NULL,Occ
varchar(10),Location varchar(20) DEFAULT 'India')
Problem Scenario:
Create database tables for adding employee details such as Employeeid, Employee name,Date of Joining,Age,Contact number and their salary details such as HRA,Basic,PF,Bonus and Gross Salary on monthly basis.
Approach to solve the problem
1. As we need to insert Employee details and their salary details on monthly basis we can create one table for Employee details and one table for storing Employee Salary details
2. Create the table Employee with Employeeid,Employee name,Date of Joining, Age,Contact number columns.
3.Create the table EmployeeSalary with HRA,Basic,PF,Bonus and Gross
Salary,SalaryDate columns.
Solution
Create table Employee(EmployeeID int NOT NULL Primary Key,Name varchar(50)
NOT NULL,Age int NOT NULL,Gender varchar(10) NOT NULL,DateOfJoining Date,Contact
int UNIQUE,CHECK (Age >0))
Create Table EmployeeSalary(SalaryID int NOT NULL Primary Key,Basic int,HRA
int,Bonus int,Gross int,SalaryDate Date,EmployeeID FOREIGN KEY REFERENCES
Employee(EmployeeID))
Explanation about the solution
1. As the Employee Id should be unique and identify each row separately we can create
Employee Id as the Primary Key in Employee table.
2. We can create a Check Constraint on age and Unique Constraint on Contact in
Employee table.
3. In Employee salary details create Salary Id as Primary Key and Employee ID as foreign
key referencing Employee Id in Employee table.
- NOT NULL : Ensures that attribute cannot be null.
- Primary Key : Represents a column or combination of columns as a unique identity for a row
- FOREIGN KEY : Used to establish relationships between tables using matching columns.
- UNIQUE : The value is unique (not repeated for other entities)
- Check : Used to allow values for a column based on a condition
- Default : Used for inserting default value into columns.
NOT NULL
The NOT NULL constraint will not allow a column to accept NULL values
Below is an example for creating NOT NULL constraint.
Create table Customer(CustomerID int NOT NULL,Name varchar(50) NOT
NULL,Age int NOT NULL,Gender varchar(10) NOT NULL,Occ varchar(10))
Primary Key
The PRIMARY KEY constraint identifies each record in a database table by allowing only unique values. Each table can have only ONE primary key
Below is an example for creating Primary Key constraint.
Create table Customer(CustomerID int NOT NULL Primary Key,Name varchar(50)
NOT NULL,Age int NOT NULL,Gender varchar(10) NOT NULL,Occ varchar(10))
Primary Key can also be added using alter statement as below
ALTER TABLE Customer
ADD PRIMARY KEY (CustomerID)
Foreign Key
Foreign Key is used to establish relationships between tables using matching columns. A FOREIGN KEY in one table refers to a PRIMARY KEY in another table. It ensures that data available in the Primary key column of one table can only be entered in the Foreign key column of another table.
Below is an example for creating Foreign Key constraint
CREATE TABLE Orders
OrderId int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
CustomerId int FOREIGN KEY REFERENCES Customer(CustomerID)
Foreign Key can also be added using alter statement as below
ALTER TABLE Orders
ADD FOREIGN KEY (CustomerId)
REFERENCES Customer(CustomerId)
UNIQUE
UNIQUE Constraint like Primary Key ensures uniqueness for a column. The UNIQUE Constraint is different from Primary Key in that a table can have only Primary Key but there can be many columns with Unique Constraint.
Below is an example for creating Unique Constraint
Create table Customer(CustomerID int NOT NULL Primary Key,Name varchar(50)
NOT NULL,Age int NOT NULL,Gender varchar(10) NOT NULL,Occ
varchar(10),Contact int UNIQUE)
Check
Check constraint is used to allow values for a column based on a condition.
Below is an example for creating Check Constraint
We can create a Check Constraint on Age column to allow only values greater than 0.
Create table Customer(CustomerID int NOT NULL Primary Key,Name varchar(50)
NOT NULL,Age int NOT NULL,Gender varchar(10) NOT NULL,Occ
varchar(10),CHECK (Age >0))
Default
Default constraint is used for inserting default value into columns.
Below is an example for creating Default Constraint
We can create a Default Constraint on Location column to enter the default location as 'India' if location is not inserted while entering Customer details.
Create table Customer(CustomerID int NOT NULL Primary Key,Name varchar(50)
NOT NULL,Age int NOT NULL,Gender varchar(10) NOT NULL,Occ
varchar(10),Location varchar(20) DEFAULT 'India')
Problem Scenario:
Create database tables for adding employee details such as Employeeid, Employee name,Date of Joining,Age,Contact number and their salary details such as HRA,Basic,PF,Bonus and Gross Salary on monthly basis.
Approach to solve the problem
1. As we need to insert Employee details and their salary details on monthly basis we can create one table for Employee details and one table for storing Employee Salary details
2. Create the table Employee with Employeeid,Employee name,Date of Joining, Age,Contact number columns.
3.Create the table EmployeeSalary with HRA,Basic,PF,Bonus and Gross
Salary,SalaryDate columns.
Solution
Create table Employee(EmployeeID int NOT NULL Primary Key,Name varchar(50)
NOT NULL,Age int NOT NULL,Gender varchar(10) NOT NULL,DateOfJoining Date,Contact
int UNIQUE,CHECK (Age >0))
Create Table EmployeeSalary(SalaryID int NOT NULL Primary Key,Basic int,HRA
int,Bonus int,Gross int,SalaryDate Date,EmployeeID FOREIGN KEY REFERENCES
Employee(EmployeeID))
Explanation about the solution
1. As the Employee Id should be unique and identify each row separately we can create
Employee Id as the Primary Key in Employee table.
2. We can create a Check Constraint on age and Unique Constraint on Contact in
Employee table.
3. In Employee salary details create Salary Id as Primary Key and Employee ID as foreign
key referencing Employee Id in Employee table.
Alter Table SQL
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. ALTER TABLE also comes under Data Definition language (DDL)
Add Column
Syntax:
Add Column
Syntax:
ALTER TABLE table_name
ADD column_name datatype
Example
ALTER TABLE Customer
ADD Location varchar(50);
Drop Column
Syntax
ALTER TABLE table_name
DROP COLUMN column_name
Example
ALTER TABLE Customer
Drop Location varchar(50);
Change DataType
Syntax
ALTER TABLE table_name
ALTER COLUMN column_name datatype
Example
ALTER TABLE Customer
Drop Location varchar(30);
Create Table SQL
Table Creation
Entities are represented as tables in database. The attributes of entities are represented as columns. As the data’s are stored in the attributes of entities the data’s are stored in columns in tables. Tables are organized into rows and columns.
For example the Customer entity can be represented as Customer table and the attributes like Name,Age,Gender,Occ,Phone,Location,Work Exp can be represented as
columns in the table.
Create Table
The CREATE TABLE statement is used to create a table in a database. Create table
statement comes under Data Definition language (DDL)
Syntax
CREATE TABLE Table ( Column1 Data type(size), Column2 Data type(size),....);
While creating table we need to
Specify the table name
Specify the column names
Specify what type of data each column need to hold (e.g. varchar, integer, decimal, date,
etc.).
Specify the size parameter,which the maximum length of the column of the table
Example
Create table Customer(CustomerID int,Name varchar(50),Age int,Gender
varchar(10),Occ varchar(10));
Save bytearray[] data to HttpResponse object
How to save bytearray[] data to HttpResponse and download the file by browser
Here's the code for this:-Write this in your Webform.aspx:
<asp:LinkButton ID="LinkButton1" runat="server" OnClick="LinkButton1_Click" >LinkButton1</asp:LinkButton>
and this is the code behind function's code:-
protected void LinkButton1_Click(object sender, EventArgs e)
{
IReportBuilder builder = new ReportBuilder();
IReportDataProvider data = new ReportDataProvider();
IReportProvider report = new ReportProvider(data, builder);
byte[] buffer = report.GetMyReport(Convert.ToInt32(txtUserId.Text));
System.Web.HttpResponse response = System.Web.HttpContext.Current.Response;
response.Clear();
string filename = "attachment; filename=MyReport_UserId_"+txtUserId.Text;
response.AddHeader("Content-Type", "application/ms-excel");
response.AddHeader("Content-Disposition", String.Format(filename+".xlsx; size={0}",buffer.Length.ToString()));
response.BinaryWrite(buffer);
response.End();
}
Note:- Content-Type can be octet-stream, vnd.ms-excel, ms-word or pdf also
Remember:- Where-ever you are creating the bytearray[] file from Memorystream object, seek the file pointer to 0 or beginning by this piece of code.
MemoryStream stream = new MemoryStream();
workbook.Save(stream, SaveFormat.Xlsx);
stream.Seek(0, SeekOrigin.Begin);
return stream.ToArray();
Friday, 12 June 2015
DB SearchString SQL
Search entire database of SQL Server with the search string and it will return you the table name from the database and column name containing the string value
Here is the code:
DECLARE
@search_string VARCHAR(100),
@table_name SYSNAME,
@table_id INT,
@column_name SYSNAME,
@sql_string VARCHAR(2000)
SET @search_string = 'umesh kumar'
DECLARE tables_cur CURSOR FOR SELECT name, object_id FROM sys.objects WHERE type = 'U'
OPEN tables_cur
FETCH NEXT FROM tables_cur INTO @table_name, @table_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id AND system_type_id IN (167, 175, 231, 239)
OPEN columns_cur
FETCH NEXT FROM columns_cur INTO @column_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sql_string = 'IF EXISTS (SELECT * FROM [Schema Name].' + @table_name + ' WHERE [' + @column_name + '] LIKE ''%' + @search_string + '%'') PRINT ''' + @table_name + ', ' + @column_name + ''''
BEGIN TRY
EXECUTE(@sql_string)
END TRY
BEGIN CATCH
END CATCH
FETCH NEXT FROM columns_cur INTO @column_name
END
CLOSE columns_cur
DEALLOCATE columns_cur
FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END
CLOSE tables_cur
DEALLOCATE tables_cur
Here is the code:
DECLARE
@search_string VARCHAR(100),
@table_name SYSNAME,
@table_id INT,
@column_name SYSNAME,
@sql_string VARCHAR(2000)
SET @search_string = 'umesh kumar'
DECLARE tables_cur CURSOR FOR SELECT name, object_id FROM sys.objects WHERE type = 'U'
OPEN tables_cur
FETCH NEXT FROM tables_cur INTO @table_name, @table_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id AND system_type_id IN (167, 175, 231, 239)
OPEN columns_cur
FETCH NEXT FROM columns_cur INTO @column_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sql_string = 'IF EXISTS (SELECT * FROM [Schema Name].' + @table_name + ' WHERE [' + @column_name + '] LIKE ''%' + @search_string + '%'') PRINT ''' + @table_name + ', ' + @column_name + ''''
BEGIN TRY
EXECUTE(@sql_string)
END TRY
BEGIN CATCH
END CATCH
FETCH NEXT FROM columns_cur INTO @column_name
END
CLOSE columns_cur
DEALLOCATE columns_cur
FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END
CLOSE tables_cur
DEALLOCATE tables_cur
Wednesday, 3 June 2015
Initialize License file of Aspose
How to Initialize License file of Aspose?
License initialization can be done in the Global.asax file or you can put the code in the constructor of your class, which you are using for the generation of your excel report.
Codes:
Here my class name is ReportBuilder. I have put the Aspose.lic file in a folder "License" in my project. I have written the path in a .cs file name AppConstant to avoid hard coding in project and i am using it here.
Note:
Write the path in the AppConstant file like this
public const string ASPOSE_LICENSE_PATH = ".License.Aspose.Total.lic";
public ReportBuilder()
{
string fileKey = string.Concat(this.GetType().Namespace, AppConstant.ASPOSE_LICENSE_PATH);
Stream file = Assembly.GetExecutingAssembly().GetManifestResourceStream(fileKey);
Aspose.Cells.License cellsLicense = new Aspose.Cells.License();
cellsLicense.SetLicense(file);
}
First i am assigning the path to a string variable filekey. Now i am getting the Project directory of the server by the code this.GetType().Namespace and by concatenation i am getting the full path of the license file. Then i am converting the file to MemoryStream object and setting the license. SetLicense() method supports other over loads also. For my convenience i have used like that.
Another important thing here is set the BuildAction of the "Aspose.license" file to "Embedded Resource" instead of "None"
How to do this?
Right click on the Aspose.license file , go to property, now set the BuildAction to Embedded Resource.
So setting license file for Aspose is done.
Tuesday, 2 June 2015
Generate Excel Report dynamically using Aspose.cells and Dot Net Overview
Aspose.Cells for .NET
Aspose.Cells for .NET is an Excel spreadsheet programming component and library that allows software developers to manipulate and convert spreadsheet files from within their own applications. A combination of APIs and GUI controls, Aspose.Cells for .NET speeds up Microsoft Excel programming and conversion.
The APIs give developers powerful tools for performing simple file conversions or more complex tasks. Developers have control over page layout, formatting, charts and formulas. They can read and write spreadsheet files and save out to a variety of image, portable and text file formats.
Aspose.Cells for .NET is fast and reliable. It saves time and effort compared to developing your own spreadsheet manipulation solution or using Microsoft Automation.
Subscribe to:
Posts (Atom)