private void loadEmployeeName()
{
this.personTableAdapter = new PersonTableAdapter();
this.personTableAdapter.ClearBeforeFill = true;
this.personTableAdapter.Fill(adventureWorksDataSet.Person);
this.employeeNameDropDownList.DataSource = this.adventureWorksDataSet.Person;
this.employeeNameDropDownList.DataTextField = "LastFirstName";
this.employeeNameDropDownList.DataValueField = "BusinessEntityID";
this.employeeNameDropDownList.DataBind();
}
static Login()
{
dsperson = new TRPRLibraryDataSet();
PersonTableAdapter daPerson = new PersonTableAdapter();
try
{
daPerson.Fill(dsperson.Person);
}
catch { }
}
public static WebTestingDataSet.PersonDataTable GetAllPersons()
{
using (PersonTableAdapter adapter = new PersonTableAdapter())
{
using (UpdateConnection(adapter))
{
WebTestingDataSet.PersonDataTable table = adapter.GetData();
return(table);
}
}
}
static SearchAE()
{
dsAE = new TRPRLibraryDataSet();
PersonTableAdapter daPerson = new PersonTableAdapter();
try
{
daPerson.Fill(dsAE.Person);
}
catch { }
}
public static void Save(WebTestingDataSet.PersonRow person)
{
if (person != null)
{
PersonTableAdapter adapter = new PersonTableAdapter();
using (UpdateConnection(adapter))
{
adapter.Update(person.Table as WebTestingDataSet.PersonDataTable);
}
}
}
public static WebTestingDataSet.PersonDataTable GetAllPersons()
{
using (PersonTableAdapter adapter = new PersonTableAdapter())
{
using (UpdateConnection(adapter))
{
WebTestingDataSet.PersonDataTable table = adapter.GetData();
return table;
}
}
}
static Author()
{
dsperson = new TRPRLibraryDataSet();
PersonTableAdapter daPerson = new PersonTableAdapter();
ManuscriptTableAdapter daManu = new ManuscriptTableAdapter();
try
{
daPerson.Fill(dsperson.Person);
daManu.Fill(dsperson.Manuscript);
}
catch { }
}
void GetPersonInDb()
{
var personTable = new PersonTableAdapter();
personTable.Fill(db.Person);
foreach (DataRow row in db.Person)
{
foreach (DataColumn column in db.Person.Columns)
{
Console.WriteLine($"{column} : {row[column]}");
}
}
Clear();
}
public static void DeleteAllPersonRows()
{
PersonTableAdapter adapter = new PersonTableAdapter();
using (UpdateConnection(adapter))
{
WebTestingDataSet.PersonDataTable table = adapter.GetData();
foreach (WebTestingDataSet.PersonRow person in table)
{
person.Delete();
}
adapter.Update(table);
}
}
private void InsertPersonInDb()
{
var personTable = new PersonTableAdapter();
personTable.Insert("coll777", "89521445786");
personTable.Fill(db.Person);
foreach (DataRow row in db.Person.Rows)
{
foreach (DataColumn column in db.Person.Columns)
{
Console.WriteLine($"{column} : {row[column]}");
}
}
Clear();
GetPersonInDb();
}
internal static SqlConnection UpdateConnection(PersonTableAdapter adapter)
{
if (adapter != null)
{
var connectionStringSetting =
ConfigurationManager.ConnectionStrings["(default)"];
if (connectionStringSetting == null)
{
throw new InvalidOperationException("Could not find connection string.");
}
return(UpdateConnection(adapter, connectionStringSetting.ConnectionString));
}
else
{
return(null);
}
}
public static WebTestingDataSet.PersonRow GetPerson(int id)
{
using (PersonTableAdapter adapter = new PersonTableAdapter())
{
using (UpdateConnection(adapter))
{
WebTestingDataSet.PersonDataTable table = adapter.GetDataById(id);
if (table == null || table.Rows.Count == 0)
{
return(null);
}
else
{
return(table[0]);
}
}
}
}
internal static SqlConnection UpdateConnection(PersonTableAdapter adapter, string connectionString)
{
if (adapter != null)
{
SqlConnection connection = new SqlConnection();
connection.ConnectionString = connectionString;
connection.Open();
adapter.Connection = connection;
return(connection);
}
else
{
return(null);
}
}
public static WebTestingDataSet.PersonRow GetPerson(int id)
{
using (PersonTableAdapter adapter = new PersonTableAdapter())
{
using (UpdateConnection(adapter))
{
WebTestingDataSet.PersonDataTable table = adapter.GetDataById(id);
if (table == null || table.Rows.Count == 0)
{
return null;
}
else
{
return table[0];
}
}
}
}
internal static SqlConnection UpdateConnection(PersonTableAdapter adapter, string connectionString)
{
if (adapter != null)
{
SqlConnection connection = new SqlConnection();
connection.ConnectionString = connectionString;
connection.Open();
adapter.Connection = connection;
return connection;
}
else
{
return null;
}
}
internal static SqlConnection UpdateConnection(PersonTableAdapter adapter)
{
if (adapter != null)
{
var connectionStringSetting =
ConfigurationManager.ConnectionStrings["(default)"];
if (connectionStringSetting == null)
{
throw new InvalidOperationException("Could not find connection string.");
}
return UpdateConnection(adapter, connectionStringSetting.ConnectionString);
}
else
{
return null;
}
}
private void Save()
{
PersonTableAdapter daPerson = new PersonTableAdapter();
try
{
daPerson.Update(dsAE.Person);
dsAE.AcceptChanges();
dsAE.Person.Clear();
daPerson.Fill(dsAE.Person);
//Label1.Text = "data saved";
}
catch
{
dsAE.RejectChanges();
//Label1.Text = "error";
}
Response.Redirect("SearchAE.aspx");
}
/// <summary>
/// Insert and Select data using Strong Typed DataSet
/// </summary>
private static void InsertSelectUsingStrongTypedDataSet()
{
Console.WriteLine("\r\nInsert and Select data using Strong Typed "
+ "DataSet...");
// 1. Create a Strong Typed DataSet object and fill its corresponding
// data tables
// Create a Strong Typed DataSet object
SQLServer2005DBDataSet dsSQLServer = new SQLServer2005DBDataSet();
// Use the PersonTableAdapter to fill the Person table
PersonTableAdapter taPerson = new PersonTableAdapter();
SQLServer2005DBDataSet.PersonDataTable tblPerson = dsSQLServer.
Person;
taPerson.Fill(tblPerson);
// Use the CourseTableAdapter to fill the Course table
CourseTableAdapter taCourse = new CourseTableAdapter();
SQLServer2005DBDataSet.CourseDataTable tblCourse = dsSQLServer.
Course;
taCourse.Fill(tblCourse);
// Use the DepartmentTableAdapter to fill the Department table
DepartmentTableAdapter taDepartment = new DepartmentTableAdapter();
SQLServer2005DBDataSet.DepartmentDataTable tblDepartment =
dsSQLServer.Department;
taDepartment.Fill(tblDepartment);
// 2. Insert a record into the Person table
//--- (Strong Typed DataSet)
// We don't set the PersonCategory value because we have set the
// default value property of the DataSet PersonCategory column.
SQLServer2005DBDataSet.PersonRow addRowPerson = tblPerson.
NewPersonRow();
addRowPerson.LastName = "Ge";
addRowPerson.FirstName = "Jialiang";
addRowPerson.SetHireDateNull();
addRowPerson.EnrollmentDate = DateTime.Now;
addRowPerson.Picture = ReadImage(@"MSDN.jpg");
tblPerson.AddPersonRow(addRowPerson);
// ---Insert a record into the Person table
// ---(Untyped DataSet)
//DataRow addRowPerson = tblPerson.NewRow();
//addRowPerson["LastName"] = "Ge";
//addRowPerson["FirstName"] = "Jialiang";
//addRowPerson["HireDate"] = DBNull.Value;
//addRowPerson["EnrollmentDate"] = DateTime.Now;
//addRowPerson["Picture"] = ReadImage(@"MSDN.jpg");
//tblPerson.Rows.Add(addRowPerson);
// Update the Person table
taPerson.Update(tblPerson);
// 3. Insert a record into the Person table using PersionTableAdapter
taPerson.Insert("Sun", "Hongye", DateTime.Now, null,
ReadImage(@"MSDN.jpg"), 2);
// 4. Find a certain record in the Person table by primary key
// ---(Strong Typed DataSet)
SQLServer2005DBDataSet.PersonRow findRowPerson = tblPerson.
FindByPersonID(1);
// ---Find a certain record in the Person table by primary key
// ---(Untyped DataSet)
//DataRow findRowPerson = tblPerson.Rows.Find(1);
// Display the result record
if (findRowPerson != null)
{
// Use IsColumnNameNull method to check the DBNull value
Console.WriteLine("{0}\t{1} {2}\t{3}", findRowPerson.PersonID,
findRowPerson.FirstName, findRowPerson.LastName,
findRowPerson.IsEnrollmentDateNull() ? "(DBNull)" :
findRowPerson.EnrollmentDate.ToShortDateString());
}
// 5. Display data in two related data tables
foreach (SQLServer2005DBDataSet.DepartmentRow rowDepartment in
dsSQLServer.Department)
{
Console.WriteLine("\r\nCourses for Department({0})",
rowDepartment.DepartmentID);
foreach (SQLServer2005DBDataSet.CourseRow rowCourse
in rowDepartment.GetCourseRows())
{
Console.WriteLine("{0} - {1}", rowCourse.CourseID,
rowCourse.Title);
}
}
}
private void Window_Loaded(object sender, RoutedEventArgs e)
{
var pta = new PersonTableAdapter();
dataGrid.ItemsSource = pta.GetData().DefaultView;
}
public static void DeleteAllPersonRows()
{
PersonTableAdapter adapter = new PersonTableAdapter();
using (UpdateConnection(adapter))
{
WebTestingDataSet.PersonDataTable table = adapter.GetData();
foreach (WebTestingDataSet.PersonRow person in table)
{
person.Delete();
}
adapter.Update(table);
}
}
public static void Save(WebTestingDataSet.PersonRow person)
{
if (person != null)
{
PersonTableAdapter adapter = new PersonTableAdapter();
using (UpdateConnection(adapter))
{
adapter.Update(person.Table as WebTestingDataSet.PersonDataTable);
}
}
}
/// <summary>
/// This method works with the strong typed DataSet
/// </summary>
private static void QueryDBByStrongTypedDataSet()
{
Console.WriteLine("Use LINQ to query strong typed DataSet...");
/////////////////////////////////////////////////////////////////////
// Fill the strong typed DataSet && Insert data into database
//
// Create a strong typed DataSet object
SQLServer2005DBDataSet dsSQLServer = new SQLServer2005DBDataSet();
// Create the PersonTableAdapter and fill the Person DataTable
PersonTableAdapter taPerson = new PersonTableAdapter();
SQLServer2005DBDataSet.PersonDataTable tblPerson = dsSQLServer.
Person;
taPerson.Fill(tblPerson);
// Create a new row into the Person DataTable
// We don't set the PersonCategory value because we have set the
// default value property of the DataSet PersonCategory column.
SQLServer2005DBDataSet.PersonRow rowPerson = tblPerson.NewPersonRow();
rowPerson.LastName = "Sun";
rowPerson.FirstName = "Hongye";
rowPerson.SetHireDateNull();
rowPerson.EnrollmentDate = DateTime.Now;
rowPerson.Picture = ReadImage(@"MSDN.jpg");
tblPerson.AddPersonRow(rowPerson);
// Update the database
taPerson.Update(tblPerson);
// Use PersonTableAdapter to insert new record into the database
// directly
taPerson.Insert("Ge", "Jialiang", DateTime.Now, null,
ReadImage(@"MSDN.jpg"), 2);
// Create the CourseTableAdapter and fill the Course DataTable
CourseTableAdapter taCourse = new CourseTableAdapter();
SQLServer2005DBDataSet.CourseDataTable tblCourse = dsSQLServer.Course;
taCourse.Fill(tblCourse);
// Create the CourseGradeTableAdapter and fill the CourseGrade
// DataTable
CourseGradeTableAdapter taCourseGrade = new CourseGradeTableAdapter();
SQLServer2005DBDataSet.CourseGradeDataTable tblCourseGrade =
dsSQLServer.CourseGrade;
taCourseGrade.Fill(tblCourseGrade);
/////////////////////////////////////////////////////////////////////
// Perform the query operation in one DataTable
//
Console.WriteLine("\nQuery people whose first name is Roger:");
// Person the query
var query = from p in tblPerson.AsEnumerable()
where p.FirstName == "Roger"
select p;
// Display the query results
foreach (var p in query)
{
Console.WriteLine("ID = {0}, Name = {1} {2}", p.PersonID,
p.FirstName, p.LastName);
}
/////////////////////////////////////////////////////////////////////
// Perform the query operation across multiple DataTables
//
Console.WriteLine("\nQuery the max grade of each course:");
// Perform the query and get a collection of hte anonymous type,
// new { int CourseID, string Title, decimal TopGrade }
var courses = from grade in tblCourseGrade.AsEnumerable()
group grade by grade.CourseID into g
join cur in tblCourse.AsEnumerable()
on g.Key equals cur.CourseID
select new
{
CourseID = g.Key,
Title = cur.Title,
TopGrade = g.Max(gra => gra.IsGradeNull() ?
decimal.Zero : gra.Grade)
};
// Display the query results
foreach (var c in courses)
{
Console.WriteLine("Course = {0}, TopGrade = {1}", c.Title,
c.TopGrade);
}
/////////////////////////////////////////////////////////////////////
// Perform the query operation across multiple related DataTables
//
Console.WriteLine("\nQuery all grades that Nino got:");
// Perform the query betweem related DataTables and get the anonymous
// type, new { PersonRow Person, CourseRow Course, CourseGradeRow
// CourseGrade}
var grades = from per in tblPerson.AsEnumerable()
from gra in per.GetCourseGradeRows()
join cur in tblCourse.AsEnumerable()
on gra.CourseID equals cur.CourseID
where per.FirstName == "Nino"
select new
{
Person = per,
Course = cur,
CourseGrade = gra
};
// Display the query results
foreach (var grade in grades)
{
Console.WriteLine("FirstName = {0}, Course= {1}, Grade= {2}",
grade.Person.FirstName, grade.Course.Title,
grade.CourseGrade.Grade);
}
}
