Programmatic access to sqldatasource dataset

You can certainly do something like this. I programmatically created an SqlDataSource,
called the Select Method on it, retrieved the DataView, and converted the results to
a DataTable that I can manipulate as I see fit.



string connectionString = "...Northwind Connection String...";
string selectSql = "SELECT [CategoryID], [CategoryName] FROM [Categories]";

DataSourceSelectArguments args = new DataSourceSelectArguments();

SqlDataSource dataSource = new SqlDataSource(connectionString, selectSql);
DataView view = (DataView)dataSource.Select(args);

DataTable table = view.ToTable();

Info Class

public class CustomerInfo
{
private int _customerid;
private string _firstname;
private string _surname;
private string _customeraddress;
private string _customertelephone;
private string _customermobile;
private string _customeremail;
private int _addressproofid;
private string _addressproofname;
private int _idproofid;
private string _idproofname;
private Boolean _cflag;

public int CustomerId
{
get { return _customerid; }
set { _customerid = value; }
}
public string FirstName
{
get { return _firstname; }
set { _firstname = value; }
}
public string SurName
{
get { return _surname; }
set { _surname = value; }
}
public string CustomerAddrees
{
get { return _customeraddress; }
set { _customeraddress = value; }
}
public string CustomerTelephone
{
get { return _customertelephone; }
set { _customertelephone = value; }
}
public string CustomerMobile
{
get { return _customermobile; }
set { _customermobile = value; }
}
public string CustomerEmail
{
get{return _customeremail;}
set{_customeremail=value;}
}
public int AddressProofId
{
get{return _addressproofid;}
set{_addressproofid=value;}
}
public string AddressProofName
{
get{return _addressproofname;}
set{_addressproofname=value;}
}
public int IdProofId
{
get{return _idproofid;}
set{_idproofid=value;}
}
public string IdProofName
{
get { return _idproofname; }
set { _idproofname = value; }
}

public Boolean CFlag
{
get { return _cflag; }
set { _cflag = value; }
}

}

SP Class

using System.Data.SqlClient;
using System.Windows.Forms;


