public int GetMaxId()
{
int maxId = 0;
StringBuilder strSql = new StringBuilder();
//select Max([DepartmentID]) from Department
strSql.Append("SELECT MAX([DepartmentID]) FROM Department");
LinqConextClass context = new LinqConextClass();
try
{
context.Open();
DataContext dataContext = context.Context;
IEnumerable collection = dataContext.ExecuteQuery((new int()).GetType(), strSql.ToString());
foreach (int item in collection)
{
maxId = item;
}
}
catch (Exception exp)
{
throw exp;
}
finally
{
context.Close();
}
return(maxId);
}
public List <testtable> GetList(string where)
{
List <testtable> testList = new List <testtable>();
StringBuilder strSql = new StringBuilder();
//select col1,col2 from testtable;
strSql.Append("select col1,col2 from testtable");
strSql.Append(where);
LinqConextClass context = new LinqConextClass();
try
{
context.Open();
DataContext dataContext = context.Context;
IEnumerable collections = dataContext.ExecuteQuery((new testtable()).GetType(), strSql.ToString());
foreach (var item in collections)
{
testtable temp = item as testtable;
testList.Add(temp);
}
}
catch (Exception exp)
{
throw exp;
}
finally
{
context.Close();
}
return(testList);
}
public void Delete(testtable table)
{
StringBuilder strSql = new StringBuilder();
//delete from testtable where col1='55'
strSql.Append("delete from testtable ");
strSql.Append(string.Format("where col1='{0}'", table.col1));
//strSql.Append("where '{0}'='{0}' and col1='{1}'");
LinqConextClass context = new LinqConextClass();
try
{
context.Open();
DataContext dataContext = context.Context;
int x = dataContext.ExecuteCommand(strSql.ToString());
}
catch (Exception exp)
{
throw exp;
}
finally
{
context.Close();
}
}
public void Save(testtable table)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into testtable(");
strSql.Append("col1,col2)");
strSql.Append(" values (");
strSql.Append(string.Format("'{0}','{1}'", table.col1, table.col2));
strSql.Append(")");
LinqConextClass context = new LinqConextClass();
try
{
context.Open();
DataContext dataContext = context.Context;
int x = dataContext.ExecuteCommand(strSql.ToString());
}
catch (Exception exp)
{
throw exp;
}
finally
{
context.Close();
}
}
public List <Department> GetList(string where)
{
List <Department> deplist = new List <Department>();
StringBuilder strSql = new StringBuilder();
//select [DepartmentID],[Name],[Budget],[StartDate],[Administrator] from Department
strSql.Append("SELECT [DepartmentID],[Name],[Budget],[StartDate],[Administrator] FROM Department ");
strSql.Append(where);
LinqConextClass context = new LinqConextClass();
try
{
context.Open();
DataContext dataContext = context.Context;
IEnumerable collections = dataContext.ExecuteQuery((new Department()).GetType(), strSql.ToString());
///jwm test
//IEnumerable<Department> vars = from Department ncol in collections where ncol.DepartmentID > 100 select ncol;
//foreach (var item in vars)
//{
// Department temp = item;
// deplist.Add(temp);
//}
//vars.ElementAt(0);
//vars.Count();
//end jwm test
foreach (var item in collections)
{
Department temp = item as Department;
deplist.Add(temp);
}
}
catch (Exception exp)
{
throw exp;
}
finally
{
context.Close();
}
return(deplist);
}
public void Save(Department dept)
{
//INSERT INTO Department (
//[DepartmentID] ,
//[Name] ,
//[Budget] ,
//[StartDate] ,
//[Administrator] ) VALUES (123,'test1',456,'2010-4-12 0:00:00',456)
StringBuilder strSql = new StringBuilder();
strSql.Append("INSERT INTO Department (");
strSql.Append("[DepartmentID],[Name],[Budget],[StartDate],[Administrator] )");
strSql.Append(" values (");
string dateTime = string.Empty;
if (dept.StartDate == null)
{
dateTime = string.Empty;
}
else
{
DateTime dateTimeTemp = (DateTime)dept.StartDate;
dateTime = dateTimeTemp.ToString("yyyy-MM-dd HH:mm:ss");
}
strSql.Append(string.Format("{0},'{1}',{2},'{3}',{4}",
dept.DepartmentID, dept.Name, dept.Budget, dateTime, dept.Administrator));
strSql.Append(")");
LinqConextClass context = new LinqConextClass();
try
{
context.Open();
DataContext dataContext = context.Context;
int x = dataContext.ExecuteCommand(strSql.ToString());
}
catch (Exception exp)
{
throw exp;
}
finally
{
context.Close();
}
}
