ASp.net c# sql

February 17, 2011

Generate Structured Class from xml file

Filed under: asp.net c#, Asp.net, c#, mssql, XML, xml — Tags: , , , , , — Admin @ 1:36 pm

Tired of writing .cs class?

we can generate it see example below…

I created a test.xml file

<test>

<field1>hi</field1>

<field2>bye</field2>

</test>

Step1:

Run visual studio command promt

step2: move to the folder where test.xml is saved and execute below command

xsd test.xml

Above command will generate xsd file based on test.xml

and then execute below command to generate the .cs file

xsd /c test.xsd

Done.

You can easily generate .cs file from xml file.

 

 

Now

 

April 20, 2010

Back button refresh page

There are situation wherein you need to reload the page/control state after browser’s “Back” button is pressed.

For example: If you have a checkbox, If someone clicks on checkbox  postback and selectedindexchange/checkedchange event is fired. Once browser’s back button is pressed, page is refreshed but still the checkbox is checked.

Below is the solution for this.

protected override void OnInit(EventArgs e)

    {
        Response.Cache.SetCacheability(HttpCacheability.NoCache);
        Response.Cache.SetNoStore();
        Response.Cache.SetExpires(DateTime.MinValue);

        base.OnInit(e);
    }

April 15, 2010

Bind XML to Nested Repeater

A simple method to bind nested repeaters with xml upto level3 ( you can go upto n level with this method).
Easiest and simplest method.
<!– Xml file to be used –>
<Root>
<Level1 Name=”Level1″ id=”L1″>
<Level2 Name=”Level2″ id=”L2″ >
<Level3 Value=”28″ id=”L3-1″ sortkey=”1″></Level3>
<Level3 Value=”28″ id=”L3-2″  sortkey=”2″></Level3>
</Level2>
</Level1>
</Root>
<!– End XML –>
<asp:Repeater runat=”server” ID=”rpt1″>
<ItemTemplate>
<%# ((System.Xml.XmlNode)Container.DataItem).Attributes[“Name”].Value%> //Level1
<asp:Repeater runat=”server” ID=”rpt2″  DataSource='<%# ((System.Xml.XmlNode)Container.DataItem).SelectNodes(“Level2”) %>’> //Bind level2 nodes
<ItemTemplate>
<%# ((System.Xml.XmlNode)Container.DataItem).Attributes[“Name”].Value%> //Level2
<asp:Repeater runat=”server” ID=”rpt3″  DataSource='<%# ((System.Xml.XmlNode)Container.DataItem).SelectNodes(“Level3”) %>’> //Bind level3 nodes
<ItemTemplate>
<%# ((System.Xml.XmlNode)Container.DataItem).Attributes[“Value”].Value%> //Level3
</ItemTemplate>
</asp:Repeater>
</ItemTemplate>
</asp:Repeater>
</ItemTemplate>
</asp:Repeater>
Now Bind the Level1 Repeater with below code..
XmlDocument doc = new XmlDocument();
doc.Load(MapPath(“../../test.xml”)); //Path of your xml
XmlNodeList nodes =
doc.SelectNodes(“Root/Level1”); //The first level you need to bind
rpt1.DataSource = nodes;
rpt1.DataBind();

July 10, 2008

Import CSV to sql from asp.net c#

Here I will show how to Import data from csv file to sql database or any other database.

//This is the connection string to connect to your csv file
string strConString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\; Extended Properties=\”Text;HDR=YES;\””;

// open connection
OleDbConnection oCon = new OleDbConnection(strConString);
oCon.Open();

// fill data set
string strSql = “SELECT * FROM csvfile.csv”; //csvfile should be present in c:
OleDbDataAdapter oDA = new OleDbDataAdapter(strSql, oCon);
DataSet oData = new DataSet();
oDA.Fill(oData, “ABC”);
GridView1.DataSource = oData;
GridView1.DataBind();

oCon.Close();

June 10, 2008

SSIS: Integration services: Throw error from Script Task (Error Handling)

There may be requirement to throw error from Script Task.
Here’s a way to Throw error from Script Task.

//Code Starts Here
Try
‘method which throws an error
Catch e
Me.ComponentMetadata.FireError(-1, “”, “Your Error Message: ” + e.Message, “”, true)
While Not e.InnerException Is Nothing
e = e.InnerException
Me.ComponentMetadata.FireError(-1, “”, “InnerException: ” + e.Message, “”, true)
End While
End Try
//Code Ends Here

For logging this error, Use below Code..

