Generate query from excel sheet

How to write query from excel sheet.

it is a very simple method to write query from excel sheet.
for example.

="insert into tablename(code,startdate,enddate,miniamt,amount,type,active) values('"&A1&"','2010-5-24','2011-5-23',300,30,'$',1)"

green text show you this is excel sheet column name.

Error: Failed to access IIS metabase

This is error occurs when you have installed IIS after installing Microsoft Visual Studio. So to overcome this error you can either run aspnet_regiis utility or re-install .net framework.

If you have installed IIS over .net framework 2.0 then you have to run this utility from following folder.

C:\Windows\Microsoft.Net\Framework\v2.0.50727\aspnet_regiis -i

In some cases this error occurs user account under which web application is running does not have proper permission on the IIS metabase. In that case run following commond.

aspnet_regiis -ga

Import contact from mail service using asp.net with php

First Download openinviter from http://openinviter.com/download.php and edit example.php to adjust it for your needs

Just like, below php file (example).


include('openinviter.php');
$inviter=new OpenInviter();
$oi_services=$inviter->getPlugins();
if (isset($_POST['provider_box']))
{
if (isset($oi_services['email'][$_POST['provider_box']])) $plugType='email';
elseif (isset($oi_services['social'][$_POST['provider_box']])) $plugType='social';
else $plugType='';
}
else $plugType = '';
function ers($ers)
{
if (!empty($ers))
{
//$contents="

"; foreach ($ers as $key=>$error) $contents.="{$error} "; //$contents.="

";
return $contents;
}
}

function oks($oks)
{
if (!empty($oks))
{
$contents="

"; foreach ($oks as $key=>$msg) $contents.="{$msg} "; $contents.="

";
return $contents;
}
}

if (!empty($_POST['step'])) $step=$_POST['step'];
else $step='get_contacts';

