reading csv   Leave a comment


using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Text;

using System.Data;

using System.IO;

using System.Data.OleDb;

using System.Globalization;

using System.Text.RegularExpressions;

using System.Collections;

 

namespace hashtable

{

class Program1

{

static void Main(string[] args)

{

clsValidateEmails objclsValidateEmails = new clsValidateEmails();

objclsValidateEmails.ValidateEmails();

}

}

 

public class clsValidateEmails

{

Hashtable htEmail = new Hashtable();

public void ValidateEmails()

{

DataTable dtCSV = GetDataTableFromCsv(@”G:\live\xls\hashtable\hashtable\foo.txt”, true);

int nCount = Load_DictionaryTable_LINQ(dtCSV);

if (nCount > 0)

{

//Loop through hash table to check working email ids

}

}

 

 

private int Load_DictionaryTable_LINQ(DataTable dt)

{

Regex regex = new Regex(@”^[A-Za-z](([_\.\-]?[a-zA-Z0-9]+)*)@([A-Za-z0-9]+)(([\.\-]?[a-zA-Z0-9]+)*)\.([A-Za-z]{2,})$”);

var count = (from dr in dt.AsEnumerable()

.Where(dr => regex.IsMatch(dr[“EmailId”].ToString()) == true)

select new

{

a = updateHashTable(dr.Field<Int32>(“SlNo”), dr.Field<String>(“EmailId”))

}).Count();

return (int)count;

}

 

public int updateHashTable(int key, string value)

{

if (htEmail.ContainsKey(key))

{

htEmail[key] = value;

}

else

{

htEmail.Add(key, value);

}

return 1;

}

 

 

private DataTable GetDataTableFromCsv(string path, bool isFirstRowHeader)

{

string header = isFirstRowHeader ? “Yes” : “No”;

 

string pathOnly = Path.GetDirectoryName(path);

string fileName = Path.GetFileName(path);

 

string sql = @”SELECT * FROM [” + fileName + “]”;

 

using (OleDbConnection connection = new OleDbConnection(

@”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + pathOnly +

“;Extended Properties=\”Text;HDR=” + header + “\””))

using (OleDbCommand command = new OleDbCommand(sql, connection))

using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))

{

DataTable dataTable = new DataTable();

dataTable.Locale = CultureInfo.CurrentCulture;

adapter.Fill(dataTable);

return dataTable;

}

}

}

}

 

 

Advertisements

Posted April 17, 2014 by PrashanthChindam in Uncategorized

Reading, manipulating an Xml file using C#.Net(including images)   Leave a comment


Objective:

To develop a windows application to read an xml file; perform insert, search, update, delete operations,navigation of records and display respective images.

Design:

Design the form as above with 1 DataGridView, 1 PictureBox control, 2 OpenFileDialog controls, 4 Labels, 4 TextBoxes and 12 Buttons.

->PictureBox1 Properties:

BorderStyle=Fixed3D; SizeMode=StrechImage

Code:

using System;

using System.Data;

using System.Drawing;

using System.Windows.Forms;

using Microsoft.VisualBasic;

using System.IO;

namespace xmlapp

