C# I need to know how to insert a value into a databse using the classes i curre
ID: 3821930 • Letter: C
Question
C# I need to know how to insert a value into a databse using the classes i currently have.. the value will be coming from a text box.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Reflection;
namespace MyFinalGroupProject
{
public class clsDataAccess
{
/// <summary>
/// Connection string to the database.
/// </summary>
private string sConnectionString;
/// <summary>
/// Constructor that sets the connection string to the database
/// </summary>
public clsDataAccess()
{
sConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data source= " + Directory.GetCurrentDirectory() + "\Invoice.mdb";
}
/// <summary>
/// This method takes an SQL statment that is passed in and executes it. The resulting values
/// are returned in a DataSet. The number of rows returned from the query will be put into
/// the reference parameter iRetVal.
/// </summary>
/// <param name="sSQL">The SQL statement to be executed.</param>
/// <param name="iRetVal">Reference parameter that returns the number of selected rows.</param>
/// <returns>Returns a DataSet that contains the data from the SQL statement.</returns>
public DataSet ExecuteSQLStatement(string sSQL, ref int iRetVal)
{
try
{
//Create a new DataSet
DataSet ds = new DataSet();
using (OleDbConnection conn = new OleDbConnection(sConnectionString))
{
using (OleDbDataAdapter adapter = new OleDbDataAdapter())
{
//Open the connection to the database
conn.Open();
//Add the information for the SelectCommand using the SQL statement and the connection object
adapter.SelectCommand = new OleDbCommand(sSQL, conn);
adapter.SelectCommand.CommandTimeout = 0;
//Fill up the DataSet with data
adapter.Fill(ds);
}
}
//Set the number of values returned
iRetVal = ds.Tables[0].Rows.Count;
//return the DataSet
return ds;
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// This method takes an SQL statment that is passed in and executes it. The resulting single
/// value is returned.
/// </summary>
/// <param name="sSQL">The SQL statement to be executed.</param>
/// <returns>Returns a string from the scalar SQL statement.</returns>
public string ExecuteScalarSQL(string sSQL)
{
try
{
//Holds the return value
object obj;
using (OleDbConnection conn = new OleDbConnection(sConnectionString))
{
using (OleDbDataAdapter adapter = new OleDbDataAdapter())
{
//Open the connection to the database
conn.Open();
//Add the information for the SelectCommand using the SQL statement and the connection object
adapter.SelectCommand = new OleDbCommand(sSQL, conn);
adapter.SelectCommand.CommandTimeout = 0;
//Execute the scalar SQL statement
obj = adapter.SelectCommand.ExecuteScalar();
}
}
//See if the object is null
if (obj == null)
{
//Return a blank
return "";
}
else
{
//Return the value
return obj.ToString();
}
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
/// <summary>
/// This method takes an SQL statment that is a non query and executes it.
/// </summary>
/// <param name="sSQL">The SQL statement to be executed.</param>
/// <returns>Returns the number of rows affected by the SQL statement.</returns>
public int ExecuteNonQuery(string sSQL)
{
try
{
//Number of rows affected
int iNumRows;
using (OleDbConnection conn = new OleDbConnection(sConnectionString))
{
//Open the connection to the database
conn.Open();
//Add the information for the SelectCommand using the SQL statement and the connection object
OleDbCommand cmd = new OleDbCommand(sSQL, conn);
cmd.CommandTimeout = 0;
//Execute the non query SQL statement
iNumRows = cmd.ExecuteNonQuery();
}
//return the number of rows affected
return iNumRows;
}
catch (Exception ex)
{
throw new Exception(MethodInfo.GetCurrentMethod().DeclaringType.Name + "." + MethodInfo.GetCurrentMethod().Name + " -> " + ex.Message);
}
}
}
}
.............................
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.ComponentModel;
using System.Data;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.ComponentModel;
using System.Data;
namespace MyFinalGroupProject
{
public class clsitem_Manager////////////////////////////////////////////this class managers flights from the database
{
//clsDataAccess db;
clsDataAccess db = new clsDataAccess();
DataSet ds = new DataSet();
BindingList<clsItem> lstItems = new BindingList<clsItem>();
public int numberOfRecords;
public BindingList<clsItem> GetAllItemsFromDB()////////////////////////////////////////////This method will get all flights from the db, put each flight into a clsFlight object, add that object to the list, then return that list to the UI
{
//lstFlights = ////////This is a list of flights that represents all flights from the database
////////////////Steps for you to complete
string sSQL = clsSQL.GetAllItems_SQL();/////////////////////////Get SQL to get all flights
int iRet = 0;
//Execute the sSQL statement using the clsDataAccess class to get the flights into a dataset
ds = db.ExecuteSQLStatement(sSQL, ref iRet);
foreach (DataRow dr in ds.Tables[0].Rows)
{
lstItems.Add(new clsItem { ItemCode = dr[0].ToString(), ItemDesc = dr[1].ToString(), Cost = dr[2].ToString() });
numberOfRecords++;
}
return lstItems;
}
}
public class clsItem
{
public string ItemCode { get; set; }
public string ItemDesc { get; set; }
public string Cost { get; set; }
}
}
Explanation / Answer
Hi,
Apologis for the repeated answer. I have given you commands to run queries for connecting SQL Server but instead you are looking for OleDB to .net connection. Here are some of the steps that you can follow-
//Create a Data Adpater Object
OleDbDataAdapter adapter = new OleDbDataAdapter();
// Create a OleDBCommand Object
OleDbCommand command;
// Create the SelectCommand(Consider TextBox1 and TextBox2 are the ids of the 2 textboxes of your page(You can use your query here. This is just a sample)
command = new OleDbCommand("SELECT CustomerID FROM Customers " +
"WHERE Country ='"+TextBox1.Text+"' AND City = ='"+TextBox2.Text"'", connection);
adapter.SelectCommand = command;
// Create the InsertCommand.Here t1, te, t3 etc indicates the ids of the text boxes which you areplanning to load into tables
command = new OleDbCommand(
"insert into Hotel values ('" + t1.Text + "','" + t2.Text + "','"
+ t3.Text + "','" + t4.Text + "','"
+ ff1 + "','" + ff2 + "','" + ff3 + "','" + t5.Text + "')";
adapter.InsertCommand = command;
return adapter;
// Need to execute a ExecuteNon Query function to insert records
SqlCommand cmd = new SqlCommand(command, con);
int k = cmd.ExecuteNonQuery();
Response.Write("Rows are inserted");
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.