$ers=array();$oks=array();$import_ok=false;$done=false;
if ($_SERVER['REQUEST_METHOD']=='GET')
{
$plugType='email';
if ($step=='get_contacts')
{
if (empty($_GET['email_box']))
$ers['email']="Email missing !";
if (empty($_GET['password_box']))
$ers['password']="Password missing !";
if (empty($_GET['provider_box']))
$ers['provider']="Provider missing !";
if (count($ers)==0)
{
$inviter->startPlugin($_GET['provider_box']);
$internal=$inviter->getInternalError();
if ($internal)
$ers['inviter']=$internal;
elseif (!$inviter->login($_GET['email_box'],$_GET['password_box']))
{
$internal=$inviter->getInternalError();
$ers['login']=($internal?$internal:"Login failed. Please check the email and password you have provided and try again later !");
}
elseif (false===$contacts=$inviter->getMyContacts())
$ers['contacts']="Unable to get contacts !";
else
{
$import_ok=true;
$step='send_invites';
$_GET['oi_session_id']=$inviter->plugin->getSessionID();
$_GET['message_box']='';
}
}
}
elseif ($step=='send_invites')
{
if (empty($_POST['provider_box'])) $ers['provider']='Provider missing !';
else
{
$inviter->startPlugin($_POST['provider_box']);
$internal=$inviter->getInternalError();
if ($internal) $ers['internal']=$internal;
else
{
if (empty($_POST['email_box'])) $ers['inviter']='Inviter information missing !';
if (empty($_POST['oi_session_id'])) $ers['session_id']='No active session !';
if (empty($_POST['message_box'])) $ers['message_body']='Message missing !';
else $_POST['message_box']=strip_tags($_POST['message_box']);
$selected_contacts=array();$contacts=array();
$message=array('subject'=>$inviter->settings['message_subject'],'body'=>$inviter->settings['message_body'],'attachment'=>"\n\rAttached message: \n\r".$_POST['message_box']);
if ($inviter->showContacts())
{
foreach ($_POST as $key=>$val)
if (strpos($key,'check_')!==false)
$selected_contacts[$_POST['email_'.$val]]=$_POST['name_'.$val];
elseif (strpos($key,'email_')!==false)
{
$temp=explode('_',$key);$counter=$temp[1];
if (is_numeric($temp[1])) $contacts[$val]=$_POST['name_'.$temp[1]];
}
if (count($selected_contacts)==0) $ers['contacts']="You haven't selected any contacts to invite !";
}
}
}
if (count($ers)==0)
{
$sendMessage=$inviter->sendMessage($_POST['oi_session_id'],$message,$selected_contacts);
$inviter->logout();
if ($sendMessage===-1)
{
$message_footer="\r\n\r\nThis invite was sent using OpenInviter technology.";
$message_subject=$_POST['email_box'].$message['subject'];
$message_body=$message['body'].$message['attachment'].$message_footer;
$headers="From: {$_POST['email_box']}";
foreach ($selected_contacts as $email=>$name)
mail($email,$message_subject,$message_body,$headers);
$oks['mails']="Mails sent successfully";
}
elseif ($sendMessage===false)
{
$internal=$inviter->getInternalError();
$ers['internal']=($internal?$internal:"There were errors while sending your invites.
Please try again later!");
}
else $oks['internal']="Invites sent successfully!";
$done=true;
}
}
}
else
{
$_POST['email_box']='';
$_POST['password_box']='';
$_POST['provider_box']='';
}


if (!$done)
{
if ($step=='get_contacts')
{

}
else{}

}
if (!$done)
{
if ($step=='send_invites')
{
if ($inviter->showContacts())
{

if (count($contacts)==0){}
else
{
$odd=true;$counter=0;
foreach ($contacts as $email=>$name)
{
$counter++;
if ($odd) {}
$contents.= "{$email}".",";

$odd=!$odd;
}

}

}
}
}

print_r($contents);
?>


Then create one class like that.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.IO;
using System.Net;
using System.Text;
using System.Data;

///

/// Summary description for ImportsContacts
///

public class ImportsContacts
{
public ImportsContacts()
{
//
// TODO: Add constructor logic here
//
}

public static DataTable GetDataTableFromArray(string url, string provider, string email, string pass)
{

DataTable dt = new DataTable();
///get all contact from url as array format
///split into array
object[] array = TinyEAIPostRequest(+ url +"/example.php?step=get_contacts&provider_box=?step=get_contacts&provider_box=" + provider + "&email_box=" + email + "&password_box=" + pass , string.Empty).Split(',');
dt.Columns.Add(new DataColumn("EmailID"));

for (int i = 0; i <>
{
DataRow dr = dt.NewRow();
dr["EmailID"] = array[i];
dt.Rows.Add(dr);
}
return dt;

}
public static string TinyEAIPostRequest(string strURL, string strRequest)
{

HttpWebResponse objHttpWebResponse = null;
UTF8Encoding encoding;
string strResponse = "";

HttpWebRequest objHttpWebRequest;
objHttpWebRequest = (HttpWebRequest)WebRequest.Create(strURL);
objHttpWebRequest.ContentType = "application/x-www-form-urlencoded";
objHttpWebRequest.PreAuthenticate = true;

objHttpWebRequest.Method = "GET";

//Prepare the request stream
if (strRequest != null && strRequest != string.Empty)
{
encoding = new UTF8Encoding();
Stream objStream = objHttpWebRequest.GetRequestStream();
Byte[] Buffer = encoding.GetBytes(strRequest);
// Post the request
objStream.Write(Buffer, 0, Buffer.Length);
objStream.Close();
}
objHttpWebResponse = (HttpWebResponse)objHttpWebRequest.GetResponse();

encoding = new UTF8Encoding();
StreamReader objStreamReader = new StreamReader(objHttpWebResponse.GetResponseStream(), encoding);
strResponse = objStreamReader.ReadToEnd();
objHttpWebResponse.Close();

objHttpWebRequest = null;

return strResponse;
}
}

Then
Call example.php file from your aspx file.
see below code how to call php file using asp.net

DataTable dt = ImportsContacts.GetDataTableFromArray(url +"/example.php", ddlmailoption.SelectedValue, txtusername.Text, txtpass.Text);//GContactsImport.GetGmailContacts(App_Name, txtusername.Text, txtpass.Text);
repgooglecontact.DataSource = dt;
repgooglecontact.DataBind();


I hope you will get good result and help to you.

Convert Array To DataTable in .NET

public static DataTable GetDataTableFromArray(object[] array)

{

DataTable dataTable = new DataTable();

dataTable.LoadDataRow(array, true);//Pass array object to LoadDataRow method

return dataTable;

}

Export Data into CSV file using Asp.net

How to use below code.

First create one default.aspx page and add below code into code behind file.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string[] mylist = new string[5]; //Declaring the string array;

//Assigning values to each element in the array
mylist[0] = "Hello";
mylist[1] = "how";
mylist[2] = "are";
mylist[3] = "you";
mylist[4] = "?";

//Creating a new List of type
List StringtoList = new List(mylist.Length);

//AddRange is a method of List objects that enables the conversion.
//We just need to pass the reference to the array.
StringtoList.AddRange(mylist);

CSVExporter.WriteToCSV(StringtoList);
}
}


Then create one CSVExporter.cs file into app_code and add below code into class file.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;

///

/// Summary description for CSVExporter
///

public class CSVExporter
{
public static void WriteToCSV(List personList)
{
string attachment = "attachment; filename=PersonList.csv";
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.AddHeader("content-disposition", attachment);
HttpContext.Current.Response.ContentType = "text/csv";
HttpContext.Current.Response.AddHeader("Pragma", "public");
WriteColumnName();
foreach (String person in personList)
{
WriteUserInfo(person);
}
HttpContext.Current.Response.End();
}

private static void WriteUserInfo(String person)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(person);
//AddComma(person.Name, stringBuilder);
//AddComma(person.Family, stringBuilder);
//AddComma(person.Age.ToString(), stringBuilder);
//AddComma(string.Format("{0:C2}", person.Salary), stringBuilder);
HttpContext.Current.Response.Write(stringBuilder.ToString());
HttpContext.Current.Response.Write(Environment.NewLine);
}

private static void AddComma(string value, StringBuilder stringBuilder)
{
stringBuilder.Append(value.Replace(',', ' '));
stringBuilder.Append(", ");
}

private static void WriteColumnName()
{
string columnNames = "Name, Family, Age, Salary";
HttpContext.Current.Response.Write(columnNames);
HttpContext.Current.Response.Write(Environment.NewLine);
}
}

Then press f5 and you will get result.