{

public partial class studdata : Form

{

public studdata()

{

InitializeComponent();

}

DataSet ds; string fpath, fdir, ppath, pname, pdestin; int rno = 0; DataRow drow;

private void loadbtn_Click(object sender, EventArgs e)

{

try

{

openFileDialog1.Filter = “xml|*.xml|all files|*.*”;

DialogResult res = openFileDialog1.ShowDialog();

if (res == DialogResult.OK)

{

dataGridView1.DataSource = null;

clearbtn.PerformClick();

fpath = openFileDialog1.FileName;

ds = new DataSet();

ds.ReadXml(fpath);

//setting primary key inorder to search a record using finding method

ds.Tables[0].Constraints.Add(“pk_sno”, ds.Tables[0].Columns[0], true);

dataGridView1.DataSource = ds.Tables[0];

fdir = fpath.Substring(0, fpath.LastIndexOf(“\\”) + 1);

showdata();

}

}

catch

{ MessageBox.Show(“invalid input”);  }

}

void showdata()

{

if (ds.Tables[0].Rows.Count > 0)

{

pictureBox1.Image = null;

sno_txtbx.Text = ds.Tables[0].Rows[rno][0].ToString();

sname_txtbx.Text = ds.Tables[0].Rows[rno][1].ToString();

course_txtbx.Text = ds.Tables[0].Rows[rno][2].ToString();

fee_txtbx.Text = ds.Tables[0].Rows[rno][3].ToString();

pictureBox1.ImageLocation = fdir + ds.Tables[0].Rows[rno][4].ToString();

}

else

MessageBox.Show(“No records”);

}

private void browsebtn_Click(object sender, EventArgs e)

{

openFileDialog2.InitialDirectory = fdir;

openFileDialog2.Filter = “bmp,jpeg,png|*.bmp;*.jpg;*.png|all files|*.*”;

DialogResult res = openFileDialog2.ShowDialog();

if (res == DialogResult.OK)

{

pictureBox1.ImageLocation = openFileDialog2.FileName;

}

}

private void insertbtn_Click(object sender, EventArgs e)

{

drow = null;

drow = ds.Tables[0].Rows.Find(sno_txtbx.Text);

if (drow == null)

{

drow = ds.Tables[0].NewRow();

drow[0] = sno_txtbx.Text;

drow[1] = sname_txtbx.Text;

drow[2] = course_txtbx.Text;

drow[3] = fee_txtbx.Text;

phototask();

drow[4] = pname;

ds.Tables[0].Rows.Add(drow);

rno = ds.Tables[0].Rows.IndexOf(drow);

ds.WriteXml(fpath);

MessageBox.Show(“record inserted”);

}

else

MessageBox.Show(“sno. should be unique”);

}

void phototask()

{

//finding name of photo ,saving it in directory of xml file with unique name(sno+photo name)

ppath = pictureBox1.ImageLocation;

pname = sno_txtbx.Text + “)” + (ppath.Substring(ppath.LastIndexOf(‘\\’) + 1));//(sno + photo name)

pdestin = fdir + pname;

pictureBox1.Image.Save(pdestin);//saving photo on disk

}

private void searchbtn_Click(object sender, EventArgs e)

{

int n = Convert.ToInt32(Interaction.InputBox(“Enter sno to search:”, “Search”, “10”, 200, 200));

//searching using find method

drow = null;

drow = ds.Tables[0].Rows.Find(n);

if (drow != null)

{

rno = ds.Tables[0].Rows.IndexOf(drow);

sno_txtbx.Text = drow[0].ToString();

sname_txtbx.Text = drow[1].ToString();

course_txtbx.Text = drow[2].ToString();

fee_txtbx.Text = drow[3].ToString();

pictureBox1.ImageLocation = fdir + drow[4];

}

else

MessageBox.Show(“record not found”);

}

private void updatebtn_Click(object sender, EventArgs e)

{

DataRow drow = ds.Tables[0].Rows.Find(sno_txtbx.Text);

if (drow!= null)

{

rno = ds.Tables[0].Rows.IndexOf(drow);

ds.Tables[0].Rows[rno][0] = sno_txtbx.Text;

ds.Tables[0].Rows[rno][1] = sname_txtbx.Text;

ds.Tables[0].Rows[rno][2] = course_txtbx.Text;

ds.Tables[0].Rows[rno][3] = fee_txtbx.Text;

File.Delete(fdir + drow[4]);

phototask();

ds.Tables[0].Rows[rno][4] = pname;

ds.WriteXml(fpath);

MessageBox.Show(“record updated”);

}

else

MessageBox.Show(“no record exists with this sno.”);

}

private void deletebtn_Click(object sender, EventArgs e)

{

DataRow drow = ds.Tables[0].Rows.Find(sno_txtbx.Text);

if (drow!= null)

{

File.Delete(fdir + drow[4]);

ds.Tables[0].Rows.Remove(drow);

ds.WriteXml(openFileDialog1.FileName);

MessageBox.Show(“record deleted”);

rno = 0;

showdata();

}

else

MessageBox.Show(“no record exists with this sno.”);

}

private void firstbtn_Click(object sender, EventArgs e)

{

rno = 0;

showdata();

}

private void prevbtn_Click(object sender, EventArgs e)

{

if (rno > 0)

{

rno–;

showdata();

}

else

{ MessageBox.Show(“first record”); }

}

private void nextbtn_Click(object sender, EventArgs e)

{

if (rno < ds.Tables[0].Rows.Count – 1)

{

rno++;

showdata();

}

else

{ MessageBox.Show(“LastRecord”); }

}

private void lastbtn_Click(object sender, EventArgs e)

{

rno = ds.Tables[0].Rows.Count – 1;

showdata();

}

private void clearbtn_Click(object sender, EventArgs e)

{

sno_txtbx.Text = sname_txtbx.Text = course_txtbx.Text = fee_txtbx.Text = “”;

pictureBox1.Image = null;

}

private void exitbtn_Click(object sender, EventArgs e)

{

this.Close();

}

}

}

——–

Note:

i)In this application, we will search a record by taking input from the InputBox. For this we have to add reference to Microsoft.VisualBasic.

Adding a Reference:

Goto Project Menu

->Add Reference -> select ‘Microsoft.VisualBasic’ from .NET tab.

Inorder to use this we have to include the namespace:

‘using Microsoft.VisualBasic’

ii) xml file and respective jpeg images should be maintained in same folder.

Example for Creating an xml file:

Open notepad and type the following & save it with extension ‘.xml’

——–

<students>

<student>

<sno>10</sno>

<sname>Prashanth</sname>

<course>dotnet</course>

<fee>3500</fee>

<photo>10)prash.jpg</photo>

</student>

<student>

<sno>20</sno>

<sname>Aravind</sname>

<course>oracle</course>

<fee>1000</fee>

<photo>20)aravind.jpg</photo>

</student>

<student>

<sno>30</sno>

<sname>Satyapal</sname>

<course>java</course>

<fee>3000</fee>    <photo>30)satya.jpg</photo>

</student>

<student>

<sno>40</sno>

<sname>Mahender</sname>

<course>php</course>

<fee>2500</fee>

<photo>40)mahi.jpg</photo>

</student>

</students>

Execution:

To execute application click loadxml button and select an xml file

(a folder ‘student data’ consisting xml file is placed in ‘xmlapp.zip’ along with source code)

——————————

Another way of handling xml file:

->performing operations on xml file and saving changes, which includes

creating xml elements(tags), assigning values, appending them to xml root element,…

(for this we have to include namespace ‘using System.Xml’)

Example to insert a record:

XmlDocument doc = new XmlDocument();

doc.Load(openFileDialog1.FileName);

XmlElement root = doc.CreateElement(“student”)

XmlElement sno = doc.CreateElement(“sno”);

XmlElement sname = doc.CreateElement(“sname”);

XmlElement course = doc.CreateElement(“course”);

XmlElement fee = doc.CreateElement(“fee”);

XmlElement photo = doc.CreateElement(“photo”);

sno.InnerText = sno_txtbx.Text;

sname.InnerText = sname_txtbx.Text;

course.InnerText = course_txtbx.Text;

fee.InnerText = fee_txtbx.Text;

phototask();//refer ‘Code’ for phototask()

photo.InnerText == pname;

root.AppendChild(sno);

root.AppendChild(sname);

root.AppendChild(course);

root.AppendChild(fee);

root.AppendChild(photo);

doc.DocumentElement.AppendChild(root);

doc.Save(fdir);

MessageBox.Show(“record inserted”);

 

Posted October 25, 2010 by PrashanthChindam in c#

Inserting & Retrieving records from M.S.Access-2007 using Odbc in C#.net   Leave a comment


