Launch of WebSite January 29, 2009
Posted by mudassarkhan in Uncategorized.add a comment
I have launched my website
I have closed this blog and all the articles will be available on the site.
Thanks all for the wonderful comments and appreciation.
Sending Email using VB January 16, 2009
Posted by mudassarkhan in VB.Net.Tags: Email using VB, Send Email using VB, smtp, System.Net, System.Net.Mail, VB.Net, VB.Net Email
add a comment
Here I have discussed Sending Mails using VB.Net
First Import these Name Spaces
Imports System.Net
Imports System.Net.Mail
See the function below
Public Function SendEmail(ByVal strTo As String, ByVal strFrom As String, ByVal strSubject As String, ByVal strBody As String, ByVal strAttachmentPath As String, ByVal IsBodyHTML As Boolean) As Boolean
Dim arrToArray As String()
Dim splitter As Char = “;”
‘Loop through the recepient list and separate them
‘based on semi colon
arrToArray = strTo.Split(splitter)
Dim mm As MailMessage = New MailMessage()
mm.From = New MailAddress(strFrom)
mm.Subject = strSubject
mm.Body = strBody
‘True – HTML Body
‘False – Text Body
mm.IsBodyHtml = IsBodyHTML
‘Add the recepient email Addresses
For Each s As String In arrToArray
mm.To.Add(New MailAddress(s))
Next
‘Add Attachment
mm.Attachments.Add(New Attachment(strAttachmentPath))
Dim smtp As SmtpClient = New SmtpClient()
Try
‘Your SMTP Server
smtp.Host = “Your smtp server”
‘SSL Settings depending on your Server
smtp.EnableSsl = True / False
‘Reply To Address (Optional)
mm.ReplyTo = New MailAddress(“your reply to email”)
‘Creadentials for the Server
Dim NetworkCred As NetworkCredential
=New System.Net.NetworkCredential()
‘Your Email
NetworkCred.UserName = ” sender@abc.com”
‘Your Password
NetworkCred.Password = “your password”
smtp.UseDefaultCredentials = True
smtp.Credentials = NetworkCred
‘Port No of the Server
smtp.Port = Your Port No ‘ Port No
smtp.Send(mm)
Return True
Catch
Return False
Finally
smtp = Nothing
mm = Nothing
End Try
End Function
To call this function
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
SendEmail(“sender@abc.com”, “recepient@abc.com”, “Subject”,
“Body”, “C:\MyDoc.doc”, True)
End Sub
Also these are the settings for sending email using Gmail
Host – smtp.gmail.com
SSL – true
Port No 465, 587 or just comment Port No
UserName – Gmail Email
Password Gmail Password
Job Schedular SQL Server 2000 January 13, 2009
Posted by mudassarkhan in SQL Server.Tags: Batch Job. Job Scheduling, Schedular, SQL Server 2000
4 comments
Here I am explaining how to create a Scheduled Batch Job in SQL Server 2000.
The Steps to Create a Batch Job in SQL Server 2000 are described below.
Open SQL Server Enterprise Manager. Expand the Management node.
Right Click Job from the Sub Nodes and Select New Job from the context Menu.
For details refer Figure 1.0
Figure 1.0
In the General Tab, you will have Fill the following Information
1. Name of the Batch Job
2. Category of the Batch Job
3. Owner of the Batch Job
4. Description of the Batch Job. If any
For Details refer Figure 2.0
Figure 2.0
The second Tab on the New Job window is Steps. Here you have to Click on New Step which will open the New Job Step window. You will need to fill the following details.
- Step Name – Name of the Job Step
- Type – Type of the step. Since its a Stored Procedure I have Selected TSQL.
- Database – The Database on which the stored procedure or Query needs to run
- Command – The SQL Query you want to execute.
For Details refer Figure 3.0
Figure 3.0
For more details refer Figure 4.0
The Third Tab is for Scheduling the Batch Job. Click new a New Jon Schedule Window will open. In that Window, give name to the Job Schedule. And select Recurring Schedule Type since the job will run on daily basis.
Figure 4
After you select Recurring Radio Button Click on Change Button. Set the Schedule Settings
As given in Figure 5
Figure 5
6. Finally, to delete a SQL Job you can do the following as depicted in Figure 6
Figure 6
When you click on New Job a Window will open. It has four Tabs. The First Tab is General
EXPORT DATA TO EXCEL ADO.NET January 12, 2009
Posted by mudassarkhan in ADO.Net, C#.Tags: .nET, ACE, ADO.Net, C# Excel, Excel, Excel 2007, Export To Excel, insert data in Excel, oledb
2 comments
Here I will explain How to insert data in Excel files using ADO.Net
First comes the Excel 97-2003 format (*.XLS)
For this Microsoft OLEDB Jet Driver will be used. Below is the function to insert Data into Excel Sheet,
just like you do in a Database.
Here’s the Mapping
Database <—————> Excel
Sheet <—————> Table
Here’s the function for Excel 97-2003 Format
//Add New Row To Excel 97-2003 File
private Boolean AddExcelRow(String strFilePath)
{
if (!File.Exists(strFilePath)) return false;
String strExcelConn = “Provider=Microsoft.Jet.OLEDB.4.0;”
+ “Data Source=” + strFilePath + “;”
+ “Extended Properties=’Excel 8.0;HDR=Yes'”;
OleDbConnection connExcel = new OleDbConnection(strExcelConn);
OleDbCommand cmdExcel = new OleDbCommand();
try
{
cmdExcel.Connection = connExcel;
//Check if the Sheet Exists
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema =
connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
connExcel.Close();
DataRow[] dr = dtExcelSchema.Select(“TABLE_NAME = ‘tblData'”);
//if not Create the Sheet
if (dr == null || dr.Length == 0)
{
cmdExcel.CommandText = “CREATE TABLE [tblData]” +
“(ID varchar(10), Name varchar(50));”;
connExcel.Open();
cmdExcel.ExecuteNonQuery();
connExcel.Close();
}
connExcel.Open();
//Add New Row to Excel File
cmdExcel.CommandText = “INSERT INTO [tblData] (ID, Name)” +
” values (‘1’, ‘MAK’)”;
cmdExcel.ExecuteNonQuery();
connExcel.Close();
return true;
}
catch
{
return false;
}
finally
{
cmdExcel.Dispose();
connExcel.Dispose();
}
}
To Insert Data in Excel 2007 (*.XLSX) OLEDB Jet will not work. For that you will have
to use the new Microsoft Ace Driver. Rest all will be the same as we did for Excel 97-2003 format.
Here’s the function
//Add New Row To Excel 2007 File
private Boolean AddExcelRow2007(String strFilePath)
{
if (!File.Exists(strFilePath)) return false;
string strExcelConn = “Provider=Microsoft.ACE.OLEDB.12.0;” +
“Data Source=” + strFilePath + “;Excel 12.0;HDR=YES;”;
OleDbConnection connExcel = new OleDbConnection(strExcelConn);
OleDbCommand cmdExcel = new OleDbCommand();
try
{
cmdExcel.Connection = connExcel;
//Check if the Sheet Exists
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema =
connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
connExcel.Close();
DataRow[] dr =
dtExcelSchema.Select(“TABLE_NAME = ‘tblData'”);
//if not Create the Sheet
if (dr == null || dr.Length == 0)
{
cmdExcel.CommandText = “CREATE TABLE [tblData]” +
” (ID varchar(10), Name varchar(50));”; ;
connExcel.Open();
cmdExcel.ExecuteNonQuery();
connExcel.Close();
}
//Add New Row to Excel File
cmdExcel.CommandText = “INSERT INTO [tblData]”
+ “(ID, Name) values (‘1’, ‘MAK’)”;
connExcel.Open();
cmdExcel.ExecuteNonQuery();
connExcel.Close();
return true;
}
catch
{
return false;
}
finally
{
cmdExcel.Dispose();
connExcel.Dispose();
}
}
To call the function on Button click.
protected void Button1_Click(object sender, EventArgs e)
{
//Excel 97-2003
AddExcelRow(“C:\\Book1.xls”);
//Excel 2007
AddExcelRow2007(“C:\\Book1.xlsx”);
}
Please post your comments.
Check Whether UserName Already Exists January 11, 2009
Posted by mudassarkhan in C#, JavaScript.Tags: AJAX, Check, JavaScript, serverside, UserName, XMLHttp
6 comments
Many times I have seen people asking “How to identify whether username Exists” When it is typed
Hence I decided to explain it here
I will start with a TextBox for User Name and a Label which will display the message.
<asp:TextBox ID=”txtUserName” runat=”server”
onchange = “CheckUser(‘XMLHTTP.aspx?UserName=’ + this.value)”>
</asp:TextBox>
<asp:Label ID=”lblMsg” Text = “UserExists” runat=”server” style =” visibility:hidden “>
</asp:Label>
On the OnChange Event of the TextBox the CheckUser JavaScript dfunction will be called which is explained below.
The label by default is invisible.
Now I will be using JavaScript to make a AJAX Call to the Server and Check whether the User Name exists.
JavaScript Part
To do that you will need to create XML Http Request.
<script type = “text/javascript”>
var xmlhttp;
function CheckUser(url)
{
xmlhttp=null;
if (window.XMLHttpRequest)
{
// code for all new browsers
xmlhttp=new XMLHttpRequest();
}
else if (window.ActiveXObject)
{
// code for IE5 and IE6xmlhttp=
new ActiveXObject(“Microsoft.XMLHTTP”);
}
if (xmlhttp!=null)
{
xmlhttp.onreadystatechange=state_Change;
xmlhttp.open(“GET”,url,true);
xmlhttp.send(null);
}
else
{
alert(“Your browser does not support XMLHTTP.”);
}
}
The Above function creates a XML Http Request and sends it asynchronously to the Server.
And when the Reponse is Received the following function will be called.
function state_Change()
{
if (xmlhttp.readyState==4)
{// 4 = “loaded”
if (xmlhttp.status==200)
{// 200 = OK
// …our code here…
var lblMesg = document.getElementById(“<%=lblMsg.ClientID%>”);
if(xmlhttp.responseText==“true”)
{
lblMesg.style.visibility = “visible”;
}
else
{
lblMesg.style.visibility = “hidden”;
}
}
else
{
alert(“Problem retrieving XML data”);
}
}
}
</script>
CodeBehind Server Side (XMLHttpCSharp.aspx)
protected void Page_Load(object sender, EventArgs e)
{
if (Request.QueryString[“UserName”] != null)
{
String strUserName = Request.QueryString[“UserName”];
//Check userName Here
String strReturnStatus = “false”;
if (CheckUsername(Request.QueryString[“UserName”]) = true)
{
strReturnStatus = “true”;
}
Response.Clear();
Response.Write(strReturnStatus);
Response.End();
}
}
On PageLoad As you can see I am Accepting the UserName in the QueryString and then checking whether username exists using CheckUsername function. If It Exists I return true else false.
Depending on the Response Received the state_change function Shows or Hides the label.
Using Select Stored Procedures ADO.Net January 10, 2009
Posted by mudassarkhan in ADO.Net, C#.Tags: ADO.Net, ASP.Net, C#, DataTable, SQL Server, Stored Procedures
4 comments
Here I will discuss how to get data using SQL Stored Procedure in ADO.Net
Here’s the Function
First import the following Namespaces
using System.Data;
using System.Data.SqlClient;
Now define your Connection string in the Web.Config
<connectionStrings>
<add name=“conString“
connectionString=“DataSource=.\SQLEXPRESS;Database=Northwind;
Integrated Security=true“/>
</connectionStrings>
Here’s the function to call the Select Stored Procedure
public DataTable GetData(SqlCommand cmd)
{
DataTable dt = new DataTable();
String strConnString
=System.Configuration.ConfigurationManager.ConnectionStrings[“conString”].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
cmd.Connection = con;
SqlDataAdapter sda = new SqlDataAdapter();
cmd.CommandType = CommandType.StoredProcedure;
try
{
con.Open();
sda.SelectCommand = cmd;
sda.Fill(dt);
}
catch
{
return dt;
}
finally
{
con.Close();
cmd.Dispose();
sda.Dispose();
}
return dt;
}
If Stored Procedure does not accept any input parameters. Call the above function in the following way.
protected void Button1_Click(object sender, EventArgs e)
{
String strStoreProcName = “sp_GetCustomersByID”;
SqlCommand cmd = new SqlCommand(strStoreProcName);
DataTable dt = GetData(cmd);
}
If Stored Procedure accepts input parameters, you can add in the following way.
protected void Button1_Click(object sender, EventArgs e)
{
String strStoreProcName = “sp_GetCustomersByID”;
SqlCommand cmd = new SqlCommand(strStoreProcName);
cmd.Parameters.AddWithValue(“@CustomerID”, “ALFKI”);
DataTable dt = GetData(cmd);
}
You can bind the Returned DataTable to any Data Bound Controls like GridView, DataGrid, DropDownList and others in the following way
DataTable dt = GetData(cmd);
GridView1.DataSource = dt;
GridView1.DataBind();
Please do comment If you want something to be added in the Blog which you want to learn.
Accessing XML Files Using ADO.Net January 10, 2009
Posted by mudassarkhan in XML.Tags: C#, DataSet, DataTable, Query, Read XML, Write XML, XML
5 comments
Here I will discuss regarding Accessing XML files using ADO.Net
Consider an XML File
<CustomerDataSet>
<CustomerTable>
<Name>John</Name>
<Age>34</Age>
</CustomerTable>
<CustomerTable>
<Name>Smith</Name>
<Age>26</Age>
</CustomerTable>
</CustomerDataSet>
Here CustomerDataSet contains one Table called CustomerTable which has two Columns
Name
Age
And Currently the Table has 2 records.
Now to Read the XML File we will use DataSet ReadXML Property.
Syntax
DataSet ds = new DataSet();
ds.ReadXml(<XML FilePath>) ;
Example
Consider that the file Customers.XML is stored in C:\
DataSet ds = new DataSet();
ds.ReadXml(“C:\\Customers.XML”) ;
After this is done you can bind your DataSet to any data bound controls like
GridView, DataGrid, Repeater, DropDownList and many others using their
DataSource Property.
Example
DataSet ds = new DataSet();
ds.ReadXml(“C:\\Customers.XML”) ;
GridView1.DataSource = ds;
GridView1.DataBind();
To Add New Rows To your XML File you can use the DataSet WriteXML Property
Syntax
Syntax
DataSet ds = new DataSet();
ds.WriteXML(<XML FilePath>) ;
Example
ds.Tables[0].Rows.Add();
ds.Tables[0].Rows[ds.Tables[0].Rows.Count – 1][“Name”] = “MAK”;
ds.Tables[0].Rows[ds.Tables[0].Rows.Count – 1][“Age”] = “23”;
ds.Tables[0].AcceptChanges();
ds.WriteXml(“C:\\Customers.XML”);
To Search for An Existing Record and Update the Record, you will have to Query the DataSet in the following way.
DataRow[] dr = ds.Tables[0].Select(“Name = ‘Smith'”);
if (dr!=null && dr.Length > 0)
{
dr[0][“Age”] = “25”;
}
ds.Tables[0].AcceptChanges();
To Delete a Particular Record.
DataRow[] dr = ds.Tables[0].Select(“Name = ‘MAK'”);
if (dr!=null && dr.Length > 0)
{
ds.Tables[0].Rows.Remove(dr[0]);
}
ds.Tables[0].AcceptChanges();
Thus all the Database Operations such as Add, Update Delete and Search can be performed on XML Files also using DataSet
Execute SQL Select Query using ADO.Net January 10, 2009
Posted by mudassarkhan in C#.add a comment
Here I will discuss how to get data using SQL Select Query in ADO.Net
Here’s the Function
First import the following Namespaces
using System.Data;
using System.Data.SqlClient;
Now define your Connection string in the Web.Config
<connectionStrings>
<add name=“conString“
connectionString=“DataSource=.\SQLEXPRESS;Database=Northwind;Integrated Security=true“/>
</connectionStrings>
Here’s the function
public DataTable GetData(SqlCommand cmd)
{
DataTable dt = new DataTable();
String strConnString
=System.Configuration.ConfigurationManager.ConnectionStrings[“conString”].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
cmd.Connection = con;
SqlDataAdapter sda = new SqlDataAdapter();
cmd.CommandType = CommandType.Text;
try
{
con.Open();
sda.SelectCommand = cmd;
sda.Fill(dt);
}
catch
{
return dt;
}
finally
{
con.Close();
cmd.Dispose();
sda.Dispose();
}
return dt;
}
To call the above function without any parameters
protected void Button1_Click(object sender, EventArgs e)
{
String strQuery = “SELECT * FROM Customers”;
SqlCommand cmd = new SqlCommand(strQuery);
DataTable dt = GetData(cmd);
}
And Now to call the above query using parameters. That I need only those records which have CustomerID as ALFKI. Here I am using Parameterized Queries So that SQL Injections can be avoided.
So the function will be called this way.
protected void Button1_Click(object sender, EventArgs e)
{
String strQuery = “SELECT * FROM Customers WHERE CustomerID=@CustomerID”;
SqlCommand cmd = new SqlCommand(strQuery);
cmd.Parameters.AddWithValue(“@CustomerID”, “ALFKI”);
DataTable dt = GetData(cmd);
}
You can bind the Returned DataTable to any DataBound Controls like Gridview, DataGrid, DropDownList and others in the following way
DataTable dt = GetData(cmd);
GridView1.DataSource = dt;
GridView1.DataBind();
Please do comment If you want something to be added in the Blog which you want to learn.
Send Email Using C# January 9, 2009
Posted by mudassarkhan in C#.Tags: C#, Email, smtp, threading
18 comments
Here I have discussed How to send Emails using C#.
See the function below
public Boolean sendemail(String strTo, string strFrom, string strSubject, string strBody,string strAttachmentPath, bool IsBodyHTML)
{
Array arrToArray;
char[] splitter = { ‘;’ };
arrToArray = strTo.Split(splitter);
MailMessage mm = new MailMessage();
mm.From = new MailAddress(strFrom);
mm.Subject = strSubject;
mm.Body = strBody;
mm.IsBodyHtml = IsBodyHTML;
foreach (string s in arrToArray)
{
mm.To.Add(new MailAddress(s));
}
if (strAttachmentPath != “”)
{
//Add Attachment
Attachment attachFile = new Attachment(strAttachmentPath);
mm.Attachments.Add(attachFile);
}
SmtpClient smtp = new SmtpClient();
try
{
//(4) Send the MailMessage (will use the Web.config settings)
smtp.Host = “YourSmtpServer”;
smtp.EnableSsl = “true/false”; //Depending on server SSL Settings
System.Net.NetworkCredential NetworkCred = new System.Net.NetworkCredential();
NetworkCred.UserName = “Your Email”;
NetworkCred.Password = “Your Password”;
smtp.UseDefaultCredentials = true;
smtp.Credentials = NetworkCred;
smtp.Port = 000;//Specify your port No;
smtp.Send(mm);
mm = null;
smtp = null;
return true;
}
catch (Exception ex)
{
mm = null;
smtp = null;
return false;
}
}
To call this function
protected void Button1_Click(object sender, EventArgs e)
{
//Without Attachment
sendemail(“recepient@abc.com”, “sender@xyz.com”, “Subject”, “Body”, “”, true);
//With Attachment
sendemail(“recepient@abc.com”, “sender@xyz.com”, “Subject”, “Body”, “c:\\mypdf.pdf”, true);}
You can also use threading so that your page does not wait for the email to get send and the users get a quick response and mail is send in background
To do that
protected void Button1_Click(object sender, EventArgs e)
{
System.Threading.Thread threadSendMails;
threadSendMails = new System.Threading.Thread(delegate() { sendemail(“recepient@abc.com”, “sender@xyz.com”, “Subject”, “Body”, “”, true); });
threadSendMails.IsBackground = true;
threadSendMails.Start();
}
Also these are the settings for sending email using Gmail
Host – smtp.gmail.com
SSL – true
PortNo 465 or just comment portNo
UserName – Gmail Email
Password Gmail Password