public class CustomerSP:DBConnection
{
public void AddDetails(CustomerInfo cinfo)
{
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand("AddCustomer", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter par = new SqlParameter();
par = cmd.Parameters.Add("@customerid", SqlDbType.Int);
par.Value = cinfo.CustomerId;
par = cmd.Parameters.Add("@firstname", SqlDbType.VarChar);
par.Value = cinfo.FirstName;
par = cmd.Parameters.Add("@surname", SqlDbType.VarChar);
par.Value = cinfo.SurName;
par = cmd.Parameters.Add("@customeraddress", SqlDbType.VarChar);
par.Value = cinfo.CustomerAddrees;
par = cmd.Parameters.Add("@customertelephone", SqlDbType.VarChar);
par.Value = cinfo.CustomerTelephone;
par = cmd.Parameters.Add("@customermobile", SqlDbType.VarChar);
par.Value = cinfo.CustomerMobile;
par = cmd.Parameters.Add("@customeremail", SqlDbType.VarChar);
par.Value = cinfo.CustomerEmail;
par = cmd.Parameters.Add("@addressproofid", SqlDbType.Int);
par.Value = cinfo.AddressProofId;
par = cmd.Parameters.Add("@addressproofname", SqlDbType.VarChar);
par.Value = cinfo.AddressProofName;
par = cmd.Parameters.Add("@idproofid", SqlDbType.Int);
par.Value = cinfo.IdProofId;
par = cmd.Parameters.Add("@idproofname", SqlDbType.VarChar);
par.Value = cinfo.IdProofName;
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
}

public DataTable ViewCustomerDetails(int customerid)
{
DataTable dt = new DataTable();
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlDataAdapter da = new SqlDataAdapter("CustomerDetailsView", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter par = new SqlParameter();
par = da.SelectCommand.Parameters.Add("@customerid", SqlDbType.Int);
par.Value = customerid;
da.Fill(dt);
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
return dt;
}

public DataTable ViewCustomerDetailsByIdInAdminSide(int customerid)
{
DataTable dt = new DataTable();
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlDataAdapter da = new SqlDataAdapter("CustomerDetailsViewById", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter par = new SqlParameter();
par = da.SelectCommand.Parameters.Add("@customerid", SqlDbType.Int);
par.Value = customerid;
da.Fill(dt);
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
return dt;
}
public long CustomerIdGenerate()
{
long newnumber = 0;
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}

SqlCommand cmd = new SqlCommand("CustomerIdGenerate", con);
cmd.CommandType = CommandType.StoredProcedure;

SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
newnumber = int.Parse(dr["Customer Id"].ToString());
}
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
return newnumber;
}
public int CustomerCheck(int customerid)
{
int count = 0;
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand("CustomerCheck", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter par = new SqlParameter();
par = cmd.Parameters.Add("@customerid", SqlDbType.Int);
par.Value = customerid;
count = int.Parse(cmd.ExecuteScalar().ToString());
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
return count;
}


public int CustomerAddressProofNameCheck(string addressproofname)
{
int count = 0;
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand("CustomerAddressProofNameCheck", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter par = new SqlParameter();
par = cmd.Parameters.Add("@addressproofname", SqlDbType.VarChar);
par.Value = addressproofname;
count = int.Parse(cmd.ExecuteScalar().ToString());
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
return count;
}


public int CustomerIdProofNameCheck(string idproofname)
{
int count = 0;
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand("CustomerIdProofNameCheck", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter par = new SqlParameter();
par = cmd.Parameters.Add("@idproofname", SqlDbType.VarChar);
par.Value = idproofname;
count = int.Parse(cmd.ExecuteScalar().ToString());
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
return count;
}
public DataTable ViewAllCustomerDetails()
{
DataTable dt = new DataTable();
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlDataAdapter da = new SqlDataAdapter("CustomerDetailsViewAll", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.Fill(dt);
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
return dt;
}
public DataTable CustomerDetailsPrint(int customerid)
{
DataTable dt = new DataTable();
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlDataAdapter adapter = new SqlDataAdapter("CustomerDetailsPrint", con);
adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter par = new SqlParameter();
par = adapter.SelectCommand.Parameters.Add("@customerid", SqlDbType.Int);
par.Value = customerid;
adapter.Fill(dt);
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
return dt;
}
public void EditDetails(CustomerInfo cinfo)
{
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand("EditCustomer", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter par = new SqlParameter();
par = cmd.Parameters.Add("@customerid", SqlDbType.Int);
par.Value = cinfo.CustomerId;
par = cmd.Parameters.Add("@firstname", SqlDbType.VarChar);
par.Value = cinfo.FirstName;
par = cmd.Parameters.Add("@surname", SqlDbType.VarChar);
par.Value = cinfo.SurName;
par = cmd.Parameters.Add("@customeraddress", SqlDbType.VarChar);
par.Value = cinfo.CustomerAddrees;
par = cmd.Parameters.Add("@customertelephone", SqlDbType.VarChar);
par.Value = cinfo.CustomerTelephone;
par = cmd.Parameters.Add("@customermobile", SqlDbType.VarChar);
par.Value = cinfo.CustomerMobile;
par = cmd.Parameters.Add("@customeremail", SqlDbType.VarChar);
par.Value = cinfo.CustomerEmail;
par = cmd.Parameters.Add("@addressproofid", SqlDbType.Int);
par.Value = cinfo.AddressProofId;
par = cmd.Parameters.Add("@addressproofname", SqlDbType.VarChar);
par.Value = cinfo.AddressProofName;
par = cmd.Parameters.Add("@idproofid", SqlDbType.Int);
par.Value = cinfo.IdProofId;
par = cmd.Parameters.Add("@idproofname", SqlDbType.VarChar);
par.Value = cinfo.IdProofName;
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
}
public int CustomerCheckByName(string customername)
{
int count = 0;
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand("CustomerCheckByName", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter par = new SqlParameter();
par = cmd.Parameters.Add("@customername", SqlDbType.VarChar);
par.Value = customername;
count = int.Parse(cmd.ExecuteScalar().ToString());
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
return count;
}
public DataTable GetCustomerBySearch(string customername)
{
DataTable dt = new DataTable();
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlDataAdapter da = new SqlDataAdapter("GetCustomerBySearch", con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter par = new SqlParameter();
par = da.SelectCommand.Parameters.Add("@customername", SqlDbType.VarChar);
par.Value = customername;
da.Fill(dt);
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
return dt;
}
public int CustomerCount()
{
int count = 0;
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}

SqlCommand cmd = new SqlCommand("CustomerCount", con);
cmd.CommandType = CommandType.StoredProcedure;

count = int.Parse(cmd.ExecuteScalar().ToString());
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
return count;
}
public void CustomerDelete(int customerid)
{
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand("CustomerDelete", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter par = new SqlParameter();
par = cmd.Parameters.Add("@customerid", SqlDbType.Int);
par.Value = customerid;

cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
}
public DataTable TransactionViewByCustomerId(int customerid)
{
DataTable dt=new DataTable();
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlDataAdapter adapter=new SqlDataAdapter("TransactionViewByCustomerId", con);
adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
SqlParameter par = new SqlParameter();
par = adapter.SelectCommand.Parameters.Add("@customerid", SqlDbType.Int);
par.Value = customerid;
adapter.Fill(dt);
con.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
return dt;
}
}

Connection Class

using System.Data.SqlClient;

public class DBConnection
{
protected static SqlConnection con = new SqlConnection();
public DBConnection()
{
if (con.State == ConnectionState.Open)
{
con.Close();
}
con = new SqlConnection(@"Data Source=STUDENTSERVER;Initial Catalog=DBEuroIndia;User ID=sa;password='cool';");
con.Open();
}
}

Regular Expression For +ve integer

Regular Expression For +ve integer
..................................

^0*[1-9][0-9]*$