Objective:
          To develop a windows application for performing insert, search, update, delete operations & navigation of M.S.Access 2007 records using Odbc connection.

Introduction:
Create a table in M.S.Access 2007 file and populate it.
In our application we use ‘stud.accdb’ (M.S.Access 2007) file, which consists ‘student’ table.
(Note: ‘stud.accdb’ is placed in ‘prash_access07.zip’ along with source code)

Creating and Configuring ODBC Data Source (dsn):

Go to Start Menu -> Control Panel -> Administrative Tools -> Data Sources (ODBC)

Click on ‘Add’ button

-> Select ‘Microsoft Access Driver (*.mdb, *.accdb)’ ->click on ‘Finish’ button.

Give a name to your Data Source
Click on ‘Select’ button and select your M.S.Access 2007 file (*.accdb) -> OK -> OK

Your Data Source Name will be specified in ‘ODBC Data Source Administrator’ window
->Click on ‘OK’ button. 

Thus, your Data Source (dsn) is configured.

Design:

 Design the form as above with a DataGridView, 3 Labels, 3 TextBoxes, 10 buttons.
Introduction to Code:

As we want to use Odbc Connection include the namespace:

‘using System.Data.Odbc’

For accesing records from M.S.Access-2003 file we use ‘Jet’ driver,

But for accesing records from M.S.Access-2003 file we use ‘Ace’ driver.

In this application, we will search a record by taking input from the InputBox. For this we have to add reference to Microsoft.VisualBasic.

Adding a Reference:

Goto Project Menu ->Add Reference -> select ‘Microsoft.VisualBasic’ from .NET tab.

In order to use this we have to include the namespace:

‘using Microsoft.VisualBasic’

Odbc connection string:

Syntax:

OdbcConnection con = new OdbcConnection(“dsn=<Data Source Name>”);

Ex:
OdbcConnection con = new OdbcConnection(“dsn=myaccess07dsn “);

You just need to specify the Data Source Name(dsn) in the Connection String, no need to specify the driver details and path of the file, you dsn will take care of it.
Creating a primary key in the dataTable:

In this app. we use Find() method to search a record, which requires details of primarykey column for database tables; this is provided using statement:

adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

But as we don’t have any primarykey column in M.S.Access table, we have to create a primary key column in datatable.
Ex:
ds.Tables[0].Constraints.Add(“pk_sno”, ds.Tables[0].Columns[0], true);
Pointing to current record in dataTable:

After searching a record, we have to get the index of that record so that we can show next and previous records when we press ‘>>'(next) and ‘<<‘(previous) buttons.
Ex:
rno= ds.Tables[0].Rows.IndexOf(drow);
————-
Code:

using System;

using System.Data;

using System.Windows.Forms;

using System.Data.Odbc;

using Microsoft.VisualBasic;

namespace prash_access07

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        OdbcConnection con;

        OdbcCommand cmd;

        OdbcDataAdapter adapter;

        DataSet ds;

        int rno;

        private void Form1_Load(object sender, EventArgs e)

        {

            con = new OdbcConnection(“dsn=myaccess07dsn”);

            //stud.accdb->access07 filename

            loaddata();

            showdata();

        }

        void loaddata()

        {

            adapter = new OdbcDataAdapter(“select * from student”, con);

            ds = new DataSet();//student-> table name in stud.accdb file

            adapter.Fill(ds, “student”);

            ds.Tables[0].Constraints.Add(“pk_sno”, ds.Tables[0].Columns[0], true);//creating primary key for Tables[0] in dataset

            dataGridView1.DataSource = ds.Tables[0];

        }

        void showdata()

        {

            textBox1.Text = ds.Tables[0].Rows[rno][0].ToString();

            textBox2.Text = ds.Tables[0].Rows[rno][1].ToString();

            textBox3.Text = ds.Tables[0].Rows[rno][2].ToString();

        }

        private void btnFirst_Click(object sender, EventArgs e)

        {

            if (ds.Tables[0].Rows.Count > 0)

            {

                rno = 0;

                showdata();

            }

            else

                MessageBox.Show(“no records”);

        }

        private void btnPrevious_Click(object sender, EventArgs e)

        {

            if (ds.Tables[0].Rows.Count > 0)

            {

                if (rno > 0)

                {

                    rno–;

                    showdata();

                }

                else

                    MessageBox.Show(“First Record”);

            }

            else

                MessageBox.Show(“no records”);

        }

        private void btnNext_Click(object sender, EventArgs e)

        {

            if (ds.Tables[0].Rows.Count > 0)

            {

                if (rno < ds.Tables[0].Rows.Count – 1)

                {

                    rno++;

                    showdata();

                }

                else

                    MessageBox.Show(“Last Record”);

            }

            else

                MessageBox.Show(“no records”);

        }

        private void btnLast_Click(object sender, EventArgs e)

        {

            if (ds.Tables[0].Rows.Count > 0)

            {

                rno = ds.Tables[0].Rows.Count – 1;

                showdata();

            }

            else

                MessageBox.Show(“no records”);

        }

        private void btnInsert_Click(object sender, EventArgs e)

        {

            cmd = new OdbcCommand(“insert into student values(” + textBox1.Text + “,’ ” + textBox2.Text + ” ‘,’ ” + textBox3.Text + ” ‘)”, con);

            con.Open();

            int n = cmd.ExecuteNonQuery();

            con.Close();

            if (n > 0)

            {

                MessageBox.Show(“record inserted”);

                loaddata();

            }

            else

                MessageBox.Show(“insertion failed”);

        }

        private void btnSearch_Click(object sender, EventArgs e)

        {

            int n = Convert.ToInt32(Interaction.InputBox(“Enter sno:”, “Search”, “20”, 200, 200));

            DataRow drow = ds.Tables[0].Rows.Find(n);

            if (drow != null)

            {

                rno = ds.Tables[0].Rows.IndexOf(drow);

                textBox1.Text = drow[0].ToString();

                textBox2.Text = drow[1].ToString();

                textBox3.Text = drow[2].ToString();

            }

            else

                MessageBox.Show(“Record not found”);

        }

        private void btnUpdate_Click(object sender, EventArgs e)

        {

            cmd = new OdbcCommand(“update student set sname='” + textBox2.Text + “‘,course='” + textBox3.Text + “‘ where sno=” + textBox1.Text, con);

            con.Open();

            int n = cmd.ExecuteNonQuery();

            con.Close();

            if (n > 0)

            {

                MessageBox.Show(“Record Updated”);

                loaddata();

            }

            else

                MessageBox.Show(“Update failed”);

        }

        private void btnDelete_Click(object sender, EventArgs e)

        {

            cmd = new OdbcCommand(“delete from student where sno=” + textBox1.Text, con);

            con.Open();

            int n = cmd.ExecuteNonQuery();

            con.Close();

            if (n > 0)

            {

                MessageBox.Show(“Record Deleted”);

                loaddata();

            }

             else

                MessageBox.Show(“Deletion failed”);

        }

         private void btnClear_Click(object sender, EventArgs e)

        {

            textBox1.Text = textBox2.Text = textBox3.Text = “”;

        } 

        private void btnExit_Click(object sender, EventArgs e)

        {

            this.Close();

        }    

    }

}

