public static void MultipleDataSets(string strSQL)
{
DataSetSerialization ser = new DataSetSerialization();
using (System.Data.Common.DbDataReader dr = SQL.ExecuteReader(strSQL
, System.Data.CommandBehavior.CloseConnection
| System.Data.CommandBehavior.SequentialAccess
)
)
{
Table tbl = null;
do
{
tbl = new Table();
for (int i = 0; i < dr.FieldCount; ++i)
{
tbl.Columns.Add(
new ColumnInfo()
{
ColumnName = dr.GetName(i),
FieldType = dr.GetFieldType(i)
}
);
} // Next i
if (dr.HasRows)
{
while (dr.Read())
{
object[] thisRow = new object[dr.FieldCount];
for (int i = 0; i < dr.FieldCount; ++i)
{
thisRow[i] = dr.GetValue(i);
} // Next i
tbl.Rows.Add(thisRow);
} // Whend
} // End if (dr.HasRows)
ser.Tables.Add(tbl);
} while (dr.NextResult());
} // End Using dr
string str = EasyJSON.JsonHelper.SerializePretty(ser);
System.Console.WriteLine(str);
DataSetSerialization ser2 = EasyJSON.JsonHelper.Deserialize <DataSetSerialization>(str);
System.Console.WriteLine(ser2);
} // End Sub MultipleDataSets
private void NextResult()
{
if (reader.NextResult())
{
readCount++;
gridIndex++;
IsConsumed = false;
}
else
{
// happy path; close the reader cleanly - no
// need for "Cancel" etc
reader.Dispose();
reader = null;
callbacks?.OnCompleted();
Dispose();
}
}
public async Task <object> InsertNewLabListWithSelect(Lab_list_detail ldata)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
{
return(WebApiApplication.CONNECTDBERRSTRING);
}
List <Lab_list_detail> result = new List <Lab_list_detail>();
string temp1tablename = "#temp1";
string temp2tablename = "#temp2";
string createtabletemp1 = string.Format("create table {0} (" +
"[row_num] INT IDENTITY(1, 1) NOT NULL," +
"[{1}] INT NOT NULL," +
"PRIMARY KEY ([row_num])) ", temp1tablename, FieldName.LAB_NUM);
string createtabletemp2 = string.Format("create table {0} (" +
"[row_num] INT IDENTITY(1, 1) NOT NULL," +
"[{1}] INT NULL," +
"PRIMARY KEY ([row_num])) "
, temp2tablename, Lab_officer.FieldName.OFFICER);
string insertintotemp1 = string.Format("INSERT INTO {0} " +
"select * from " +
"(insert into {1} output inserted.{2} values " +
"('{3}','{4}','{5}',{6})) as outputinsert ",
temp1tablename, FieldName.TABLE_NAME, FieldName.LAB_NUM,
ldata.name, ldata.room, ldata.curri_id, ldata.aca_year);
string insertintotemp2 = string.Format("INSERT INTO {0} VALUES (null)", temp2tablename);
foreach (Personnel_with_t_name p in ldata.officer)
{
insertintotemp2 += string.Format(",({0})", p.user_id);
}
string insertintolabofficer = string.Format(" INSERT INTO {0} " +
"select {1},{2} from {3},{4} where {2} is not null ",
Lab_officer.FieldName.TABLE_NAME, FieldName.LAB_NUM, Lab_officer.FieldName.OFFICER,
temp1tablename, temp2tablename);
curri_id = ldata.curri_id;
aca_year = ldata.aca_year;
string selectcmd = getSelectByCurriculumAcademicCommand();
d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} {4} {5} END", createtabletemp1, createtabletemp2,
insertintotemp1, insertintotemp2, insertintolabofficer, selectcmd);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
do
{
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
if (data.Columns.Contains(FieldName.ROOM))
{
result.Add(new Lab_list_detail
{
lab_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.LAB_NUM].Ordinal]),
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
name = item.ItemArray[data.Columns[FieldName.NAME].Ordinal].ToString(),
room = item.ItemArray[data.Columns[FieldName.ROOM].Ordinal].ToString()
});
}
else
{
int labid = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.LAB_NUM].Ordinal]);
result.First(t => t.lab_num == labid).officer.Add(new Personnel_with_t_name
{
user_id = Convert.ToInt32(item.ItemArray[data.Columns[Lab_officer.FieldName.OFFICER].Ordinal]),
t_name = item.ItemArray[data.Columns[User_list.FieldName.T_PRENAME].Ordinal].ToString() + item.ItemArray[data.Columns[User_list.FieldName.T_NAME].Ordinal].ToString()
});
}
}
data.Dispose();
}
else if (!res.IsClosed)
{
if (!res.NextResult())
{
break;
}
}
} while (!res.IsClosed);
res.Close();
}
catch (Exception ex)
{
//Handle error from sql execution
return(ex.Message);
}
finally
{
//Whether it success or not it must close connection in order to end block
d.SQLDisconnect();
}
return(result);
}
public async Task <object> UpdateLabListWithSelect(Lab_list_detail ldata)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
{
return(WebApiApplication.CONNECTDBERRSTRING);
}
List <Lab_list_detail> result = new List <Lab_list_detail>();
string updatelablistcmd = string.Format("update {0} set {1} = '{2}', {3} = '{4}' " +
"where {5} = {6} ", FieldName.TABLE_NAME, FieldName.NAME, ldata.name,
FieldName.ROOM, ldata.room, FieldName.LAB_NUM, ldata.lab_num);
string deletefromlabofficer = string.Format("DELETE FROM {0} where {1} = {2} ",
Lab_officer.FieldName.TABLE_NAME, Lab_officer.FieldName.LAB_NUM, ldata.lab_num);
string insertintolabofficer = string.Format("INSERT INTO {0} values ",
Lab_officer.FieldName.TABLE_NAME);
foreach (Personnel_with_t_name p in ldata.officer)
{
insertintolabofficer += string.Format("({0},{1})", ldata.lab_num, p.user_id);
if (p != ldata.officer.Last())
{
insertintolabofficer += ",";
}
}
curri_id = ldata.curri_id;
aca_year = ldata.aca_year;
string selectcmd = getSelectByCurriculumAcademicCommand();
string updatecondition = string.Format("if exists (select * from {0} where {1} = {2}) ", FieldName.TABLE_NAME, FieldName.LAB_NUM, ldata.lab_num);
d.iCommand.CommandText = string.Format("{4} BEGIN {0} {1} {2} {3} END", updatelablistcmd,
deletefromlabofficer, insertintolabofficer, selectcmd, updatecondition);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
do
{
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
if (data.Columns.Contains(FieldName.ROOM))
{
result.Add(new Lab_list_detail
{
lab_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.LAB_NUM].Ordinal]),
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
name = item.ItemArray[data.Columns[FieldName.NAME].Ordinal].ToString(),
room = item.ItemArray[data.Columns[FieldName.ROOM].Ordinal].ToString()
});
}
else
{
int labid = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.LAB_NUM].Ordinal]);
result.First(t => t.lab_num == labid).officer.Add(new Personnel_with_t_name
{
user_id = Convert.ToInt32(item.ItemArray[data.Columns[Lab_officer.FieldName.OFFICER].Ordinal]),
t_name = item.ItemArray[data.Columns[User_list.FieldName.T_PRENAME].Ordinal].ToString() + item.ItemArray[data.Columns[User_list.FieldName.T_NAME].Ordinal].ToString()
});
}
}
data.Dispose();
}
else if (!res.IsClosed)
{
if (!res.NextResult())
{
break;
}
}
} while (!res.IsClosed);
res.Close();
}
catch (Exception ex)
{
//Handle error from sql execution
return(ex.Message);
}
finally
{
//Whether it success or not it must close connection in order to end block
d.SQLDisconnect();
}
return(result);
}
public async Task <object> SelectByCurriculumAndTitle()
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
{
return(WebApiApplication.CONNECTDBERRSTRING);
}
Extra_privilege_by_type_with_privilege_choices result = new Extra_privilege_by_type_with_privilege_choices();
string temp5tablename = "#temp5";
string selectTitle = string.Format("select * from {0} where {1} = {2} ", Title.FieldName.TABLE_NAME,
Title.FieldName.TITLE_CODE, title_code);
string selectPrivilegeChoices = string.Format("select * from {0} where {1} = {2} ", Title_privilege.FieldName.TABLE_NAME,
Title_privilege.FieldName.TITLE_CODE, title_code);
string createtabletemp5 = string.Format("create table {0} ( " +
"[row_num] INT IDENTITY(1, 1) NOT NULL," +
"[{1}] INT NOT NULL," +
"[{2}] {6} NOT NULL," +
"[{3}] INT NOT NULL," +
"[{4}] INT NOT NULL," +
"[{5}] VARCHAR(40) NOT NULL, " +
"PRIMARY KEY([row_num]) " +
") " +
"alter table {0} " +
"alter column [{2}] {6} collate database_default " +
"alter table {0} " +
"alter column [{5}] VARCHAR(40) collate database_default ",
temp5tablename, FieldName.USER_TYPE_ID, FieldName.CURRI_ID, FieldName.TITLE_CODE,
FieldName.TITLE_PRIVILEGE_CODE, User_type.FieldName.USER_TYPE_NAME,
DBFieldDataType.CURRI_ID_TYPE
);
string insertintotemp5_maindata = string.Format("insert into {9} " +
"select {0}.*,{1} from {0},{2} " +
"where {3} = {4} and {5} = '{6}' " +
"and {0}.{7} = {2}.{8} ",
FieldName.TABLE_NAME, User_type.FieldName.USER_TYPE_NAME, User_type.FieldName.TABLE_NAME,
FieldName.TITLE_CODE, title_code, FieldName.CURRI_ID, curri_id,
FieldName.USER_TYPE_ID, User_type.FieldName.USER_TYPE_ID, temp5tablename
);
string insertintotemp5_setdefaultdata = string.Format("insert into {12} " +
"select {0}.{1},'{2}' as {3},{4},{5},{6} " +
"from {0},{7} " +
"where {4} = {8} " +
"and not exists " +
"(select * from {12} where " +
"{0}.{1} = {9} " +
"and {0}.{4} = {10}) " +
"and {0}.{1} = {7}.{11} ",
Default_privilege_by_type.FieldName.TABLE_NAME, Default_privilege_by_type.FieldName.USER_TYPE_ID,
curri_id, FieldName.CURRI_ID, Default_privilege_by_type.FieldName.TITLE_CODE,
Default_privilege_by_type.FieldName.TITLE_PRIVILEGE_CODE, User_type.FieldName.USER_TYPE_NAME,
User_type.FieldName.TABLE_NAME, title_code, FieldName.USER_TYPE_ID, FieldName.TITLE_CODE,
User_type.FieldName.USER_TYPE_ID, temp5tablename
);
string selectfromtemp5 = string.Format("select * from {0} order by {1} ", temp5tablename, FieldName.USER_TYPE_ID);
d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} {4} {5} END", createtabletemp5, insertintotemp5_maindata,
insertintotemp5_setdefaultdata, selectTitle, selectPrivilegeChoices, selectfromtemp5);
try
{
//Set result's curri_id
result.curri_id = curri_id;
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
do
{
if (res.HasRows)
{
DataTable tabledata = new DataTable();
tabledata.Load(res);
foreach (DataRow item in tabledata.Rows)
{
if (tabledata.Columns.Contains(Title.FieldName.NAME))
{
//Set title name from title table result
result.title_code = Convert.ToInt32(item.ItemArray[tabledata.Columns[Title.FieldName.TITLE_CODE].Ordinal]);
result.name = item.ItemArray[tabledata.Columns[Title.FieldName.NAME].Ordinal].ToString();
}
else if (tabledata.Columns.Contains(Title_privilege.FieldName.PRIVILEGE))
{
//Set privilege choice for target title
result.choices.Add(new Privilege_choice
{
title_privilege_code = Convert.ToInt32(item.ItemArray[tabledata.Columns[Title_privilege.FieldName.TITLE_PRIVILEGE_CODE].Ordinal]),
privilege = item.ItemArray[tabledata.Columns[Title_privilege.FieldName.PRIVILEGE].Ordinal].ToString()
});
}
else
{
//Read main privilege data
int title_priv_code = Convert.ToInt32(item.ItemArray[tabledata.Columns[FieldName.TITLE_PRIVILEGE_CODE].Ordinal]);
result.privilege_list.Add(new User_type_privilege
{
user_type_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[FieldName.USER_TYPE_ID].Ordinal]),
user_type = item.ItemArray[tabledata.Columns[User_type.FieldName.USER_TYPE_NAME].Ordinal].ToString(),
privilege = new Privilege_choice
{
title_privilege_code = title_priv_code,
//Find privilege caption from choices array
privilege = result.choices.First(t => t.title_privilege_code == title_priv_code).privilege
}
});
}
}
tabledata.Dispose();
}
else if (!res.IsClosed)
{
if (!res.NextResult())
{
break;
}
}
} while (!res.IsClosed);
res.Close();
}
catch (Exception ex)
{
//Handle error from sql execution
return(ex.Message);
}
finally
{
//Whether it success or not it must close connection in order to end block
d.SQLDisconnect();
}
return(result);
}
}// End Sub
public static void SerializeDataTableAsAssociativeJsonArray(
System.Data.Common.DbCommand cmd
, Microsoft.AspNetCore.Http.HttpContext context
, bool pretty
, System.Text.Encoding enc)
{
SqlService service = (SqlService)context.RequestServices.GetService(typeof(SqlService));
using (System.IO.TextWriter sw = new System.IO.StreamWriter(context.Response.Body, enc))
{
using (Newtonsoft.Json.JsonTextWriter jsonWriter = new Newtonsoft.Json.JsonTextWriter(sw))
{
if (pretty)
{
jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented;
}
// jsonWriter.WriteStartObject();
// jsonWriter.WritePropertyName("tables");
// jsonWriter.WriteStartArray();
using (System.Data.Common.DbConnection con = service.Connection)
{
cmd.Connection = con;
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
try
{
using (System.Data.Common.DbDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess
| System.Data.CommandBehavior.CloseConnection
))
{
do
{
// jsonWriter.WriteStartObject(); // tbl = new Table();
//jsonWriter.WritePropertyName("columns");
//// WriteArray(jsonWriter, dr);
//WriteAssociativeArray(jsonWriter, dr);
//jsonWriter.WritePropertyName("rows");
jsonWriter.WriteStartArray();
if (dr.HasRows)
{
string[] columns = new string[dr.FieldCount];
for (int i = 0; i < dr.FieldCount; i++)
{
columns[i] = dr.GetName(i);
} // Next i
while (dr.Read())
{
// jsonWriter.WriteStartArray(); // object[] thisRow = new object[dr.FieldCount];
jsonWriter.WriteStartObject(); // tbl = new Table();
for (int i = 0; i < dr.FieldCount; ++i)
{
jsonWriter.WritePropertyName(columns[i]);
object obj = dr.GetValue(i);
if (obj == System.DBNull.Value)
{
obj = null;
}
jsonWriter.WriteValue(obj);
} // Next i
// jsonWriter.WriteEndArray(); // tbl.Rows.Add(thisRow);
jsonWriter.WriteEndObject();
} // Whend
} // End if (dr.HasRows)
jsonWriter.WriteEndArray();
// jsonWriter.WriteEndObject(); // ser.Tables.Add(tbl);
} while (dr.NextResult());
} // End using dr
}
catch (System.Exception ex)
{
System.Console.WriteLine(ex.Message);
throw;
}
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
} // End using con
// jsonWriter.WriteEndArray();
// jsonWriter.WriteEndObject();
jsonWriter.Flush();
} // End Using jsonWriter
} // End Using sw
} // End Sub SerializeDataTableAsAssociativeJsonArray
public override void ExecuteResult(System.Web.Mvc.ControllerContext context)
{
context.HttpContext.Response.ContentType = "application/json";
context.HttpContext.Response.ContentEncoding = System.Text.Encoding.UTF8;
using (Newtonsoft.Json.JsonTextWriter jsonWriter = new Newtonsoft.Json.JsonTextWriter(context.HttpContext.Response.Output))
{
jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented;
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("Tables");
jsonWriter.WriteStartArray();
using (System.Data.Common.DbConnection con = SQL.CreateConnection())
{
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
using (System.Data.Common.DbCommand cmd = this.GetCommand(con))
{
using (System.Data.Common.DbDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess
| System.Data.CommandBehavior.CloseConnection
))
{
do
{
jsonWriter.WriteStartObject(); // tbl = new Table();
jsonWriter.WritePropertyName("Columns");
jsonWriter.WriteStartArray();
for (int i = 0; i < dr.FieldCount; ++i)
{
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("ColumnName");
jsonWriter.WriteValue(dr.GetName(i));
jsonWriter.WritePropertyName("FieldType");
jsonWriter.WriteValue(SQL.GetAssemblyQualifiedNoVersionName(dr.GetFieldType(i)));
jsonWriter.WriteEndObject();
} // Next i
jsonWriter.WriteEndArray();
jsonWriter.WritePropertyName("Rows");
jsonWriter.WriteStartArray();
if (dr.HasRows)
{
while (dr.Read())
{
object[] thisRow = new object[dr.FieldCount];
jsonWriter.WriteStartArray(); // object[] thisRow = new object[dr.FieldCount];
for (int i = 0; i < dr.FieldCount; ++i)
{
jsonWriter.WriteValue(dr.GetValue(i));
} // Next i
jsonWriter.WriteEndArray(); // tbl.Rows.Add(thisRow);
} // Whend
} // End if (dr.HasRows)
jsonWriter.WriteEndArray();
jsonWriter.WriteEndObject(); // ser.Tables.Add(tbl);
} while (dr.NextResult());
} // End using dr
} // End using cmd
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
} // End using con
jsonWriter.WriteEndArray();
jsonWriter.WriteEndObject();
jsonWriter.Flush();
} // End Using jsonWriter
context.HttpContext.Response.Output.Flush();
context.HttpContext.Response.OutputStream.Flush();
context.HttpContext.Response.Flush();
} // End Sub SerializeLargeDataset
public static void LinqTest()
{
System.Collections.Generic.List <string> ls = new System.Collections.Generic.List <string>();
ls.Add("foo");
ls.Add("bar");
ls.Add("foobar");
int oobj = 123;
Person someOne = new Person()
{
Name = "foo", Email = "*****@*****.**", SnailMail = "Snail"
};
// object inexistant = GetProperty(someOne, "Inexistant");
object myName = GetProperty(someOne, "Name");
string myNameString = GetProperty <Person, string>(someOne, "Name");
int?nullMe = GetProperty <Person, int?>(someOne, "NullableNumber");
object nullNumObj = GetProperty(someOne, "NullableNumber");
int? nullNum = GetProperty <Person, int?>(someOne, "NullableNumber");
System.Console.WriteLine(nullNum);
SetProperty(someOne, "NullableNumber", null);
System.Console.WriteLine(someOne);
SetProperty(someOne, "NullableNumber", -123);
System.Console.WriteLine(someOne);
SetProperty(someOne, "NullableNumber", "-123");
System.Console.WriteLine(someOne);
SetProperty(someOne, "NullableNumber", System.DBNull.Value);
System.Console.WriteLine(someOne);
// object obj = System.DBNull.Value;
// SetProperty(someOne, "NullableNumber", obj);
System.Console.WriteLine(myName);
System.Console.WriteLine(myNameString);
// SetProperty(someOne, "Anumber", oobj);
// SetProperty(someOne, "SnailMail", "Turtle Mail");
// SetProperty(someOne, "Email", "SpamMail");
T_User ben = new T_User();
int cnt = GetProperty <System.Collections.Generic.List <string>, int>(ls, "cOuNt");
object objCount = GetProperty(ls, "cOuNt");
System.Console.WriteLine(cnt);
// b15186d6-adb1-4c8a-bbfa-830b24417e8b
string SQL = @"SELECT 'B15186D6-ADB1-4C8A-BBFA-830B24417E8B' AS BE_UID, '123' AS BE_ID, 'Carbon Unit' AS BE_Name, '*****@*****.**' AS EMail, 'omg' AS SnailMail, CAST(NULL AS integer) AS NullableNumber;";
// SQL = @"SELECT CAST(NULL AS uniqueidentifier) AS BE_UID"; // Test NULLing non-null type error message...
using (System.Data.Common.DbDataReader rdr = null) // CoinBaseSharp.SQL.ExecuteReader(SQL))
{
do
{
int fieldCount = rdr.FieldCount;
System.Type[] ts = new System.Type[fieldCount];
string[] fieldNames = new string[fieldCount];
System.Action <T_User, object>[] fieldSetters = new System.Action <T_User, object> [fieldCount];
for (int i = 0; i < fieldCount; ++i)
{
ts[i] = rdr.GetFieldType(i);
fieldNames[i] = rdr.GetName(i);
fieldSetters[i] = GetSetter <T_User>(fieldNames[i]);
} // Next i
if (rdr.HasRows)
{
while (rdr.Read())
{
for (int i = 0; i < fieldCount; ++i)
{
object objValue = rdr.GetValue(i);
// if (object.ReferenceEquals(objValue, System.DBNull.Value)) objValue = null;
System.Console.WriteLine(ts[i]);
//int abc = 123;
// SetProperty(ben, fieldNames[i], abc);
// SetProperty(ben, fieldNames[i], objValue);
fieldSetters[i](ben, objValue);
System.Console.WriteLine(objValue);
} // Next i
} // Whend
} // End if (rdr.HasRows)
} while (rdr.NextResult());
} // End Using rdr
System.Console.WriteLine(ben.BE_UID);
} // End Sub LinqTest
public static void Test()
{
System.Data.SqlClient.SqlConnectionStringBuilder csb = new System.Data.SqlClient.SqlConnectionStringBuilder();
csb.DataSource = System.Environment.MachineName;
csb.IntegratedSecurity = true;
if (!csb.IntegratedSecurity)
{
csb.UserID = "DAL_Test";
csb.Password = "******";
} // End if (!csb.IntegratedSecurity)
csb.InitialCatalog = "COR_Basic_Demo_V4";
cDAL DAL = cDAL.CreateInstance();
DAL.ConnectionString = csb.ConnectionString;
using (System.Data.Common.DbConnection dbConnection = DAL.GetConnection())
{
object objUser1 = DAL.ExecuteScalar("SELECT TOP 1 BE_User FROM T_Benutzer ORDER BY BE_User;", dbConnection);
object objUser2 = DAL.ExecuteScalar("SELECT TOP 1 BE_User FROM T_Benutzer ORDER BY BE_ID;", dbConnection);
DAL.ExecuteNonQuery("UPDATE T_Benutzer SET BE_Hash = BE_Hash;", dbConnection);
DAL.ExecuteNonQuery("UPDATE T_Benutzer SET BE_Hash = BE_Hash;", dbConnection);
#if WITH_CONNECTION
//using (System.Data.Common.DbDataReader reader = DAL.ExecuteReader("SELECT * FROM T_Benutzer; SELECT * FROM T_Benutzergruppen;", dbConnection))
using (System.Data.Common.DbDataReader reader = DAL.ExecuteReader_Buggy("SELECT * FROM T_Benutzer; SELECT * FROM T_Benutzergruppen;"))
#else
DAL.ExecuteReader("SELECT * FROM T_Benutzer; SELECT * FROM T_Benutzergruppen;", delegate(System.Data.Common.DbDataReader reader)
#endif
{
do
{
for (int i = 0; i < reader.FieldCount; ++i)
{
string fieldName = reader.GetName(i);
System.Type fieldType = reader.GetFieldType(i);
System.Console.WriteLine("{0}:\t{1}\t{2}", i, fieldName, fieldType.ToString());
} // Next i
if (reader.HasRows)
{
int rowCount = 1;
while (reader.Read())
{
System.Console.WriteLine(@"Row {0}", rowCount);
for (int i = 0; i < reader.FieldCount; ++i)
{
string fieldName = reader.GetName(i);
object fieldValue = reader.GetValue(i);
System.Console.WriteLine(@" - {0}: {1}", fieldName, System.Convert.ToString(fieldValue));
} // Next i
++rowCount;
} // Whend
--rowCount;
} // End if (reader.HasRows)
} while (reader.NextResult());
} // End Using reader
#if !WITH_CONNECTION
);
#endif
object objUser3 = DAL.ExecuteScalar("SELECT TOP 1 BE_User FROM T_Benutzer ORDER BY BE_Hash;", dbConnection);
object objUser4 = DAL.ExecuteScalar("SELECT TOP 1 BE_User FROM T_Benutzer ORDER BY BE_Passwort;", dbConnection);
} // End Using dbConnection
} // End Sub Test
public async Task <object> SelectAllCurriculumsAndAllPresidents()
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
{
return(WebApiApplication.CONNECTDBERRSTRING);
}
Curriculums_presidents_detail result = new Curriculums_presidents_detail();
string selcurriculumdata = string.Format("select {0}.{1},{2} " +
"from {0},{3} " +
"where {0}.{1} = {3}.{4} and {5} = {6} ",
Cu_curriculum.FieldName.TABLE_NAME, Cu_curriculum.FieldName.CURRI_ID, Cu_curriculum.FieldName.CURR_TNAME,
Curriculum_academic.FieldName.TABLE_NAME, Curriculum_academic.FieldName.CURRI_ID,
Curriculum_academic.FieldName.ACA_YEAR, ParameterName.ACA_YEAR);
string selallpresident = string.Format("select {0}.*,{1},{2},{3},{4} from {0},{5} " +
"where {6} = {7} and {8} = {9} ",
FieldName.TABLE_NAME, Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME, Teacher.FieldName.FILE_NAME_PIC,
Teacher.FieldName.EMAIL, User_list.FieldName.TABLE_NAME, User_list.FieldName.USER_ID, FieldName.TEACHER_ID,
FieldName.ACA_YEAR, ParameterName.ACA_YEAR);
string selallteacherwithcurri = string.Format("select {0}.*,{1},{2},{3},{4} from {0},{5} " +
"where {6} = 1 and {0}.{7} = {5}.{8} " +
"and {9} in (select {10} from {11} where {12} = {13}) ",
User_curriculum.FieldName.TABLE_NAME, Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME, Teacher.FieldName.FILE_NAME_PIC,
Teacher.FieldName.EMAIL, User_list.FieldName.TABLE_NAME, User_list.FieldName.USER_TYPE_ID,
User_curriculum.FieldName.USER_ID, User_list.FieldName.USER_ID,
User_curriculum.FieldName.CURRI_ID, Curriculum_academic.FieldName.CURRI_ID, Curriculum_academic.FieldName.TABLE_NAME,
Curriculum_academic.FieldName.ACA_YEAR, ParameterName.ACA_YEAR);
d.iCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter(ParameterName.ACA_YEAR, aca_year));
d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} END", selcurriculumdata, selallpresident, selallteacherwithcurri);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
do
{
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
if (data.Columns.Count == 2)
{
foreach (DataRow item in data.Rows)
{
curri_id = item.ItemArray[data.Columns[Cu_curriculum.FieldName.CURRI_ID].Ordinal].ToString();
result.all_curri_id.Add(curri_id);
result.all_presidents[curri_id] = new Curri_with_pres_and_cand
{
curri_tname = item.ItemArray[data.Columns[Cu_curriculum.FieldName.CURR_TNAME].Ordinal].ToString()
};
}
}
else if (data.Columns.Count == 7)
{
foreach (DataRow item in data.Rows)
{
curri_id = item.ItemArray[data.Columns[Cu_curriculum.FieldName.CURRI_ID].Ordinal].ToString();
result.all_presidents[curri_id].presidents.Add(new Personnel_brief_detail
{
tname = NameManager.GatherPreName(item.ItemArray[data.Columns[User_list.FieldName.T_PRENAME].Ordinal].ToString()) + item.ItemArray[data.Columns[User_list.FieldName.T_NAME].Ordinal].ToString(),
pic = MiscUtils.GatherProfilePicturePath(item.ItemArray[data.Columns[Teacher.FieldName.FILE_NAME_PIC].Ordinal].ToString()),
email = item.ItemArray[data.Columns[Teacher.FieldName.EMAIL].Ordinal].ToString(),
user_id = item.ItemArray[data.Columns[FieldName.TEACHER_ID].Ordinal].ToString()
});
}
}
else
{
foreach (DataRow item in data.Rows)
{
curri_id = item.ItemArray[data.Columns[Cu_curriculum.FieldName.CURRI_ID].Ordinal].ToString();
result.all_presidents[curri_id].candidates.Add(new Personnel_brief_detail
{
tname = NameManager.GatherPreName(item.ItemArray[data.Columns[User_list.FieldName.T_PRENAME].Ordinal].ToString()) + item.ItemArray[data.Columns[User_list.FieldName.T_NAME].Ordinal].ToString(),
pic = MiscUtils.GatherProfilePicturePath(item.ItemArray[data.Columns[Teacher.FieldName.FILE_NAME_PIC].Ordinal].ToString()),
email = item.ItemArray[data.Columns[Teacher.FieldName.EMAIL].Ordinal].ToString(),
user_id = item.ItemArray[data.Columns[User_list.FieldName.USER_ID].Ordinal].ToString()
});
}
}
data.Dispose();
}
else if (!res.IsClosed)
{
if (!res.NextResult())
{
break;
}
}
} while (!res.IsClosed);
res.Close();
}
catch (Exception ex)
{
//Handle error from sql execution
return(ex.Message);
}
finally
{
//Whether it success or not it must close connection in order to end block
d.SQLDisconnect();
}
return(result);
}
public async Task <object> SelectByIndicator()
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
{
return(WebApiApplication.CONNECTDBERRSTRING);
}
Others_evaluation_s_indic_name_list_with_file_name result = new Others_evaluation_s_indic_name_list_with_file_name();
string temp5tablename = "#temp5";
string createtabletemp5 = string.Format("CREATE TABLE {0}(" +
"[row_num] INT IDENTITY(1, 1) NOT NULL," +
"[{1}] VARCHAR(2000) NULL," +
"[{2}] INT NULL," +
"[{3}] INT NULL," +
"[{4}] INT NULL," +
"[{5}] {16} NULL," +
"[{6}] CHAR NULL," +
"[{7}] VARCHAR(MAX) NULL," +
"[{8}] VARCHAR(MAX) NULL," +
"[{9}] DATE NULL," +
"[{10}] TIME(0) NULL," +
"[{11}] {15} NULL," +
"[{12}] {14} NULL," +
"[{13}] INT NULL," +
"[{17}] VARCHAR(16) NULL," +
"[{18}] VARCHAR(60) NULL," +
"PRIMARY KEY([row_num])) " +
"ALTER TABLE {0} " +
"ALTER COLUMN [{1}] VARCHAR(2000) collate DATABASE_DEFAULT " +
"ALTER TABLE {0} " +
"ALTER COLUMN [{5}] {16} collate DATABASE_DEFAULT " +
"ALTER TABLE {0} " +
"ALTER COLUMN [{6}] CHAR collate DATABASE_DEFAULT " +
"ALTER TABLE {0} " +
"ALTER COLUMN [{7}] VARCHAR(MAX) collate DATABASE_DEFAULT " +
"ALTER TABLE {0} " +
"ALTER COLUMN [{8}] VARCHAR(MAX) collate DATABASE_DEFAULT " +
"ALTER TABLE {0} " +
"ALTER COLUMN [{11}] {15} collate DATABASE_DEFAULT " +
"ALTER TABLE {0} " +
"ALTER COLUMN [{12}] {14} collate DATABASE_DEFAULT " +
"ALTER TABLE {0} " +
"ALTER COLUMN [{17}] VARCHAR(16) collate DATABASE_DEFAULT " +
"ALTER TABLE {0} " +
"ALTER COLUMN [{18}] VARCHAR(60) collate DATABASE_DEFAULT ",
temp5tablename, Sub_indicator.FieldName.SUB_INDICATOR_NAME,
FieldName.OTHERS_EVALUATION_ID, FieldName.INDICATOR_NUM, FieldName.SUB_INDICATOR_NUM,
FieldName.ASSESSOR_ID, FieldName.EVALUATION_SCORE, FieldName.STRENGTH, FieldName.IMPROVE,
FieldName.DATE, FieldName.TIME, Evidence.FieldName.FILE_NAME, FieldName.CURRI_ID,
FieldName.ACA_YEAR, DBFieldDataType.CURRI_ID_TYPE, DBFieldDataType.FILE_NAME_TYPE,
DBFieldDataType.USERNAME_TYPE, Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME);
string insertintotemp5_1 = string.Format("insert into {13} " +
"select {2}, {0}.*,{17},{18} " +
"from {0}, {1}, {14} " +
"where {0}.{3} = {4} and " +
"{0}.{5} = {1}.{6} and " +
"{0}.{3} = {1}.{7} and " +
"{15} = {16} and " + //user_id = assessor_id
"{1}.{8} = " +
"(select max(s1.{8}) from {1} as s1 where s1.{8} <= {9}) and " +
"{0}.{10} = '{11}' and " +
"{0}.{12} = {9} ",
FieldName.TABLE_NAME, Sub_indicator.FieldName.TABLE_NAME,
Sub_indicator.FieldName.SUB_INDICATOR_NAME, FieldName.INDICATOR_NUM,
indicator_num, FieldName.SUB_INDICATOR_NUM,
Sub_indicator.FieldName.SUB_INDICATOR_NUM,
Sub_indicator.FieldName.INDICATOR_NUM,
Sub_indicator.FieldName.ACA_YEAR, aca_year,
FieldName.CURRI_ID, curri_id, FieldName.ACA_YEAR, temp5tablename,
User_list.FieldName.TABLE_NAME, User_list.FieldName.USER_ID, FieldName.ASSESSOR_ID,
Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME);
string insertintotemp5_2 = string.Format("insert into {12} " +
"select {1},0,{2},{3}," +
"'','0','','',null,null,'','{4}',{6},null,null " +
"from {0} where " +
"{2} = {13} and {5} = " +
"(select max(s1.{5}) from {0} as s1 where s1.{5} <= {6}) " +
"and not exists(select * from {7} " +
"where {8} = '{4}' and {9} = {6} and " +
"{0}.{2} = {7}.{10} " +
"and {0}.{3} = {7}.{11}) ",
Sub_indicator.FieldName.TABLE_NAME, Sub_indicator.FieldName.SUB_INDICATOR_NAME,
Sub_indicator.FieldName.INDICATOR_NUM, Sub_indicator.FieldName.SUB_INDICATOR_NUM,
curri_id, Sub_indicator.FieldName.ACA_YEAR, aca_year, FieldName.TABLE_NAME,
FieldName.CURRI_ID, FieldName.ACA_YEAR, FieldName.INDICATOR_NUM,
FieldName.SUB_INDICATOR_NUM, temp5tablename, indicator_num);
string insertintotemp5_3truecase = string.Format("insert into {0} " +
"select {1}," +
"{2}.*," +
"{3}, {4} from {2}, {5}, {6} " +
"where {2}.{7} = {8} " +
"and {2}.{7} = {5}.{9} " +
"and {2}.{10} = 0 " +
"and {11} = {12} " +
"and {5}.{13} = " +
"(select max(s1.{13}) from {5} as s1 where s1.{13} <= {14}) " +
"and {2}.{15} = '{16}' and {2}.{17} = {14} ",
temp5tablename, Indicator.FieldName.INDICATOR_NAME_E, /*2 others*/ FieldName.TABLE_NAME,
Teacher.FieldName.T_PRENAME, Teacher.FieldName.T_NAME,
/*5 ind*/ Indicator.FieldName.TABLE_NAME,
/*6 usr*/ User_list.FieldName.TABLE_NAME,
FieldName.INDICATOR_NUM, indicator_num, Indicator.FieldName.INDICATOR_NUM,
FieldName.SUB_INDICATOR_NUM, User_list.FieldName.USER_ID,
FieldName.ASSESSOR_ID, Indicator.FieldName.ACA_YEAR, aca_year,
FieldName.CURRI_ID, curri_id, FieldName.ACA_YEAR
);
string insertintotemp5_3falsecase = string.Format("insert into {0} select " +
"{1}," +
"0,{2},0,'','0','','',null,null,'','{3}',{4},null,null " +
"from {5} " +
"where {6} = {2} " +
"and {5}.{7} = (select max({7}) from {5} where {7} <= {4}) ",
temp5tablename, Indicator.FieldName.INDICATOR_NAME_E, indicator_num, curri_id, aca_year,
Indicator.FieldName.TABLE_NAME, Indicator.FieldName.INDICATOR_NUM,
Indicator.FieldName.ACA_YEAR);
string insertintotemp5_3 = string.Format("if exists (select * from {0} where {1} = 0 and {2} = {3} and {4} = '{5}' and {6} = {7}) " +
" BEGIN " + insertintotemp5_3truecase + " END " +
" else " +
" BEGIN " + insertintotemp5_3falsecase + " END ",
FieldName.TABLE_NAME, FieldName.SUB_INDICATOR_NUM, FieldName.INDICATOR_NUM, indicator_num,
FieldName.CURRI_ID, curri_id, FieldName.ACA_YEAR, aca_year);
string selectcmd = string.Format("select * from {0} order by {1} ", temp5tablename, FieldName.SUB_INDICATOR_NUM);
string selectselfscorecmd = string.Format("select {0},{1} " +
"from {2} " +
"where {3} = '{4}' and {5} = {6} and {7} = {8} ",
Self_evaluation.FieldName.SUB_INDICATOR_NUM, Self_evaluation.FieldName.EVALUATION_SCORE,
Self_evaluation.FieldName.TABLE_NAME, Self_evaluation.FieldName.CURRI_ID, curri_id,
Self_evaluation.FieldName.ACA_YEAR, aca_year, Self_evaluation.FieldName.INDICATOR_NUM, indicator_num);
d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} {4} {5} END", createtabletemp5, insertintotemp5_1,
insertintotemp5_2, insertintotemp5_3, selectcmd, selectselfscorecmd);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
do
{
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
if (data.Columns.Count > 2)
{
foreach (DataRow item in data.Rows)
{
if (Convert.ToInt32(item.ItemArray[data.Columns[FieldName.OTHERS_EVALUATION_ID].Ordinal]) != 0)
{
string h, m;
DateTime timeofday = Convert.ToDateTime(item.ItemArray[data.Columns[FieldName.TIME].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture);
h = timeofday.Hour.ToString();
m = timeofday.Minute.ToString();
result.evaluation_detail.Add(new Others_evaluation_sub_indicator_name
{
curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
others_evaluation_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.OTHERS_EVALUATION_ID].Ordinal]),
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
assessor_id = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ASSESSOR_ID].Ordinal]),
t_name = NameManager.GatherPreName(item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString()) +
item.ItemArray[data.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString(),
date = Convert.ToDateTime(item.ItemArray[data.Columns[FieldName.DATE].Ordinal].ToString(), System.Globalization.CultureInfo.CurrentCulture).GetDateTimeFormats()[3],
time = (timeofday.Hour > 9 ? "" : "0") + h + '.' + (timeofday.Minute > 9 ? "" : "0") + m,
strength = item.ItemArray[data.Columns[FieldName.STRENGTH].Ordinal].ToString(),
improve = item.ItemArray[data.Columns[FieldName.IMPROVE].Ordinal].ToString(),
evaluation_score = item.ItemArray[data.Columns[FieldName.EVALUATION_SCORE].Ordinal].ToString() != "" ? Convert.ToInt32(item.ItemArray[data.Columns[FieldName.EVALUATION_SCORE].Ordinal]) : 0,
indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.INDICATOR_NUM].Ordinal]),
sub_indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.SUB_INDICATOR_NUM].Ordinal]),
sub_indicator_name = item.ItemArray[data.Columns[Sub_indicator.FieldName.SUB_INDICATOR_NAME].Ordinal].ToString()
});
if (item.ItemArray[data.Columns[Evidence.FieldName.FILE_NAME].Ordinal].ToString() != "")
{
result.file_name = item.ItemArray[data.Columns[Evidence.FieldName.FILE_NAME].Ordinal].ToString();
}
}
else
{
result.evaluation_detail.Add(new Others_evaluation_sub_indicator_name
{
curri_id = this.curri_id,
others_evaluation_id = 0,
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
assessor_id = 31,
date = "",
time = "",
strength = "",
improve = "",
evaluation_score = 0,
indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.INDICATOR_NUM].Ordinal]),
sub_indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.SUB_INDICATOR_NUM].Ordinal]),
sub_indicator_name = item.ItemArray[data.Columns[Sub_indicator.FieldName.SUB_INDICATOR_NAME].Ordinal].ToString()
});
}
}
}
else
{
foreach (DataRow item in data.Rows)
{
sub_indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[Self_evaluation.FieldName.SUB_INDICATOR_NUM].Ordinal]);
result.evaluation_detail.First(t => t.sub_indicator_num == sub_indicator_num).self_score =
Convert.ToInt32(item.ItemArray[data.Columns[Self_evaluation.FieldName.EVALUATION_SCORE].Ordinal]);
}
}
data.Dispose();
}
else if (!res.IsClosed)
{
if (!res.NextResult())
{
break;
}
}
} while (!res.IsClosed);
res.Close();
}
catch (Exception ex)
{
//Handle error from sql execution
return(ex.Message);
}
finally
{
//Whether it success or not it must close connection in order to end block
d.SQLDisconnect();
}
return(result);
}
public async Task <object> SelectByTitle()
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
{
return(WebApiApplication.CONNECTDBERRSTRING);
}
Default_privilege_by_type_with_privilege_choices result = new Default_privilege_by_type_with_privilege_choices();
string selectTitle = string.Format("select * from {0} where {1} = {2} ", Title.FieldName.TABLE_NAME,
Title.FieldName.TITLE_CODE, title_code);
string selectPrivilegeChoices = string.Format("select * from {0} where {1} = {2} ", Title_privilege.FieldName.TABLE_NAME,
Title_privilege.FieldName.TITLE_CODE, title_code);
string selectMainData = string.Format("select {0}.*,{1} " +
"from {0},{2} " +
"where {3} = {4} " +
"and {0}.{5} = {2}.{6} ",
FieldName.TABLE_NAME, User_type.FieldName.USER_TYPE_NAME,
User_type.FieldName.TABLE_NAME,
FieldName.TITLE_CODE, title_code, FieldName.USER_TYPE_ID, User_type.FieldName.USER_TYPE_ID);
string selectSetDefaultData = string.Format("select {0}, {1},1 as {2},{3} " +
"from {4},{5} " +
"where {1} = {6} and {0} != 7 " +
"and not exists(select * from {7} " +
"where {7}.{8} = {5}.{1} " +
"and {7}.{9} = {4}.{0}) ",
User_type.FieldName.USER_TYPE_ID, Title.FieldName.TITLE_CODE, Title_privilege.FieldName.TITLE_PRIVILEGE_CODE,
User_type.FieldName.USER_TYPE_NAME, User_type.FieldName.TABLE_NAME,
Title.FieldName.TABLE_NAME, title_code, FieldName.TABLE_NAME, FieldName.TITLE_CODE,
FieldName.USER_TYPE_ID);
d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} END", selectTitle, selectPrivilegeChoices,
selectMainData, selectSetDefaultData);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
do
{
if (res.HasRows)
{
DataTable tabledata = new DataTable();
tabledata.Load(res);
foreach (DataRow item in tabledata.Rows)
{
if (tabledata.Columns.Contains(Title.FieldName.NAME))
{
//Set title name from title table result
result.title_code = Convert.ToInt32(item.ItemArray[tabledata.Columns[Title.FieldName.TITLE_CODE].Ordinal]);
result.name = item.ItemArray[tabledata.Columns[Title.FieldName.NAME].Ordinal].ToString();
}
else if (tabledata.Columns.Contains(Title_privilege.FieldName.PRIVILEGE))
{
//Set privilege choice for target title
result.choices.Add(new Privilege_choice
{
title_privilege_code = Convert.ToInt32(item.ItemArray[tabledata.Columns[Title_privilege.FieldName.TITLE_PRIVILEGE_CODE].Ordinal]),
privilege = item.ItemArray[tabledata.Columns[Title_privilege.FieldName.PRIVILEGE].Ordinal].ToString()
});
}
else
{
//Read main privilege data
int title_priv_code = Convert.ToInt32(item.ItemArray[tabledata.Columns[FieldName.TITLE_PRIVILEGE_CODE].Ordinal]);
result.privilege_list.Add(new User_type_privilege
{
user_type_id = Convert.ToInt32(item.ItemArray[tabledata.Columns[FieldName.USER_TYPE_ID].Ordinal]),
user_type = item.ItemArray[tabledata.Columns[User_type.FieldName.USER_TYPE_NAME].Ordinal].ToString(),
privilege = new Privilege_choice {
title_privilege_code = title_priv_code,
//Find privilege caption from choices array
privilege = result.choices.First(t => t.title_privilege_code == title_priv_code).privilege
}
});
}
}
tabledata.Dispose();
}
else if (!res.IsClosed)
{
if (!res.NextResult())
{
break;
}
}
} while (!res.IsClosed);
res.Close();
}
catch (Exception ex)
{
//Handle error from sql execution
return(ex.Message);
}
finally
{
//Whether it success or not it must close connection in order to end block
d.SQLDisconnect();
}
return(result);
}
public void SerializeLargeDataset(HttpContext context)
{
string strSQL = @"
SELECT TOP 10 * FROM T_Benutzer;
SELECT TOP 10 * FROM T_Benutzergruppen;
-- SELECT * FROM T_Benutzer LIMIT 10;
-- SELECT * FROM T_Benutzergruppen LIMIT 10;
-- SELECT * FROM T_Benutzer OFFSET 0 FETCH NEXT 10 ROWS ONLY;
-- SELECT * FROM T_Benutzergruppen OFFSET 0 FETCH NEXT 10 ROWS ONLY;
";
Newtonsoft.Json.JsonSerializer ser = new Newtonsoft.Json.JsonSerializer();
using (Newtonsoft.Json.JsonTextWriter jsonWriter = new Newtonsoft.Json.JsonTextWriter(context.Response.Output))
{
jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented;
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("Tables");
jsonWriter.WriteStartArray();
using (System.Data.Common.DbConnection con = SQL.CreateConnection())
{
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
using (System.Data.Common.DbCommand cmd = con.CreateCommand())
{
cmd.CommandText = strSQL;
using (System.Data.Common.DbDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess
| System.Data.CommandBehavior.CloseConnection
))
{
do
{
jsonWriter.WriteStartObject(); // tbl = new Table();
jsonWriter.WritePropertyName("Columns");
jsonWriter.WriteStartArray();
for (int i = 0; i < dr.FieldCount; ++i)
{
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("ColumnName");
jsonWriter.WriteValue(dr.GetName(i));
jsonWriter.WritePropertyName("FieldType");
jsonWriter.WriteValue(GetAssemblyQualifiedNoVersionName(dr.GetFieldType(i)));
jsonWriter.WriteEndObject();
} // Next i
jsonWriter.WriteEndArray();
jsonWriter.WritePropertyName("Rows");
jsonWriter.WriteStartArray();
if (dr.HasRows)
{
while (dr.Read())
{
object[] thisRow = new object[dr.FieldCount];
jsonWriter.WriteStartArray(); // object[] thisRow = new object[dr.FieldCount];
for (int i = 0; i < dr.FieldCount; ++i)
{
jsonWriter.WriteValue(dr.GetValue(i));
} // Next i
jsonWriter.WriteEndArray(); // tbl.Rows.Add(thisRow);
} // Whend
} // End if (dr.HasRows)
jsonWriter.WriteEndArray();
jsonWriter.WriteEndObject(); // ser.Tables.Add(tbl);
} while (dr.NextResult());
} // End using dr
} // End using cmd
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
} // End using con
jsonWriter.WriteEndArray();
jsonWriter.WriteEndObject();
jsonWriter.Flush();
} // End Using jsonWriter
context.Response.Output.Flush();
context.Response.OutputStream.Flush();
context.Response.Flush();
} // End Sub SerializeLargeDataset
public async Task <object> SelectByCurriculumAcademic()
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
{
return(WebApiApplication.CONNECTDBERRSTRING);
}
List <Lab_list_detail> result = new List <Lab_list_detail>();
d.iCommand.CommandText = getSelectByCurriculumAcademicCommand();
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
do
{
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
if (data.Columns.Contains(FieldName.ROOM))
{
result.Add(new Lab_list_detail
{
lab_num = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.LAB_NUM].Ordinal]),
aca_year = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.ACA_YEAR].Ordinal]),
curri_id = item.ItemArray[data.Columns[FieldName.CURRI_ID].Ordinal].ToString(),
name = item.ItemArray[data.Columns[FieldName.NAME].Ordinal].ToString(),
room = item.ItemArray[data.Columns[FieldName.ROOM].Ordinal].ToString()
});
}
else
{
int labid = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.LAB_NUM].Ordinal]);
result.First(t => t.lab_num == labid).officer.Add(new Personnel_with_t_name
{
user_id = Convert.ToInt32(item.ItemArray[data.Columns[Lab_officer.FieldName.OFFICER].Ordinal]),
t_name = item.ItemArray[data.Columns[User_list.FieldName.T_PRENAME].Ordinal].ToString() + item.ItemArray[data.Columns[User_list.FieldName.T_NAME].Ordinal].ToString()
});
}
}
data.Dispose();
}
else if (!res.IsClosed)
{
if (!res.NextResult())
{
break;
}
}
} while (!res.IsClosed);
res.Close();
}
catch (Exception ex)
{
//Handle error from sql execution
return(ex.Message);
}
finally
{
//Whether it success or not it must close connection in order to end block
d.SQLDisconnect();
}
return(result);
}
public async Task <object> getSectionSaveDataForSAR()
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
{
return(WebApiApplication.CONNECTDBERRSTRING);
}
SAR result = new SAR();
string selectindicator = string.Format("select {0}, {1}, {2} " +
"from {3} " +
"where {4} = (select max({4}) from {3} where {4} <= {5}) order by {0} ",
Indicator.FieldName.INDICATOR_NUM, Indicator.FieldName.INDICATOR_NAME_T, Indicator.FieldName.INDICATOR_NAME_E,
Indicator.FieldName.TABLE_NAME,
Indicator.FieldName.ACA_YEAR, aca_year);
string selectsubindicator = string.Format("select {0},{1},{2} " +
"from {3} " +
"where {4} = (select max({4}) from {3} where {4} <= {5}) order by {0},{1} ",
Sub_indicator.FieldName.INDICATOR_NUM, Sub_indicator.FieldName.SUB_INDICATOR_NUM, Sub_indicator.FieldName.SUB_INDICATOR_NAME,
Sub_indicator.FieldName.TABLE_NAME, Sub_indicator.FieldName.ACA_YEAR, aca_year);
string selectsectionsave = string.Format("select {0},{1},{2},{3},{4},{5} " +
"from {6} " +
"where {7} = '{8}' and {9} = {10} order by {0},{1} ",
FieldName.INDICATOR_NUM, FieldName.SUB_INDICATOR_NUM, FieldName.DETAIL, FieldName.STRENGTH, FieldName.IMPROVE, FieldName.WEAKNESS,
FieldName.TABLE_NAME, FieldName.CURRI_ID, curri_id,
FieldName.ACA_YEAR, aca_year);
string selectevidence = string.Format("select {0},{1},{2} " +
"from {3} " +
"where {4} = '{5}' and {6} = {7} order by {0},{1} ",
Evidence.FieldName.INDICATOR_NUM, Evidence.FieldName.EVIDENCE_REAL_CODE, Evidence.FieldName.EVIDENCE_NAME,
Evidence.FieldName.TABLE_NAME, Evidence.FieldName.CURRI_ID, curri_id,
Evidence.FieldName.ACA_YEAR, aca_year);
string selectselfevaluation = string.Format("select {0},{1},{2} " +
"from {3} " +
"where {4} = '{5}' and {6} = {7} order by {0},{1} ",
Self_evaluation.FieldName.INDICATOR_NUM, Self_evaluation.FieldName.SUB_INDICATOR_NUM,
Self_evaluation.FieldName.EVALUATION_SCORE, Self_evaluation.FieldName.TABLE_NAME,
Self_evaluation.FieldName.CURRI_ID, curri_id, Self_evaluation.FieldName.ACA_YEAR, aca_year);
d.iCommand.CommandText = string.Format("BEGIN {0} {1} {2} {3} {4} END", selectindicator, selectsubindicator, selectsectionsave, selectevidence, selectselfevaluation);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
do
{
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
//Case current resultset is indicator table
if (data.Columns.Contains("indicator_name_t"))
{
foreach (DataRow item in data.Rows)
{
string indicator_namet = item.ItemArray[data.Columns[Indicator.FieldName.INDICATOR_NAME_T].Ordinal].ToString();
//Use thai indicator name if it exists
if (indicator_namet != "")
{
result.indicator_section_save_list.Add(new Indicator_with_section_save_list
{
indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[Indicator.FieldName.INDICATOR_NUM].Ordinal]),
indicator_name = item.ItemArray[data.Columns[Indicator.FieldName.INDICATOR_NAME_T].Ordinal].ToString()
});
}
//Otherwise use engish normally
else
{
result.indicator_section_save_list.Add(new Indicator_with_section_save_list
{
indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[Indicator.FieldName.INDICATOR_NUM].Ordinal]),
indicator_name = item.ItemArray[data.Columns[Indicator.FieldName.INDICATOR_NAME_E].Ordinal].ToString()
});
}
result.indicator_self_evaluation_list.Add(new Indicator_with_self_evaluation_tiny_obj_list
{
indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[Indicator.FieldName.INDICATOR_NUM].Ordinal])
});
//Force to add self_evaluation with sub_indicator_num = 0 (overall result for each indicator)
result.indicator_self_evaluation_list.Last().self_evaluation_list.Add(new Self_evaluation_tiny_detail
{
sub_indicator_num = 0,
evaluation_score = 0 //Default score
});
}
}
//Case current resultset is sub_indicator table
else if (data.Columns.Contains("sub_indicator_name"))
{
foreach (DataRow item in data.Rows)
{
int indnum = Convert.ToInt32(item.ItemArray[data.Columns[Sub_indicator.FieldName.INDICATOR_NUM].Ordinal]);
result.indicator_section_save_list.First(t => t.indicator_num == indnum).section_save_list.Add(new Section_save_with_sub_indicator_detail
{
detail = "--ไม่พบข้อมูล--",
strength = "--ไม่พบข้อมูล--",
weakness = "--ไม่พบข้อมูล--",
improve = "--ไม่พบข้อมูล--",
sub_indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[Sub_indicator.FieldName.SUB_INDICATOR_NUM].Ordinal]),
indicator_num = indnum,
sub_indicator_name = item.ItemArray[data.Columns[Sub_indicator.FieldName.SUB_INDICATOR_NAME].Ordinal].ToString()
});
//Force to add self_evaluation with sub_indicator_num equal to => current read value
result.indicator_self_evaluation_list.First(t => t.indicator_num == indnum).self_evaluation_list.Add(new Self_evaluation_tiny_detail
{
sub_indicator_num = Convert.ToInt32(item.ItemArray[data.Columns[Sub_indicator.FieldName.SUB_INDICATOR_NUM].Ordinal]),
evaluation_score = 0 //Default score
});
}
}
//Case current resultset is section_save table
else if (data.Columns.Contains("detail"))
{
foreach (DataRow item in data.Rows)
{
int indnum = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.INDICATOR_NUM].Ordinal]);
int subindnum = Convert.ToInt32(item.ItemArray[data.Columns[FieldName.SUB_INDICATOR_NUM].Ordinal]);
Section_save_with_sub_indicator_detail target = result.indicator_section_save_list.First(t => t.indicator_num == indnum).
section_save_list.First(u => u.sub_indicator_num == subindnum);
string readdetail = item.ItemArray[data.Columns[FieldName.DETAIL].Ordinal].ToString();
if (readdetail != "")
{
target.detail = readdetail;
}
string readstrength = item.ItemArray[data.Columns[FieldName.STRENGTH].Ordinal].ToString();
if (readstrength != "")
{
target.strength = readstrength;
}
string readweak = item.ItemArray[data.Columns[FieldName.WEAKNESS].Ordinal].ToString();
if (readweak != "")
{
target.weakness = readweak;
}
string readimprove = item.ItemArray[data.Columns[FieldName.IMPROVE].Ordinal].ToString();
if (readimprove != "")
{
target.improve = readimprove;
}
}
}
//Case current resultset is evidence table
else if (data.Columns.Contains(Evidence.FieldName.EVIDENCE_NAME))
{
foreach (DataRow item in data.Rows)
{
int indnum = Convert.ToInt32(item.ItemArray[data.Columns[Evidence.FieldName.INDICATOR_NUM].Ordinal]);
result.indicator_section_save_list.First(t => t.indicator_num == indnum).evidence_list.Add(new Evidence_detail_for_SAR
{
indicator_num = indnum.ToString(),
evidence_real_code = item.ItemArray[data.Columns[Evidence.FieldName.EVIDENCE_REAL_CODE].Ordinal].ToString(),
evidence_name = item.ItemArray[data.Columns[Evidence.FieldName.EVIDENCE_NAME].Ordinal].ToString()
});
}
}
//Case current resultset is self_evaluation
else
{
foreach (DataRow item in data.Rows)
{
int indnum = Convert.ToInt32(item.ItemArray[data.Columns[Self_evaluation.FieldName.INDICATOR_NUM].Ordinal]);
int subindnum = Convert.ToInt32(item.ItemArray[data.Columns[Self_evaluation.FieldName.SUB_INDICATOR_NUM].Ordinal]);
result.indicator_self_evaluation_list.First(t => t.indicator_num == indnum).
self_evaluation_list.First(u => u.sub_indicator_num == subindnum).
evaluation_score = Convert.ToInt32(item.ItemArray[data.Columns[Self_evaluation.FieldName.EVALUATION_SCORE].Ordinal]);
}
}
data.Dispose();
}
else if (!res.IsClosed)
{
if (!res.NextResult())
{
break;
}
}
} while (!res.IsClosed);
res.Close();
}
catch (Exception ex)
{
//Handle error from sql execution
return(ex.Message);
}
finally
{
//Whether it success or not it must close connection in order to end block
d.SQLDisconnect();
}
return(result);
}
public static void Test()
{
System.Data.SqlClient.SqlConnectionStringBuilder csb = new System.Data.SqlClient.SqlConnectionStringBuilder();
csb.DataSource = System.Environment.MachineName;
csb.DataSource = @"10.1.1.8"; // Must be IP, NETBIOS doesn't resolve on Linux
// SQL Server Configuration Manager
// SQL Server 2016 C:\Windows\SysWOW64\SQLServerManager13.msc
// SQL Server 2014 C:\Windows\SysWOW64\SQLServerManager12.msc
// SQL Server 2012 C:\Windows\SysWOW64\SQLServerManager11.msc
// SQL Server 2008 C:\Windows\SysWOW64\SQLServerManager10.msc
// in Network-Configuration: Activate TCP/IP & Restart Service
// Open firewall port for SQL-Server
// - Windows 10:
// netsh advfirewall firewall add rule name="SQL Server" dir=in action=allow protocol=TCP localport=1433
// - Windows < 10:
// netsh firewall set portopening TCP 1433 "SQLServer"
// https://support.microsoft.com/en-us/kb/968872
// https://blog.brankovucinec.com/2015/12/04/scripts-to-open-windows-firewall-ports-for-sql-server/
csb.IntegratedSecurity = false;
if (!csb.IntegratedSecurity)
{
csb.UserID = "LoggyWebServices";
csb.Password = "******";
}
csb.InitialCatalog = "TestDB";
Loggy.cDAL DAL = Loggy.cDAL.CreateInstance();
DAL.ConnectionString = csb.ConnectionString;
using (System.Data.Common.DbConnection dbConnection = DAL.GetConnection())
{
object objUser1 = DAL.ExecuteScalar("SELECT TOP 1 BE_User FROM T_Benutzer ORDER BY BE_User;", dbConnection);
object objUser2 = DAL.ExecuteScalar("SELECT TOP 1 BE_User FROM T_Benutzer ORDER BY BE_ID;", dbConnection);
DAL.ExecuteNonQuery("UPDATE T_Benutzer SET BE_Hash = BE_Hash;", dbConnection);
DAL.ExecuteNonQuery("UPDATE T_Benutzer SET BE_Hash = BE_Hash;", dbConnection);
#if WITH_CONNECTION
//using (System.Data.Common.DbDataReader reader = DAL.ExecuteReader("SELECT * FROM T_Benutzer; SELECT * FROM T_Benutzergruppen;", dbConnection))
using (System.Data.Common.DbDataReader reader = DAL.ExecuteReader_Buggy("SELECT * FROM T_Benutzer; SELECT * FROM T_Benutzergruppen;"))
#else
DAL.ExecuteReader("SELECT * FROM T_Benutzer; SELECT * FROM T_Benutzergruppen;", delegate(System.Data.Common.DbDataReader reader)
#endif
{
do
{
for (int i = 0; i < reader.FieldCount; ++i)
{
string fieldName = reader.GetName(i);
System.Type fieldType = reader.GetFieldType(i);
System.Console.WriteLine("{0}:\t{1}\t{2}", i, fieldName, fieldType.ToString());
} // Next i
if (reader.HasRows)
{
int rowCount = 1;
while (reader.Read())
{
System.Console.WriteLine(@"Row {0}", rowCount);
for (int i = 0; i < reader.FieldCount; ++i)
{
string fieldName = reader.GetName(i);
object fieldValue = reader.GetValue(i);
System.Console.WriteLine(@" - {0}: {1}", fieldName, System.Convert.ToString(fieldValue));
} // Next i
++rowCount;
} // Whend
--rowCount;
} // End if (reader.HasRows)
} while (reader.NextResult());
} // End Using reader
#if !WITH_CONNECTION
);
#endif
object objUser3 = DAL.ExecuteScalar("SELECT TOP 1 BE_User FROM T_Benutzer ORDER BY BE_Hash;", dbConnection);
object objUser4 = DAL.ExecuteScalar("SELECT TOP 1 BE_User FROM T_Benutzer ORDER BY BE_Passwort;", dbConnection);
} // End Using dbConnection
} // End Sub Test
} // End Sub WriteArray
public static void AnyDataReaderToAnyJson(
string sql
, SqlService service
, System.Collections.Generic.Dictionary <string, object> pars
, System.Web.HttpContext context
, RenderType_t format)
{
using (System.Data.Common.DbConnection con = service.Connection)
{
using (System.Data.Common.DbCommand cmd = con.CreateCommand())
{
cmd.CommandText = sql;
service.AddParameterList(pars, cmd);
// cmd.ExecuteNonQuery
// cmd.ExecuteReader
// cmd.ExecuteScalar
using (System.Data.Common.DbDataReader dr = cmd.ExecuteReader(
System.Data.CommandBehavior.SequentialAccess
| System.Data.CommandBehavior.CloseConnection))
{
using (System.IO.StreamWriter output = new System.IO.StreamWriter(context.Response.OutputStream))
{
using (Newtonsoft.Json.JsonTextWriter jsonWriter =
new Newtonsoft.Json.JsonTextWriter(output)) // context.Response.Output)
{
if (format.HasFlag(RenderType_t.Indented))
{
jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented;
}
context.Response.StatusCode = (int)System.Net.HttpStatusCode.OK;
context.Response.ContentType = "application/json";
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("tables");
jsonWriter.WriteStartArray();
do
{
if (!format.HasFlag(RenderType_t.Data_Only) &&
!format.HasFlag(RenderType_t.DataTable))
{
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("columns");
if (format.HasFlag(RenderType_t.Columns_Associative))
{
WriteAssociativeColumnsArray(jsonWriter, dr, format);
}
else if (format.HasFlag(RenderType_t.Columns_ObjectArray))
{
WriteComplexArray(jsonWriter, dr, format);
}
else // (format.HasFlag(RenderType_t.Array))
{
WriteArray(jsonWriter, dr);
}
} // End if (!format.HasFlag(RenderType_t.Data_Only))
if (!format.HasFlag(RenderType_t.Data_Only) &&
!format.HasFlag(RenderType_t.DataTable))
{
jsonWriter.WritePropertyName("rows");
} // End if (!format.HasFlag(RenderType_t.Data_Only))
jsonWriter.WriteStartArray();
if (dr.HasRows)
{
string[] columns = null;
if (format.HasFlag(RenderType_t.DataTable))
{
columns = new string[dr.FieldCount];
for (int i = 0; i < dr.FieldCount; i++)
{
columns[i] = dr.GetName(i);
} // Next i
} // End if (format.HasFlag(RenderType_t.DataTable))
while (dr.Read())
{
if (format.HasFlag(RenderType_t.DataTable))
{
jsonWriter.WriteStartObject();
}
else
{
jsonWriter.WriteStartArray();
}
for (int i = 0; i <= dr.FieldCount - 1; i++)
{
object obj = dr.GetValue(i);
if (obj == System.DBNull.Value)
{
obj = null;
}
if (columns != null && format.HasFlag(RenderType_t.DataTable))
{
jsonWriter.WritePropertyName(columns[i]);
}
jsonWriter.WriteValue(obj);
} // Next i
if (format.HasFlag(RenderType_t.DataTable))
{
jsonWriter.WriteEndObject();
}
else
{
jsonWriter.WriteEndArray();
}
} // Whend
} // End if (dr.HasRows)
jsonWriter.WriteEndArray();
if (!format.HasFlag(RenderType_t.Data_Only) &&
!format.HasFlag(RenderType_t.DataTable))
{
jsonWriter.WriteEndObject();
} // End if (!format.HasFlag(RenderType_t.Data_Only))
} while (dr.NextResult());
jsonWriter.WriteEndArray();
jsonWriter.WriteEndObject();
jsonWriter.Flush();
output.Flush();
} // jsonWriter
} // output
} // dr
} // End Using cmd
if (con.State != System.Data.ConnectionState.Closed)
{
con.Close();
}
} // con
} // End Sub WriteArray
public async Task <object> selectWithFullDetail(string curri_id_data)
{
DBConnector d = new DBConnector();
if (!d.SQLConnect())
{
return(WebApiApplication.CONNECTDBERRSTRING);
}
List <Personnel_educational> result = new List <Personnel_educational>();
string selpersonnel = string.Format("select {0}, {1}.{2}," +
"{3} = {4} " +
", {5}, {6}, {7}, {8}, {9} " +
"from {1}, {10} " +
"where {1}.{2} in(1,2) " +
"and exists (select * from {11} " +
"where {1}.{0} = {12} and {13} = '{14}') " +
"and {1}.{2} = {10}.{15} ",
User_list.FieldName.USER_ID, User_list.FieldName.TABLE_NAME,
User_list.FieldName.USER_TYPE_ID, User_list.FieldName.T_PRENAME,
NameManager.GatherSQLCASEForPrename(User_list.FieldName.TABLE_NAME, User_list.FieldName.USER_TYPE_ID, User_list.FieldName.T_PRENAME),
User_list.FieldName.T_NAME, User_list.FieldName.FILE_NAME_PIC,
User_list.FieldName.EMAIL, User_list.FieldName.TEL,
User_type.FieldName.USER_TYPE_NAME, User_type.FieldName.TABLE_NAME,
User_curriculum.FieldName.TABLE_NAME, User_curriculum.FieldName.USER_ID,
User_curriculum.FieldName.CURRI_ID, curri_id_data,
User_type.FieldName.USER_TYPE_ID
);
string seleducation = string.Format("select * from {0} " +
"where " +
"exists (select * from {1} " +
"where {2} = {3} and {4} = '{5}') " +
"and exists (select * from {6} " +
"where {2} = {7} and {8} in(1, 2)) ",
Educational_teacher_staff.FieldName.TABLE_NAME,
User_curriculum.FieldName.TABLE_NAME,
Educational_teacher_staff.FieldName.PERSONNEL_ID,
User_curriculum.FieldName.USER_ID,
User_curriculum.FieldName.CURRI_ID, curri_id_data,
User_list.FieldName.TABLE_NAME,
User_list.FieldName.USER_ID, User_list.FieldName.USER_TYPE_ID
);
d.iCommand.CommandText = string.Format("BEGIN {0} {1} END", selpersonnel, seleducation);
try
{
System.Data.Common.DbDataReader res = await d.iCommand.ExecuteReaderAsync();
do
{
if (res.HasRows)
{
DataTable data = new DataTable();
data.Load(res);
foreach (DataRow item in data.Rows)
{
if (data.Columns.Contains(Educational_teacher_staff.FieldName.EDUCATION_ID))
{//Educational table data
int personnelid = Convert.ToInt32(item.ItemArray[data.Columns[Educational_teacher_staff.FieldName.PERSONNEL_ID].Ordinal]);
result.First(p => p.user_id == personnelid).history.Add(new Educational_teacher_staff
{
college = item.ItemArray[data.Columns[Educational_teacher_staff.FieldName.COLLEGE].Ordinal].ToString(),
degree = Convert.ToChar(item.ItemArray[data.Columns[Educational_teacher_staff.FieldName.DEGREE].Ordinal]),
grad_year = Convert.ToInt32(item.ItemArray[data.Columns[Educational_teacher_staff.FieldName.GRAD_YEAR].Ordinal]),
pre_major = item.ItemArray[data.Columns[Educational_teacher_staff.FieldName.PRE_MAJOR].Ordinal].ToString(),
major = item.ItemArray[data.Columns[Educational_teacher_staff.FieldName.MAJOR].Ordinal].ToString(),
personnel_id = personnelid
});
}
else
{
result.Add(new Personnel_educational
{ //Main user table data
user_id = Convert.ToInt32(item.ItemArray[data.Columns[User_list.FieldName.USER_ID].Ordinal]),
email = item.ItemArray[data.Columns[User_list.FieldName.EMAIL].Ordinal].ToString(),
file_name_pic = MiscUtils.GatherProfilePicturePath(item.ItemArray[data.Columns[User_list.FieldName.FILE_NAME_PIC].Ordinal].ToString()),
tel = item.ItemArray[data.Columns[User_list.FieldName.TEL].Ordinal].ToString(),
user_type = item.ItemArray[data.Columns[User_type.FieldName.USER_TYPE_NAME].Ordinal].ToString(),
fullname = item.ItemArray[data.Columns[Teacher.FieldName.T_PRENAME].Ordinal].ToString() +
item.ItemArray[data.Columns[Teacher.FieldName.T_NAME].Ordinal].ToString()
});
}
}
data.Dispose();
}
else if (!res.IsClosed)
{
if (!res.NextResult())
{
break;
}
}
} while (!res.IsClosed);
res.Close();
}
catch (Exception ex)
{
//Handle error from sql execution
return(ex.Message);
}
finally
{
//Whether it success or not it must close connection in order to end block
d.SQLDisconnect();
}
return(result);
}
} // End Sub MultipleLargeDataSets
public static void MultipleLargeDataSets(System.IO.Stream strm, string strSQL)
{
Newtonsoft.Json.JsonSerializer serializer = new Newtonsoft.Json.JsonSerializer();
using (System.IO.StreamWriter output = new System.IO.StreamWriter(strm))
{
using (Newtonsoft.Json.JsonTextWriter jsonWriter = new Newtonsoft.Json.JsonTextWriter(output))
{
jsonWriter.Formatting = Newtonsoft.Json.Formatting.Indented;
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("Tables");
jsonWriter.WriteStartArray();
using (System.Data.Common.DbDataReader dr = SQL.ExecuteReader(strSQL
, System.Data.CommandBehavior.CloseConnection
| System.Data.CommandBehavior.SequentialAccess
))
{
do
{
jsonWriter.WriteStartObject(); // tbl = new Table();
jsonWriter.WritePropertyName("Columns");
jsonWriter.WriteStartArray();
for (int i = 0; i < dr.FieldCount; ++i)
{
jsonWriter.WriteStartObject();
jsonWriter.WritePropertyName("ColumnName");
jsonWriter.WriteValue(dr.GetName(i));
jsonWriter.WritePropertyName("FieldType");
jsonWriter.WriteValue(dr.GetFieldType(i).AssemblyQualifiedName);
jsonWriter.WriteEndObject();
} // Next i
jsonWriter.WriteEndArray();
jsonWriter.WritePropertyName("Rows");
jsonWriter.WriteStartArray();
if (dr.HasRows)
{
while (dr.Read())
{
object[] thisRow = new object[dr.FieldCount];
jsonWriter.WriteStartArray(); // object[] thisRow = new object[dr.FieldCount];
for (int i = 0; i < dr.FieldCount; ++i)
{
jsonWriter.WriteValue(dr.GetValue(i));
} // Next i
jsonWriter.WriteEndArray(); // tbl.Rows.Add(thisRow);
} // Whend
} // End if (dr.HasRows)
jsonWriter.WriteEndArray();
jsonWriter.WriteEndObject(); // ser.Tables.Add(tbl);
} while (dr.NextResult());
} // End Using dr
jsonWriter.WriteEndArray();
jsonWriter.WriteEndObject();
jsonWriter.Flush();
output.Flush();
output.BaseStream.Flush();
output.Close();
// context.Response.Output.Flush();
// context.Reponse.OutputStream.Flush();
// context.Response.Flush();
} // End Using jsonWriter
} // End using output
} // End Sub MultipleLargeDataSets