data retrieving from a database....

data retrieving from a database....

• Ceefour

  In my example, "MyTable" is the name of the DataTable in the DataSet. It can be specified in the .Fill() method of the DataAdapter, as I showed, or you can add DataTables to the DataSet (naming them as you go) after you instantiate the DataSet. Or, you can use Typed DataSets.

  As far as namespaces go, you'd need

  System.Data

  System.Data.SqlClient or System.Data.OleDb (depending on your database)

  System.Xml

  
  
  

• santech79

  Is there any possibility to store word document, pdf document, excel sheets, different type of images are to the database OLE field through C#
  
  

• JP#

  I'm not sure what you mean by the "OLE object field".

  You can use the FileStream class to capture a file and then convert it to a byte array, which you can then send to your database.

  private byte[] UploadedFile;

  FileStream fs = new FileStream (MyFileName, FileMode.OpenOrCreate,

  FileAccess.Read);

  // Read the Data into the Byte Array

  UploadedFile = new byte[fs.Length];

  fs.Read(UploadedFile, 0, (int)fs.Length);

  fs.Close();

  And then you use the UploadedFile to update your database.

  
  
  

• baskardurai

  Can you give a code snippet for this.. if possible.

  Also let me know if it possible to find out whether ab OLE object field contains a specific data like images, documents, etc.

  
  
  

• oz_michaelw

  Yes, you can do that. In Sql Server, that sort of thing can be stored in an image data type.
  
  

• A. Nagy

  uh hi. i just read this thread and im still a newbie with these database stuff so i just have a few questions.. do i have to create a dataset first which i would name as "MyTable" and what are the namespaces that i should include in the program
  
  
  

• Johnson.R

  Hi

  Select data from SQL database using C#

  int i = 0;
  sqlConnection1.Open();
  SqlCommand cmd_Code = new SqlCommand("SELECT [Customer Code] FROM Customer_Details_Table", sqlConnection1);
  SqlDataReader read = cmd_Code.ExecuteReader();
  while (read.Read())
  {
  cmbSelect.Items.Add(read.GetValue(i).ToString()); //cmdSelect is a combobox
  i = i + 1 - 1;
  }
  read.Close();
  sqlConnection1.Close();

  Delete data from SQL database using C#

  sqlConnection1.Open();
  SqlCommand cmd_del = new SqlCommand("DELETE FROM Customer_Details_Table WHERE [Customer Code]='" + txtCode.Text + "'", sqlConnection1);
  cmd_del.ExecuteNonQuery();
  sqlConnection1.Close();

  Insert data from SQL database using C#

  //Create a Dataset from Add New Item Options.

  //Drag & Drop the table in Data Source View to your Dataset

  this.data_customerCodeTableAdapter1.Insert(txtCode.Text);

  
  
  

• Atem

  Jeff,

  Your DataAccess class should retrieve/pass DataSets between your database and your front-end UI (I throw a Web Service in between the front-end client-side stuff and the back-end server-side stuff, but let's just talk about DataAccess for now).

  Retrieving the data is easy. Note that this DataAccess class returns a DataSet:

  public DataSet GetMyData()
  {
  string TestConnection = "server=(local);database=MyDataBase;uid=sa;pwd=MyPassword";
  SqlDataAdapter da = new SqlDataAdapter("select * from bob", this.TestConnection);
  DataSet ds = new DataSet();
  da.Fill(ds, "MyTable");

  return ds;
  }

  
  There are a few more options when updating the database.

  First, you can use the Update method of the DataAdapter. In order for this to work, your DataSet must have a PrimaryKey defined.

  You can do it using the CommandBuilder, which will generate update commands for you:

  public void UpdateMyData(DataSet ds)
  {
  // I wouldn't actually hard-code the connection string here, it should be in your config settings
  string TestConnection = "server=(local);database=MyDataBase;uid=sa;pwd=MyPassword";

  SqlDataAdapter da = new SqlDataAdapter("select * from bob", this.TestConnection);
  SqlCommandBuilder sb = new SqlCommandBuilder(da);

  da.Update(ds);
  }

  Or you can create the various update commands yourself instead of using the CommandBuilder:

  public void UpdateMyData(DataSet ds)
  {

  // I wouldn't actually hard-code the connection string here, it should be in your config settings
  string TestConnection = "server=(local);database=MyDataBase;uid=sa;pwd=MyPassword";

  SqlCommand sc = new SqlCommand();
  sc.Connection = new SqlConnection(TestConnection);
  da = new SqlDataAdapter(sc);

  da.InsertCommand = new SqlCommand("Insert into bob (xyz, abc) VALUES ( @xyz, @abc )", sc.Connection);
  da.InsertCommand.Parameters.Add("@xyz", SqlDbType.Int, 8, "xyz");
  da.InsertCommand.Parameters.Add("@abc", SqlDbType.VarChar, 50, "abc");

  // do the same for da.DeleteCommand & da.UpdateCommand

  da.Update(ds);
  }

  Or, you can take total control, not use the da.Update() and do it all yourself (this is basically the same code that gets done behind the scenes by the da.Update() method:

  public void UpdateMyData(DataSet ds)
  {
  // I wouldn't actually hard-code the connection string here, it should be in your config settings
  string TestConnection = "server=(local);database=MyDataBase;uid=sa;pwd=MyPassword";

  SqlCommand sc = new SqlCommand();
  sc.Connection = new SqlConnection(TestConnection);
  sc.Connection.Open();
  foreach (DataRow Row in ds.Tables[0].Rows)
  {
  switch (Row.RowState)
  {
  case DataRowState.Added :
  sc.CommandText = "Insert into bob (xyz, abc) VALUES ( @xyz, @abc )";
  sc.Parameters.Clear();
  sc.Parameters.Add("@xyz", Row["xyz"]);
  sc.Parameters.Add("@abc", Row["abc"]);
  sc.ExecuteNonQuery();
  break;
  
  // Do the same for DataRowState Deleted and Modified
  case DataRowState.Deleted :
  break;
  case DataRowState.Modified :
  break;
  }
  }
  sc.Connection.Close();
  }

  
  
  
• data retrieving from a database....