Posted October 13, 2010 by PrashanthChindam in c#

inserting & retrieving images from database without using stored procedures   Leave a comment


Objective:

          To insert into & retrieve images from SQL server database without using stored procedures and also to perform insert, search, update and delete operations & navigation of records.

Introduction:

As we want to insert images into the database, first we have to create a table in the database, we can use the data type ‘image’ or ‘binary’ for storing the image.

Query for creating table in our application:
create table student(sno int primary key,sname varchar(50),course varchar(50),fee money,photo image)

Design:

 

Design the form as above with 1 PictureBox control, 1 OpenFileDialog control, 4 Labels, 4 TextBoxes and 11 Buttons.

PictureBox1 Properties:
    BorderStyle=Fixed3D; SizeMode=StrechImage

Note that OpenFileDialog control appears below the form(not on the form), which can be used for browsing an image.

 


Introduction to code:

Inorder to communicate with SQL sever database, include the namespace
‘using System.Data.SqlClient’.

In this application, we will search a record by taking input from the InputBox. For this we have to add reference to Microsoft.VisualBasic.

Adding a Reference to ‘Microsoft.VisualBasic’:

Goto Project Menu ->Add Reference -> select ‘Microsoft.VisualBasic’ from .NET tab.
In order to use this reference we have to include the namespace:
‘using Microsoft.VisualBasic’ in the code.

Converting image into binary data:

We can’t store an image directly into the database. For this we have two solutions:

i) To store the location of the image in the database

ii) Converting the image into binary data and insert that binary data into database and convert that back to image while retrieving the records.

If we store the location of an image in the database, and suppose if that image is deleted or moved from that location, we will face problems while retrieving the records. So it is better to convert image into binary data and insert that binary data into database and convert that back to image while retrieving records.

->We can convert an image into binary data using 1. FileStream (or) 2. MemoryStream

1. FileStream uses file location to convert an image into binary data which we may/may not provide during updation of a record.
Ex:
      FileStream fs = new FileStream(openFileDialog1.FileName, FileMode.Open,                       FileAccess.Read);
      byte[] photo_aray = new byte[fs.Length];
      fs.Read(photo_aray, 0, photo_aray.Length);

2. So it is better to use MemoryStream which uses image in the PictureBox to convert an image into binary data.
Ex:
             MemoryStream ms = new MemoryStream();
             pictureBox1.Image.Save(ms, ImageFormat.Jpeg);
             byte[] photo_aray = new byte[ms.Length];
             ms.Position = 0;
             ms.Read(photo_aray, 0, photo_aray.Length);

->Inorder to use FileStream or MemoryStream we have to include the namespace:
‘using System.IO’.

            
OpenFileDialog Control:
We use OpenFileDialog control inorder to browse for the images (photos) to insert into the record.

Loading the constraint details into the dataTable:
In this app. we use Find() method to search a record, which requires details of primarykey column, which can be provided using the statement:
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

Pointing to current record in dataTable:
After searching a record, we have to get the index of that record so that we can navigate the next and previous records.
 Ex:
rno= ds.Tables[0].Rows.IndexOf(drow);
————-
Code:

using System;

using System.Windows.Forms;

using System.Data;

using System.Data.SqlClient;

using System.Drawing;

using System.Drawing.Imaging;

using System.IO;

using Microsoft.VisualBasic;

