Saving Word Document to the Sql Server

First we have to create a table to store the Word Document
CREATE TABLE [dbo].[File1](
[id] [int] IDENTITY(1,1) NOT NULL,
[Filename] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[FileBytes] [varbinary](max) NOT NULL
) ON [PRIMARY]
The Design view of a Web Form
<asp:Label id="lblFile" Text="wordDocument" AssociatedControlID="upFile" runat="server" />
<asp:FileUpload id="upFile" runat="Server" />
<asp:Button id="btnAdd" Text="Add Documents" runat="server" OnClick="btnAdd_Click" />
<hr />
<asp:Repeater id="rptFiles" DataSourceID="srcFiles" runat="Server">
<HeaderTemplate>
<ul class="fileList">
</HeaderTemplate>
<ItemTemplate>
<li>
<asp:HyperLink id="lnkFile" Text='<%#Eval("FileName")%>' NavigateUrl='<%#Eval("id","~/FileHandler.ashx?id={0}")%>' runat="Server" />
</li>
</ItemTemplate>
<FooterTemplate>
</ul>
</FooterTemplate>
</asp:Repeater>
<asp:SqlDataSource id="srcFiles"
ConnectionString="<%$ ConnectionStrings:FileDBConnectionString %>"
SelectCommand="SELECT [id], [Filename] FROM [File1]"
InsertCommand="INSERT INTO [File1] (Filename, FileBytes) values (@fileName,@FileBytes)"
runat="server">
<InsertParameters>
<asp:ControlParameter Name="FileName" ControlID="upFile" PropertyName="FileName" />
<asp:ControlParameter Name="FileBytes" ControlID="upFile" PropertyName="FileBytes" />
</InsertParameters>
</asp:SqlDataSource>
The Code Behind of Button Add Documents
if (upFile.HasFile)
{
if (CheckFileType(upFile.FileName))
srcFiles.Insert();
}
The Function CheckFileType
bool CheckFileType(string fileName)
{
return Path.GetExtension(fileName).ToLower() == ".doc";
}
Then Add Custom Handler on to the Page
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "application/msword";
SqlConnection con = new SqlConnection(@"Data Source=SHEEBAN\SQLEXPRESS;Initial Catalog=FileDB;Integrated Security=True");
SqlCommand cmd = new SqlCommand("select filebytes from [file1] where id=@id", con);
cmd.Parameters.AddWithValue("@id", context.Request["id"]);
using (con)
{
con.Open();
byte[] file = (byte[])cmd.ExecuteScalar();
context.Response.BinaryWrite(file);
}
}
public bool IsReusable
{
get
{
return false;
}
}

Saving Data into XML Format

DataSet ds = new DataSet();
Xml Xml1 = new Xml();
protected void Page_Load(object sender, EventArgs e)
{
//Create a connection string.
string sqlConnect=@"Data Source=SHEEBAN\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True";
SqlConnection sqlconnect = new SqlConnection(ConfigurationManager.ConnectionStrings["NorthwindConnection"].ConnectionString);
//Create a connection object to connect to the web shoppe database
try
{
SqlConnection nwconnect = new SqlConnection(sqlConnect);
String scommand = "select top 10 * from customers";
//Create an adapter to load the dataset
SqlDataAdapter da = new SqlDataAdapter(scommand, nwconnect);
//Fill the dataset
da.Fill(ds, "Customers");
XmlDataDocument doc = new XmlDataDocument(ds);
//Xml1.Document = doc;
doc.Save(MapPath("Customers.xml"));//This is where we are saving the data in an XML file Customers.xml
Label1.Text = "Your Data saved succesfully";
}
catch
{
//if there is any error then Label1 will give the Error
Label1.Text = "Error while connecting to database";
}
}

SQL Injection Example