Dts.Events.FireError(-1, “”, “Your Error Message: ” + e.ToString(), “”, 0)

Here ToString() will fetch all the InnerException Messages.

June 5, 2008

SSIS: Logging from script task

This post will show you how to log from the script task.
While logging from inbuilt log system this method helps you to log the exact details of the event in the script task.

using System;
using System.Data;
using System.Math;
using Microsoft.SqlServer.Dts.Runtime;

public class ScriptMain
{

public void Main()
{
//
int rowsProcessed = 100;
byte[] emptyBytes = new byte[0];

try
{
Dts.Log(“Rows processed: “+rowsProcessed.ToString, 0, emptyBytes);
Dts.TaskResult = Dts.Results.Success;
}
catch(Exception ex)
{
//An error occurred.
Dts.Events.FireError(0, “Script Task Example”, ex.Message+ControlChars.CrLf+ex.StackTrace, String.Empty, 0);
Dts.TaskResult = Dts.Results.Failure;
}

}

June 4, 2008

SSIS: Send SMTP mail from Script task

Here i will show how to send SMTP email from a script task rather than the SMTP Mail Task.

Imports System
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail
Imports System.Net

Public Class ScriptMain
Public Sub Main()
Dim myHtmlMessage As MailMessage
Dim mySmtpClient As SmtpClient

myHtmlMessage=New MailMessage(name@helpindotnet.blogspot.com,
“name@helpindotnet.blogspot.com”, “Subject”, “body”)
mySmtpClient = New SmtpClient(“192.168.3.75”)
mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
mySmtpClient.Send(myHtmlMessage)
Dts.TaskResult = Dts.Results.Success

Dts.TaskResult = Dts.Results.Success
End Sub
End Class

June 2, 2008

Remove Special Character from XML string using SQL Server

Create a function in MSSQL to remove all special characters from XML.

ALTER FUNCTION [dbo].[RemoveSpChar]
(
— Add the parameters for the function here
@sInput varchar(MAX)=”
)
RETURNS varchar(MAX)
AS
BEGIN
— Declare the return variable here
DECLARE @sOutput Varchar(MAX),
@iIndex int,
@iLength int,
@sChar varchar(1),
@iASCII int,
@iLen int,
@iRem int

set @sInput= ltrim(rtrim(@sInput))
set @iLength = len(@sInput)
set @iIndex =1
set @sOutput=”

while @iIndex <= @iLength
begin
set @sChar=substring(@sInput,@iIndex,1)
set @iASCII=ascii(@sChar)

if ((@iASCII>=48 and @iASCII<=57) or (@iASCII>=65 and @iASCII<=90) or (@iASCII>=97 and @iASCII<=122) )
set @sOutput=@sOutput+@sChar
–return @sChar + ‘ – ‘ + convert(varchar,@iASCII)
set @iIndex =@iIndex +1
end

if len(@sOutput)>17
set @sOutput=substring(@sOutput,1,17)
else if len(@sOutput)<6
begin
set @iLen=len(@sOutput)
set @iRem=6-@iLen

set @sOutput=substring(@sOutput + replicate(‘0’,@iRem),1,6)

end

— Return the result of the function
RETURN @sOutput

END

May 7, 2008

– Fetch XML (xmldatadocument) from database

Filed under: asp.net c#, sql, xml — Tags: , , , , — Admin @ 1:19 pm

This post shows you how to return XmlDataDocument from the database.
This helps you to fetch the xml data created in sql and use in asp.net.
If you are familiar how to get xml data from sql then this post helps you to fetch the xml data in XmlDataDocument.

//Code Starts Here

// http://helpindotnet.blogspot.com/
// https://helpindotnet.wordpress.com

public XmlDataDocument getauditnames(string prefixText, int count)
{

XmlDataDocument xml = new XmlDataDocument();
SqlConnection con = new SqlConnection(” <<Connection String >>”);
SqlCommand com = new SqlCommand(“<<Query>> For XML PATH (‘Path’), root(‘Root’)”, con);
//The key to this step is the FOR XML PATH(###), ROOT(###) part. This tells SQL Server to return XML with each row having the element name //”Path” of and the root of the XML document to be ROOT
con.Open();
XmlReader xdr = com.ExecuteXmlReader();
xml.Load(xdr);
xdr.Close();
con.Close();
return xml;
}
//Code Ends Here

May 6, 2008

– GZIP Compress a file using Asp.net and C#.

Filed under: asp.net c# — Tags: , , , — Admin @ 8:58 am

<span style=”font-weight:bold;”>Here I will show you the easiest way to compress and Decompress (Gzip) a file using Asp.net with c#.
You can compress any file with this method.</span>

//Code Starts here

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.IO.Compression;

/*
* http://helpindotnet.blogspot.com/
*/

namespace ConsoleApplication1
{
class GZipTest
{
private const int buffer_size = 100;

public static int ReadAllBytesFromStream(Stream stream, byte[] buffer)
{
// Use this method is used to read all bytes from a stream.
int offset = 0;
int totalCount = 0;
while (true)
{
int bytesRead = stream.Read(buffer, offset, buffer_size);
if (bytesRead == 0)
{
break;
}
offset += bytesRead;
totalCount += bytesRead;
}
return totalCount;
}

public static bool CompareData(byte[] buf1, int len1, byte[] buf2, int len2)
{
// Use this method to compare data from two different buffers.
if (len1 != len2)
{
Console.WriteLine(“Number of bytes in two buffer are different {0}:{1}”, len1, len2);
return false;
}

for (int i = 0; i < len1; i++)
{
if (buf1[i] != buf2[i])
{
Console.WriteLine(“byte {0} is different {1}|{2}”, i, buf1[i], buf2[i]);
return false;
}
}
Console.WriteLine(“All bytes compare.”);
return true;
}

public static void GZipCompressDecompress(string filename)
{
Console.WriteLine(“Test compression and decompression on file {0}”, filename);
FileStream infile;
try
{
// Open the file as a FileStream object.
infile = new FileStream(filename, FileMode.Open, FileAccess.Read, FileShare.Read);
byte[] buffer = new byte[infile.Length];
// Read the file to ensure it is readable.
int count = infile.Read(buffer, 0, buffer.Length);
if (count != buffer.Length)
{
infile.Close();
Console.WriteLine(“Test Failed: Unable to read data from file”);
return;
}
infile.Close();
FileStream fs = new FileStream(filename+”.gz”, FileMode.Append);
MemoryStream ms = new MemoryStream();
// Use the newly created memory stream for the compressed data.
GZipStream compressedzipStream = new GZipStream(fs, CompressionMode.Compress, true);
Console.WriteLine(“Compression”);
compressedzipStream.Write(buffer, 0, buffer.Length);
// Close the stream.
compressedzipStream.Close();
Console.WriteLine(“Original size: {0}, Compressed size: {1}”, buffer.Length, fs.Length);

ms.WriteTo(fs);

// Reset the memory stream position to begin decompression.
ms.Position = 0;
GZipStream zipStream = new GZipStream(ms, CompressionMode.Decompress);
Console.WriteLine(“Decompression”);
byte[] decompressedBuffer = new byte[buffer.Length + buffer_size];
// Use the ReadAllBytesFromStream to read the stream.
int totalCount = GZipTest.ReadAllBytesFromStream(zipStream, decompressedBuffer);
Console.WriteLine(“Decompressed {0} bytes”, totalCount);

if (!GZipTest.CompareData(buffer, buffer.Length, decompressedBuffer, totalCount))
{
Console.WriteLine(“Error. The two buffers did not compare.”);
}
zipStream.Close();
} // end try
catch (InvalidDataException)
{
Console.WriteLine(“Error: The file being read contains invalid data.”);
}
catch (FileNotFoundException)
{
Console.WriteLine(“Error:The file specified was not found.”);
}
catch (ArgumentException)
{
Console.WriteLine(“Error: path is a zero-length string, contains only white space, or contains one or more invalid characters”);
}
catch (PathTooLongException)
{
Console.WriteLine(“Error: The specified path, file name, or both exceed the system-defined maximum length. For example, on Windows-based platforms, paths must be less than 248 characters, and file names must be less than 260 characters.”);
}
catch (DirectoryNotFoundException)
{
Console.WriteLine(“Error: The specified path is invalid, such as being on an unmapped drive.”);
}
catch (IOException)
{
Console.WriteLine(“Error: An I/O error occurred while opening the file.”);
}
catch (UnauthorizedAccessException)
{
Console.WriteLine(“Error: path specified a file that is read-only, the path is a directory, or caller does not have the required permissions.”);
}
catch (IndexOutOfRangeException)
{
Console.WriteLine(“Error: You must provide parameters for MyGZIP.”);
}
}

public static void Main(string[] args)
{

Console.WriteLine(“Start”);

GZipCompressDecompress(“c:\\Test1.txt”);

}
}
}

//Code Ends Here

Older Posts »

Blog at WordPress.com.