namespace inserting_imgs

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        SqlConnection con;

        SqlCommand cmd;

        SqlDataAdapter adapter;

        DataSet ds; int rno = 0;

        MemoryStream ms;

        byte[] photo_aray;

        private void Form1_Load(object sender, EventArgs e)

        {

            con = new SqlConnection(“user id=sa;password=123;database=prash”);

            loaddata();

            showdata();

        }

        void loaddata()

        {

            adapter = new SqlDataAdapter(“select sno,sname,course,fee,photo from student”, con);

            adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

            ds = new DataSet(); adapter.Fill(ds, “student”);

        }

        void showdata()

        {

            if (ds.Tables[0].Rows.Count > 0)

            {

                textBox1.Text = ds.Tables[0].Rows[rno][0].ToString();

                textBox2.Text = ds.Tables[0].Rows[rno][1].ToString();

                textBox3.Text = ds.Tables[0].Rows[rno][2].ToString();

                textBox4.Text = ds.Tables[0].Rows[rno][3].ToString();

                pictureBox1.Image = null;

                if (ds.Tables[0].Rows[rno][4] != System.DBNull.Value)

                {

                    photo_aray = (byte[])ds.Tables[0].Rows[rno][4];

                    MemoryStream ms = new MemoryStream(photo_aray);

                    pictureBox1.Image = Image.FromStream(ms);

                }

            }

            else

                MessageBox.Show(“No Records”);

        }

        private void browse_Click(object sender, EventArgs e)

        {

            openFileDialog1.Filter = “jpeg|*.jpg|bmp|*.bmp|all files|*.*”;

            DialogResult res = openFileDialog1.ShowDialog();

            if (res == DialogResult.OK)

            {

                pictureBox1.Image = Image.FromFile(openFileDialog1.FileName);

            }

        }

        private void newbtn_Click(object sender, EventArgs e)

        {

            cmd = new SqlCommand(“select max(sno)+10 from student”, con);

            con.Open();

            textBox1.Text = cmd.ExecuteScalar().ToString();

            con.Close();

            textBox2.Text = textBox3.Text = textBox4.Text = “”;

            pictureBox1.Image = null;

        }

        private void insert_Click(object sender, EventArgs e)

        {

            cmd = new SqlCommand(“insert into student(sno,sname,course,fee,photo) values(” + textBox1.Text + “,'” + textBox2.TabIndex + “‘,'” + textBox3.Text + “‘,” + textBox4.Text + “,@photo)”, con);

            conv_photo();

            con.Open();

            int n = cmd.ExecuteNonQuery();

            con.Close();

            if (n > 0)

            {

                MessageBox.Show(“record inserted”);

                loaddata();

            }

            else

                MessageBox.Show(“insertion failed”);

        }

        void conv_photo()

        {

            //converting photo to binary data

            if (pictureBox1.Image != null)

            {

                //using FileStream:(will not work while updating, if image is not changed)

                //FileStream fs = new FileStream(openFileDialog1.FileName, FileMode.Open, FileAccess.Read);

                //byte[] photo_aray = new byte[fs.Length];

                //fs.Read(photo_aray, 0, photo_aray.Length); 

                //using MemoryStream:

                ms = new MemoryStream();

                pictureBox1.Image.Save(ms, ImageFormat.Jpeg);

                byte[] photo_aray = new byte[ms.Length];

                ms.Position = 0;

                ms.Read(photo_aray, 0, photo_aray.Length);

                cmd.Parameters.AddWithValue(“@photo”, photo_aray);

            }

        }

        private void search_Click(object sender, EventArgs e)

        {

            try

            {

                int n = Convert.ToInt32(Interaction.InputBox(“Enter sno:”, “Search”, “20”, 100, 100));

                DataRow drow;

                drow = ds.Tables[0].Rows.Find(n);

                if (drow != null)

                {

                    rno = ds.Tables[0].Rows.IndexOf(drow);

                    textBox1.Text = drow[0].ToString();

                    textBox2.Text = drow[1].ToString();

                    textBox3.Text = drow[2].ToString();

                    textBox4.Text = drow[3].ToString();

                    pictureBox1.Image = null;

                    if (drow[4] != System.DBNull.Value)

                    {

                        photo_aray = (byte[])drow[4];

                        MemoryStream ms = new MemoryStream(photo_aray);

                        pictureBox1.Image = Image.FromStream(ms);

                    }

                }

                else

                    MessageBox.Show(“Record Not Found”);

            }

            catch

            {

                MessageBox.Show(“Invalid Input”);

            }

        }

        private void update_Click(object sender, EventArgs e)

        {

            cmd = new SqlCommand(“update student set sname='” + textBox2.Text + “‘, course='” + textBox3.Text + “‘, fee='” + textBox4.Text + “‘, photo=@photo where sno=” + textBox1.Text, con);

            conv_photo();

            con.Open();

            int n = cmd.ExecuteNonQuery();

            con.Close();

            if (n > 0)

            {

                MessageBox.Show(“Record Updated”);

                loaddata();

            }

            else

                MessageBox.Show(“Updation Failed”);

        }

        private void delete_Click(object sender, EventArgs e)

        {

            cmd = new SqlCommand(“delete from student where sno=” + textBox1.Text, con);

            con.Open();

            int n = cmd.ExecuteNonQuery();

            con.Close();

            if (n > 0)

            {

                MessageBox.Show(“Record Deleted”);

                loaddata();

                rno = 0;

                showdata();

            }

            else

                MessageBox.Show(“Deletion Failed”);

        }

        private void first_Click(object sender, EventArgs e)

        {

            rno = 0; showdata();

            MessageBox.Show(“First record”);

        }

        private void previous_Click(object sender, EventArgs e)

        {

            if (rno > 0)

            {

                rno–; showdata();

            }

            else

                MessageBox.Show(“First record”);

        }

        private void next_Click(object sender, EventArgs e)

        {

            if (rno < ds.Tables[0].Rows.Count – 1)

            {

                rno++; showdata();

            }

            else

                MessageBox.Show(“Last record”);

        }

        private void last_Click(object sender, EventArgs e)

        {

            rno = ds.Tables[0].Rows.Count – 1;

            showdata(); MessageBox.Show(“Last record”);

        }

        private void exit_Click(object sender, EventArgs e)

        {

            this.Close();

        }     

      }

}

Posted October 10, 2010 by PrashanthChindam in c#

inserting & retrieving images from database using stored procedures   1 comment


Objective:

          To insert & retrieve images from SQL server database using stored procedures and also to perform insert, search, update and delete operations & navigation of records.

Introduction:

As we want to insert images into database using stored procedures, we have to create a table and stored procedures in the database.

Query for creating table:
create table student(sno int primary key,sname varchar(50),course varchar(50),fee money,photo image)

Stored procedures:

create procedure  get_student
as
select  sno,sname, course, fee, photo from student
———–

create procedure insert_student (@sno int, @sname varchar(50), @course varchar(50), @fee smallmoney,@photo image=null)
as
insert into student(sno, sname,course, fee, photo) values (@sno, @sname, @course, @fee, @photo)
—————