Design A Form of the Web Page
<table>
<tr>
<td>
UserName:
</td>
<td>
<asp:TextBox ID="tbxUserName" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
Password:
</td>
<td>
<asp:TextBox ID="tbxPassword" runat="server" TextMode="Password"></asp:TextBox>
</td>
</tr>
<tr>
<td colspan="2" align="right">
<asp:Button ID="btnLogin" runat="server" Text="Login" Width="150px" OnClick="btnLogin_Click"/>
</td>
</tr>
<tr>
<td colspan="2" align="left">
<asp:Literal ID="Literal1" runat="server" Text=""></asp:Literal>
</td>
</tr>
</table>
//Now the Code Behind of the Button Login
string conn = ConfigurationManager.ConnectionStrings["FileDBConnectionString"].ConnectionString;
string query = "Select Count(*) From Users Where Username = '" + tbxUserName.Text + "' And Password = '" + tbxPassword.Text + "'";
int result = 0;
SqlConnection connection = new SqlConnection(conn);
connection.Open();
SqlCommand cmd = new SqlCommand(query, connection);
result = (int)cmd.ExecuteScalar();
if (result > 0)
{
connection.Close();
Response.Redirect("LoggedIn.aspx");
}
else
{
Literal1.Text = "Invalid credentials";
connection.Close();
}
If we copy the "'or'1'='1"(without quotation marks) both in the password textbox and username textbox it will redirect you on to the LoggedIn page this is what we called SQL Injection


In order to control the SQL Injection we have to add few code of line Just before the Query
string username = tbxUserName.Text.Replace("'", "''");
string password = tbxPassword.Text.Replace("'", "''");
And query would be change into
string query = "Select Count(*) From Users Where Username = '" + username + "' And Password = '" + password + "'";

Toggle HTML Element

//Just Design of a Web Form
Enter the Text:<input id="Text1" type="text" />
<input id="Button1" type="button" value="button" onclick="toggle()" />

//JavaScript to toggle the HTML Element
<script type="text/javascript">
function toggle(){
document.getElementById('Text1').style.display == '' ? document.getElementById('Text1').style.display = 'none'
: document.getElementById('Text1').style.display = '';
}
</script>

Concatenation in Sql Query

select * from DataTypeCategory
--if i run this query i will get result like that.

--now i want to concatenate all the same category data type so i have to write query like that

SELECT A.Category,
(SELECT B.DataType + ',' FROM DataTypeCategory B WHERE B.Category=A.Category
FOR XML PATH('')) 'Concatinated' FROM DataTypeCategory A GROUP BY A.Category

SQL_Query_Substring

DECLARE @SampleString VARCHAR(MAX)
--I declare @SampleString cause i can easily increase the length of variable '@SampleString'
SELECT @SampleString = 'Sheeban Ahmed'

SELECT SUBSTRING(@SampleString,1,1) AS S
--S

SELECT SUBSTRING(@SampleString,1,2) AS SH
--Sh

SELECT SUBSTRING(@SampleString,1,0) AS BLANK
--<BLANK>

SELECT SUBSTRING(@SampleString,0,1) AS BLANK
--<BLANK>

SELECT SUBSTRING(@SampleString,-1,4) AS SH
--S

SELECT SUBSTRING(@SampleString,-1,0) AS BLANK
--<BLANK>

SELECT SUBSTRING(@SampleString,-10,12) AS S
--S

SELECT SUBSTRING(@SampleString,-10,25) AS [SHEEBAN AHMED]
--Sheeban Ahmed

Reading and Writing File

The Design view of that Page
Write the value in the TextBox
<asp:TextBox ID="tbxWrite" runat="server"></asp:TextBox>
<asp:Button ID="btnWrite" runat="server" Text="Write"
OnClick="btnWrite_Click" />
<br />
Read all that value are written in the Textbox
<asp:Button ID="btnRead" runat="server" Text="Read" OnClick="btnRead_Click" />
<asp:Literal ID="litText" runat="server"></asp:Literal>



The Code Behind of that Page

string contents = "";
string fileNameWithPath=@"c:\sample.txt";//where ur text file is present
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnWrite_Click(object sender, EventArgs e)
{
//This will be used to write the file
using (StreamWriter writer = File.AppendText(fileNameWithPath))
{
//whatever u write in that textbox it will write on to that text file
writer.Write(tbxWrite.Text);
}
}

protected void btnRead_Click(object sender, EventArgs e)
{
// Read data from text file
using (StreamReader reader = File.OpenText(fileNameWithPath))
{
// reads a single line of contents
contents = reader.ReadLine();
// reads complete contents of the the file
contents += reader.ReadToEnd();
}
litText.Text = contents;
}