create procedure  update_student(@sno int, @sname varchar(50),@course varchar(50), @fee smallmoney,@photo image=null)
as
update student set sname=@sname, course=@course, fee=@fee, photo=@photo where sno=@sno
————

create procedure delete_student(@sno int=null)
as
if not(@sno=null)
delete from student where sno=@sno

———————————————


Design:

 

Design the form as above with 1 PictureBox control, 1 OpenFileDialog control, 4 Labels, 4 TextBoxes and 11 Buttons.

PictureBox1 Properties:
    BorderStyle=Fixed3D; SizeMode=StrechImage

Note that OpenFileDialog control appears below the form(not on the form).

 

Introduction to code:

Inorder to communicate with SQL sever database, include the namespace
‘using System.Data.SqlClient’.

In this application, we will search a record by taking input from the InputBox. For this we have to add reference to Microsoft.VisualBasic.

Adding a Reference to ‘Microsoft.VisualBasic’:

Goto Project Menu ->Add Reference -> select ‘Microsoft.VisualBasic’ from .NET tab.
In order to use this reference we have to include the namespace:
‘using Microsoft.VisualBasic’ in the code.

Converting image into binary data:

We can’t store an image directly into the database. For this we have to solutions:

i) To store the location of the image in the database

ii) Converting the image into binary data and insert that binary data into database and convert that back to image while retrieving the records.

If we store the location of an image in the database, and suppose if that image is deleted or moved from that location, we will face problems while retrieving the records. So it is better to convert image into binary data and insert that binary data into database and convert that back to image while retrieving records.

->We can convert an image into binary data using 1. FileStream (or) 2. MemoryStream

1. FileStream uses file location to convert an image into binary data which we may/may not provide during updation of a record.
Ex:
      FileStream fs = new FileStream(openFileDialog1.FileName, FileMode.Open,                       FileAccess.Read);
      byte[] photo_aray = new byte[fs.Length];
      fs.Read(photo_aray, 0, photo_aray.Length);

2. So it is better to use MemoryStream which uses image in the PictureBox to convert an image into binary data.
Ex:
             MemoryStream ms = new MemoryStream();
             pictureBox1.Image.Save(ms, ImageFormat.Jpeg);
             byte[] photo_aray = new byte[ms.Length];
             ms.Position = 0;
             ms.Read(photo_aray, 0, photo_aray.Length);

->Inorder to use FileStream or MemoryStream we have to include the namespace:
‘using System.IO’.
          
OpenFileDialog Control:

We use OpenFileDialog control inorder to browse for the images (photos) to insert into the record.

Using DataAdapter with StoredProcedures:

We can use command object inorder to work with stored procedures by passing stored procedure name to command object and specifying CommandType as StoredProcedure

Ex:
cmd = new SqlCommand(“get_student”, con);
cmd.CommandType = CommandType.StoredProcedure;

DataAdapter can’t interact directly with the stored procedures, but if we need to use DataAdapter while working with stored procedures, we can achieve this by passing the command object to the DataAdapter.

Loading the constraint details into the dataTable:

In this app. we use Find() method to search a record, which requires details of primarykey column, which can be provided using the statement:
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

Pointing to current record in dataTable:

After searching a record, we have to get the index of that record so that we can show next and previous records when we press ‘>>'(next) and ‘<<‘(previous) buttons.

Ex:
rno= ds.Tables[0].Rows.IndexOf(drow);

Code:

using System;

using System.Windows.Forms;

using System.Data;

using System.Data.SqlClient;

using System.Drawing;

using System.Drawing.Imaging;

using System.IO;

using Microsoft.VisualBasic;

namespace inserting_imgs

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        SqlConnection con;

        SqlCommand cmd;

        SqlDataAdapter adapter;

        DataSet ds; int rno = 0;

        MemoryStream ms;

        byte[] photo_aray;

        private void Form1_Load(object sender, EventArgs e)

        {

            con = new SqlConnection(“user id=sa;password=123;database=prash”);

            loaddata();

            showdata();

        }

        void loaddata()

        {

            cmd = new SqlCommand(“get_student”, con);

            cmd.CommandType = CommandType.StoredProcedure; adapter = new SqlDataAdapter(cmd);

            adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

            ds = new DataSet(); adapter.Fill(ds, “student”);

        }

        void showdata()

        {

            if (ds.Tables[0].Rows.Count > 0)

            {

                textBox1.Text = ds.Tables[0].Rows[rno][0].ToString();

                textBox2.Text = ds.Tables[0].Rows[rno][1].ToString();

                textBox3.Text = ds.Tables[0].Rows[rno][2].ToString();

                textBox4.Text = ds.Tables[0].Rows[rno][3].ToString();

                pictureBox1.Image = null;

                if (ds.Tables[0].Rows[rno][4] != System.DBNull.Value)

                {

                    photo_aray = (byte[])ds.Tables[0].Rows[rno][4];

                    MemoryStream ms = new MemoryStream(photo_aray);

                    pictureBox1.Image = Image.FromStream(ms);

                }

            }

            else

                MessageBox.Show(“No Records”);

        }        

        private void browse_Click(object sender, EventArgs e)

        {

            openFileDialog1.Filter = “jpeg|*.jpg|bmp|*.bmp|all files|*.*”;

            DialogResult res = openFileDialog1.ShowDialog();

            if (res == DialogResult.OK)

            {

                pictureBox1.Image = Image.FromFile(openFileDialog1.FileName);

            }

        }

        private void clear_Click(object sender, EventArgs e)

        {

            textBox1.Text = textBox2.Text = textBox3.Text = textBox4.Text = “”;

            pictureBox1.Image = null;

        }

        private void insert_Click(object sender, EventArgs e)

        {

            cmd = new SqlCommand(“insert_student”, con);

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue(“@sno”, textBox1.Text);

            cmd.Parameters.AddWithValue(“@sname”, textBox2.Text);

            cmd.Parameters.AddWithValue(“@course”, textBox3.Text);

            cmd.Parameters.AddWithValue(“@fee”, textBox4.Text);

            conv_photo();

            con.Open();

            int n = cmd.ExecuteNonQuery();

            con.Close();

            if (n > 0)

            {

                MessageBox.Show(“record inserted”);

                loaddata();

            }

            else

                MessageBox.Show(“insertion failed”);

        }

        void conv_photo()

        {

            //converting photo to binary data

            if (pictureBox1.Image != null)

            {

                //using FileStream: (will not work in updating record without changing photo)

                //FileStream fs = new FileStream(openFileDialog1.FileName, FileMode.Open, FileAccess.Read);

                //byte[] photo_aray = new byte[fs.Length];

                //fs.Read(photo_aray, 0, photo_aray.Length); 

                //using MemoryStream: (works for updating record without changing photo)

                ms = new MemoryStream();

                pictureBox1.Image.Save(ms, ImageFormat.Jpeg);

                byte[] photo_aray = new byte[ms.Length];

                ms.Position = 0;

                ms.Read(photo_aray, 0, photo_aray.Length);

                cmd.Parameters.AddWithValue(“@photo”, photo_aray);

            }

        }

        private void search_Click(object sender, EventArgs e)

        {

            try

            {

                int n = Convert.ToInt32(Interaction.InputBox(“Enter sno:”, “Search”, “20”, 100, 100));

                DataRow drow;

                drow = ds.Tables[0].Rows.Find(n);

                if (drow != null)

                {

                    rno = ds.Tables[0].Rows.IndexOf(drow);

                    textBox1.Text = drow[0].ToString();

                    textBox2.Text = drow[1].ToString();

                    textBox3.Text = drow[2].ToString();

                    textBox4.Text = drow[3].ToString();

                    pictureBox1.Image = null;

                    if (drow[4] != System.DBNull.Value)

                    {

                        photo_aray = (byte[])drow[4];

                        MemoryStream ms = new MemoryStream(photo_aray);

                        pictureBox1.Image = Image.FromStream(ms);

                    }

                }

                else

                    MessageBox.Show(“Record Not Found”);

            }

            catch

            {

                MessageBox.Show(“Invalid Input”);

            }

        }

        private void update_Click(object sender, EventArgs e)

        {

            cmd = new SqlCommand(“update_student”, con);

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue(“@sno”, textBox1.Text);

            cmd.Parameters.AddWithValue(“@sname”, textBox2.Text);

            cmd.Parameters.AddWithValue(“@course”, textBox3.Text);

            cmd.Parameters.AddWithValue(“@fee”, textBox4.Text);

            conv_photo();

            con.Open();

            int n = cmd.ExecuteNonQuery();

            con.Close();

            if (n > 0)

            {

                MessageBox.Show(“Record Updated”);

                loaddata();

            }

            else

                MessageBox.Show(“Updation Failed”);

        }

        private void delete_Click(object sender, EventArgs e)

        {

            cmd = new SqlCommand(“delete_student”, con);

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue(“@sno”, textBox1.Text);

            con.Open();

            int n = cmd.ExecuteNonQuery();

            con.Close();

            if (n > 0)

            {

                MessageBox.Show(“Record Deleted”);

                loaddata();

                rno = 0;

                showdata();

            }

            else

                MessageBox.Show(“Deletion Failed”);

        }

        private void first_Click(object sender, EventArgs e)

        {

            rno = 0; showdata();

            MessageBox.Show(“First record”);

        }

        private void previous_Click(object sender, EventArgs e)

        {

            if (rno > 0)

            {

                rno–; showdata();

            }

            else

                MessageBox.Show(“First record”);

        }

        private void next_Click(object sender, EventArgs e)

        {

            if (rno < ds.Tables[0].Rows.Count – 1)

            {

                rno++; showdata();

            }

            else

                MessageBox.Show(“Last record”);

        }

        private void last_Click(object sender, EventArgs e)

        {

            rno = ds.Tables[0].Rows.Count – 1;

            showdata(); MessageBox.Show(“Last record”);

        }

        private void exit_Click(object sender, EventArgs e)

        {

            this.Close();

        }     

    }

}

Posted October 10, 2010 by PrashanthChindam in c#

inserting & retrieving records from M.S.Access-2007 using oledb in C#.net   Leave a comment


Objective:

To insert, search, edit and delete records from M.S.Access-2007 file using OleDb Connection.

Design:


Design the form as above with a DataGridView, 3 Labels, 3 TextBoxes, 10 buttons.

[ Note: In order to perform operations on M.S.Access-2007 records,
M.S.Office-2007 should be installed in your system.]

Introduction:

As we want to use OleDb Connection include the namespace:

‘using System.Data.OleDb’

For accesing records from M.S.Access-2003 file we use ‘Jet’ driver,

but for accesing records from M.S.Access-2003 file we use ‘Ace’ driver.

In this application, we will search a record by taking input from the InputBox. For this we have to add reference to Microsoft.VisualBasic.

Adding a Reference:

Goto Project Menu

->Add Reference -> select ‘Microsoft.VisualBasic’ from .NET tab.

Inorder to use this we have to include the namespace:

‘using Microsoft.VisualBasic’

->creating a primary key in the dataTable:

In this app. we use Find() method to search a record, which requires details of primarykey column for database tables this provided using statement: adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

But as we don’t have any primarykey column in M.S.Access table, we have to create a primary key column in datatable.

Eg:

ds.Tables[0].Constraints.Add(“pk_sno”, ds.Tables[0].Columns[0], true);

->pointing to current record in dataTable:

After searching a record, we have to get index of that record so that we can show next and previous records when we press ‘>>’(next) and ‘<<’(previous) buttons.

Eg:

rno= ds.Tables[0].Rows.IndexOf(drow);

—————————————————–

Code:

using System;

using System.Data;

using System.Windows.Forms;

using System.Data.OleDb;

using Microsoft.VisualBasic;

namespace prash_access07

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

OleDbConnection con;

OleDbCommand cmd;

OleDbDataAdapter adapter;

DataSet ds;

int rno;

private void Form1_Load(object sender, EventArgs e)

{

con = new OleDbConnection(@” provider=Microsoft.ace.Oledb.12.0; data source=E:\prash\stud.accdb;Persist Security Info=False”);//stud.accdb->access07 filename

loaddata();

showdata();

}

void loaddata()

{

adapter = new OleDbDataAdapter(“select * from student”, con);

ds = new DataSet();//student-> table name in stud.accdb file

adapter.Fill(ds, “student”);

ds.Tables[0].Constraints.Add(“pk_sno”, ds.Tables[0].Columns[0], true);//creating primary key for Tables[0] in dataset

dataGridView1.DataSource = ds.Tables[0];

}

void showdata()

{

textBox1.Text = ds.Tables[0].Rows[rno][0].ToString();

textBox2.Text = ds.Tables[0].Rows[rno][1].ToString();

textBox3.Text = ds.Tables[0].Rows[rno][2].ToString();

}

private void btnFirst_Click(object sender, EventArgs e)

{

if (ds.Tables[0].Rows.Count > 0)

{

rno = 0;

showdata();

}

else

MessageBox.Show(“no records”);

}

private void btnPrevious_Click(object sender, EventArgs e)

{

if (ds.Tables[0].Rows.Count > 0)

{

if (rno > 0)

{

rno–;

showdata();

}

else

MessageBox.Show(“First Record”);

}

else

MessageBox.Show(“no records”);

}

private void btnNext_Click(object sender, EventArgs e)

{

if (ds.Tables[0].Rows.Count > 0)

{

if (rno < ds.Tables[0].Rows.Count – 1)

{

rno++;

showdata();

}

else

MessageBox.Show(“Last Record”);

}

else

MessageBox.Show(“no records”);

}

private void btnLast_Click(object sender, EventArgs e)

{

if (ds.Tables[0].Rows.Count > 0)

{

rno = ds.Tables[0].Rows.Count – 1;

showdata();

}

else

MessageBox.Show(“no records”);

}

private void btnInsert_Click(object sender, EventArgs e)

{

cmd = new OleDbCommand(“insert into student values(” + textBox1.Text + “,’ ” + textBox2.Text + ” ‘,’ ” + textBox3.Text + ” ‘)”, con);

con.Open();

int n = cmd.ExecuteNonQuery();

con.Close();

if (n > 0)

{

MessageBox.Show(“record inserted”);

loaddata();

}

else

MessageBox.Show(“insertion failed”);

}

private void btnSearch_Click(object sender, EventArgs e)

{

int n = Convert.ToInt32(Interaction.InputBox(“Enter sno:”, “Search”, “20”, 200, 200));

DataRow drow = ds.Tables[0].Rows.Find(n);

if (drow != null)

{

rno = ds.Tables[0].Rows.IndexOf(drow);

textBox1.Text = drow[0].ToString();

textBox2.Text = drow[1].ToString();

textBox3.Text = drow[2].ToString();

}

else

MessageBox.Show(“Record not found”);

}

private void btnUpdate_Click(object sender, EventArgs e)

{

cmd = new OleDbCommand(“update student set sname='” + textBox2.Text + “‘,course='” + textBox3.Text + “‘ where sno=” + textBox1.Text, con);

con.Open();

int n = cmd.ExecuteNonQuery();

con.Close();

if (n > 0)

{

MessageBox.Show(“Record Updated”);

loaddata();

}

else

MessageBox.Show(“Update failed”);

}

private void btnDelete_Click(object sender, EventArgs e)

{

cmd = new OleDbCommand(“delete from student where sno=” + textBox1.Text, con);

con.Open();

int n = cmd.ExecuteNonQuery();

con.Close();

if (n > 0)

{

MessageBox.Show(“Record Deleted”);

loaddata();

}

else

MessageBox.Show(“Deletion failed”);

}

private void btnClear_Click(object sender, EventArgs e)

{

textBox1.Text = textBox2.Text = textBox3.Text = “”;

}

private void btnExit_Click(object sender, EventArgs e)

{

this.Close();

}

}

}

Posted October 4, 2010 by PrashanthChindam in c#

Application to speak the text written in the textbox using C#.Net   Leave a comment


Application to speak the text in the textbox using C#.Net

 Design:                               

 Design the form as shown above with one TextBox and three Buttons,

Set the ‘textBox1’ Properties as follows:

Dock: Top,

Multiline: True.

->Now  goto ‘Project’ Menu -> Select ‘AddReference’-> Click on ‘COM’ tab,

    Select ‘Microsoft Speech Object Library’ COM component -> OK          

          

 ->Now goto code window and include ‘using SpeechLib’ namespace

Code:

 

using System;

using System.Windows.Forms;

using SpeechLib;//include this namespace

namespace TextSpeaker

{

    public partial class TextSpeakerForm : Form

    {

        public TextSpeakerForm()

        {

            InitializeComponent();

        }

        private void btnSpeak_Click(object sender, EventArgs e)

        {

            if (textBox1.Text.Trim().Length > 0)

            {

                SpVoice obj = new SpVoice();

                obj.Speak(textBox1.Text, SpeechVoiceSpeakFlags.SVSFDefault);

            }

            MessageBox.Show(“Plz. write some text in the TextBox”,”Info.”,MessageBoxButtons.OK,MessageBoxIcon.Information);

        }

        private void btnClear_Click(object sender, EventArgs e)

        {

            textBox1.Text = “”;

        }

        private void btnExit_Click(object sender, EventArgs e)

        {

            this.Close();

        }

    }

}

Output:

            Write some text in the textbox and press ‘speak’ button

                                 

Posted September 28, 2010 by PrashanthChindam in c#

%d bloggers like this: