public void ACCESS2CSV_ViaOdbc()
{
ControlFlow.CurrentDbConnection = new AccessOdbcConnectionManager(new OdbcConnectionString(AccessConnectionStringParameter))
{
AlwaysUseSameConnection = false
};
var sqlConnMan = new SqlConnectionManager(new ConnectionString(ConnectionStringParameter));
TableDefinition testTable = RecreateTestTable();
SqlTask.ExecuteNonQuery("Insert test data", "INSERT INTO TestTable (Field1, Field2) values (1,'Test1');");
SqlTask.ExecuteNonQuery("Insert test data", "INSERT INTO TestTable (Field1, Field2) values (2,'Test2');");
SqlTask.ExecuteNonQuery("Insert test data", "INSERT INTO TestTable (Field1, Field2) values (3,'Test3');");
new SqlTask("Create Target Table", $@"CREATE TABLE dbo.TargetTable (
Field1 decimal not null, Field2 nvarchar(1000) not null)")
{
ConnectionManager = sqlConnMan
}
.ExecuteNonQuery();
DBSource <Data> source = new DBSource <Data>(testTable);
DBDestination <Data> dest = new DBDestination <Data>("dbo.TargetTable", 1)
{
ConnectionManager = sqlConnMan
};
source.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(3, (new RowCountTask("dbo.TargetTable")
{
ConnectionManager = sqlConnMan
}).Count().Rows);
}
void paytab()
{
int month = DateTime.Now.Month;
int year = DateTime.Now.Year;
var connection = SqlConnectionManager.GetConnection();
string query = "SELECT [BasicPayPD],[EAccno] FROM [dbo].[tblPayroll] WHERE [EId]='" + txtEid.Text + "' AND [Month]='" + month + "' AND [Year]='" + year + "'";
var cmd = SqlConnectionManager.GetCommand(query, connection);
cmd.Connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
btnUpdate.Text = "Update";
while (reader.Read())
{
txtAn.Text = reader["EAccno"].ToString();
txtBp.Text = reader["BasicPayPD"].ToString();
}
}
else
{
btnUpdate.Text = "Add";
// btnClear_Click(new object(), new EventArgs());
}
}
}
public List <Appointment> GetAppointmentsByComplaint(int cid)
{
using (var connection = SqlConnectionManager.GetConnection())
using (var command = new NpgsqlCommand()) {
var appointments = new List <Appointment>();
command.Connection = connection;
command.CommandText = "select * from getAppointmentsByComplaint(@cid)";
command.Parameters.AddWithValue("@cid", cid);
var reader = command.ExecuteReader();
var dt = new DateTime(1970, 01, 01);
while (reader.Read())
{
var a = new Appointment()
{
Id = int.Parse(reader["id"].ToString()),
Complaint = new Complaint()
{
Id = int.Parse(reader["complaint_id"].ToString())
},
Date = DateTime.Parse(reader["date"].ToString()),
Status = bool.Parse(reader["status"].ToString())
};
var ft = TimeSpan.Parse(reader["from_time"].ToString());
var tt = TimeSpan.Parse(reader["to_time"].ToString());
a.FromTime = dt + ft;
a.ToTime = dt + tt;
appointments.Add(a);
}
return(appointments);
}
}
public int CreateClassroom(Classroom_lkpInfo info)
{
if (info == null)
{
throw new Exception(ExceptionMessage.InputParameterNull);
}
using (SqlConnectionManager cm = new SqlConnectionManager())
{
Nullable<int> classroom_id = null;
using(var cmd = new Classroom_lkp_Save_p())
{
cmd.Parameters.IsInsert = true;
cmd.Parameters.Classroom_id = info.Classroom_id;
cmd.Parameters.School_id = info.School_id;
cmd.Parameters.IsDeleted = info.IsDeleted;
cmd.Parameters.ClassroomName = info.ClassroomName;
cmd.Parameters.Classroom_Blurb_id = info.Classroom_Blurb_id;
cmd.Parameters.DefaultPhysicalCapacity = info.DefaultPhysicalCapacity;
cmd.Parameters.DisplayOrder = info.DisplayOrder;
cmd.Parameters.Insertby = info.Insertby;
cmd.Parameters.Updateby = info.Updateby;
cmd.Parameters.IsHidden = info.IsHidden;
cm.ExecuteNonQuery(cmd, _context);
classroom_id = cmd.Parameters.Classroom_id;
if (!classroom_id.HasValue)
{
throw new Exception(ExceptionMessage.NoIdReturnedForCreatedObject);
}
}
return classroom_id.Value;
}
}
void emptab()
{
var connection = SqlConnectionManager.GetConnection();
string query = "SELECT * FROM [dbo].[tblEmployee] WHERE [EId]='" + txtEid.Text + "'";
var cmd = SqlConnectionManager.GetCommand(query, connection);
cmd.Connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
btnUpdate.Text = "Update";
while (reader.Read())
{
txtName.Text = reader.GetString(1);
txtDepartment.Text = reader.GetString(2);
txtDesignation.Text = reader.GetString(3);
DateTime dt1 = Convert.ToDateTime(reader["EJoiningDate"].ToString());
txtJoindate.Text = String.Format("{0:yyyy-MM-dd}", dt1);
txtGender.Text = reader.GetString(6);
DateTime dt2 = Convert.ToDateTime(reader["EDob"].ToString());
txtDob.Text = String.Format("{0:yyyy-MM-dd}", dt2);
txtAddress.Text = reader.GetString(8);
txtPhoneno.Text = reader.GetString(9);
txtEmail.Text = reader.GetString(10);
}
}
else
{
btnUpdate.Text = "Add";
// btnClear_Click(new object(), new EventArgs());
}
}
}
public void TestLeaveConnectionOpenInParallel()
{
//Arrange
SqlConnectionManager con = new SqlConnectionManager(ConnectionStringParameter)
{
LeaveOpen = true,
MaxLoginAttempts = 1
};
//Act
Assert.Throws <InvalidOperationException>(() =>
{
try
{
Parallel.Invoke(
() => SqlTask.ExecuteNonQuery(con, "Dummy", "WAITFOR DELAY '0:00:01.000'")
, () => SqlTask.ExecuteNonQuery(con, "Dummy", "WAITFOR DELAY '0:00:01.000'")
);;
}
catch (AggregateException e)
{
throw e.InnerException;
}
});
con.Close();
SqlConnection.ClearAllPools();
}
public void TestLeaveConnectionOpen()
{
//Arrange
SqlConnectionManager con = new SqlConnectionManager(ConnectionStringParameter)
{
LeaveOpen = true
};
//Act
AssertOpenConnectionCount(0, ConnectionStringParameter);
Assert.True(con.State == null);
con.Open();
Assert.True(con.State == System.Data.ConnectionState.Open);
AssertOpenConnectionCount(1, ConnectionStringParameter);
con.Open();
AssertOpenConnectionCount(1, ConnectionStringParameter);
Assert.True(con.State == System.Data.ConnectionState.Open);
AssertOpenConnectionCount(1, ConnectionStringParameter);
SqlConnection.ClearAllPools();
//Assert
AssertOpenConnectionCount(1, ConnectionStringParameter);
con.Close();
SqlConnection.ClearAllPools();
AssertOpenConnectionCount(0, ConnectionStringParameter);
}
public void TestLeaveConnectionOpenWithSqlTask()
{
//Arrange
SqlConnectionManager con = new SqlConnectionManager(ConnectionStringParameter)
{
LeaveOpen = true
};
//Act
AssertOpenConnectionCount(0, ConnectionStringParameter);
Assert.True(con.State == null);
SqlTask.ExecuteNonQuery(con, "Dummy", "SELECT 1");
Assert.True(con.State == System.Data.ConnectionState.Open);
AssertOpenConnectionCount(1, ConnectionStringParameter);
SqlTask.ExecuteNonQuery(con, "Dummy", "SELECT 1");
AssertOpenConnectionCount(1, ConnectionStringParameter);
Assert.True(con.State == System.Data.ConnectionState.Open);
AssertOpenConnectionCount(1, ConnectionStringParameter);
SqlConnection.ClearAllPools();
//Assert
AssertOpenConnectionCount(1, ConnectionStringParameter);
con.Close();
SqlConnection.ClearAllPools();
AssertOpenConnectionCount(0, ConnectionStringParameter);
}
private static SqlTableInformation CreateSqlTableInformation(string connectionString, string tableName)
{
var connection = SqlConnectionManager.GetConnection(connectionString);
var columns = GetColumnsInfo(connection, tableName);
// Checking if the necessary table exists
if (columns.Count == 0)
{
return(null);
}
var tableInformation = new SqlTableInformation(tableName);
foreach (var column in columns.Values)
{
tableInformation.AddColumnInformation(
new SqlColumnInformation(
column.Name,
column.IsPrimaryKey,
column.IsIdentity,
column.IsComputed,
column.IsNullable,
ConvertSqlTypeToSystemType(column.Type),
ConvertSqlTypeToSqlDbType(column.Type)
));
}
return(tableInformation);
}
public void OneTransactionAndParallelWritingWithMARS()
{
//Arrange
TwoColumnsTableFixture s2c = new TwoColumnsTableFixture(SqlConnection, "TransactionSourceParallelWrite");
s2c.InsertTestData();
TwoColumnsTableFixture d2c1 = new TwoColumnsTableFixture(SqlConnection, "TransactionDest1");
TwoColumnsTableFixture d2c2 = new TwoColumnsTableFixture(SqlConnection, "TransactionDest2");
DbSource <MySimpleRow> source = new DbSource <MySimpleRow>(SqlConnection, "TransactionSourceParallelWrite");
string constring = $"{Config.SqlConnection.RawConnectionString("DataFlow")};MultipleActiveResultSets=True;";
var marscon = new SqlConnectionManager(constring);
DbDestination <MySimpleRow> dest1 = new DbDestination <MySimpleRow>(marscon, "TransactionDest1", batchSize: 2);
DbDestination <MySimpleRow> dest2 = new DbDestination <MySimpleRow>(marscon, "TransactionDest2", batchSize: 2);
Multicast <MySimpleRow> multicast = new Multicast <MySimpleRow>();
//Act & Assert
marscon.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
source.LinkTo(multicast);
multicast.LinkTo(dest1);
multicast.LinkTo(dest2);
source.Execute();
dest1.Wait();
dest2.Wait();
marscon.CommitTransaction();
d2c1.AssertTestData();
d2c1.AssertTestData();
}
protected override void OnSetup()
{
var config = new ScriptDeployerConfig
{
DatabaseLookup = "select 'model' [name]",
ProjectsTableConfig = new ProjectsTableConfig
{
Database = "master", CreateTable = "print 'done'", Insert = "print 'done'",
Read = "select '14.12.01.01' -- {0}"
},
ValidationFilterConfig = new ValidationFilterConfig()
};
Given.Config = config;
Then.Logger = new Logger();
Then.LogSink = new StringLogSink();
Then.Logger.AddLogSink(Then.LogSink);
var manager = new SqlConnectionManager(Given.Config.DatabaseLookup);
manager.OpenConnection(".", "model");
manager.BeginTransaction();
Then.Manager = manager;
}
public void ExecuteNonQuery(SqlCommand cmd)
{
var conn = SqlConnectionManager.GetConnection(_connectionString);
cmd.Connection = conn;
cmd.ExecuteNonQuery();
}
public void BuildScriptCollection(string testName)
{
using (var sqlConnection = SqlConnectionManager.GetOpenConnection())
{
_scriptNames = SqlCommands.GetScriptCollection(testName, sqlConnection);
}
}
public List <Class> GetClassesByTeacherAndCourse(int tid, int cid)
{
using (var classConnection = SqlConnectionManager.GetConnection())
using (var classCommand = new NpgsqlCommand())
{
classCommand.Connection = classConnection;
classCommand.CommandText = "select * from getClassesByTeacherAndCourse(@tid, @cid)";
classCommand.Parameters.AddWithValue("@tid", tid);
classCommand.Parameters.AddWithValue("@cid", cid);
var classReader = classCommand.ExecuteReader();
var classes = new List <Class>();
while (classReader.Read())
{
var c = new Class()
{
Id = int.Parse(classReader["id"].ToString()),
Branch = classReader["branch"].ToString(),
Year = int.Parse(classReader["year"].ToString()),
SectionFrom = int.Parse(classReader["sec_from"].ToString()),
SectionTo = int.Parse(classReader["sec_to"].ToString())
};
classes.Add(c);
}
return(classes);
}
}
public static void RecreateDatabase(string dbName, SqlConnectionString connectionString)
{
var masterConnection = new SqlConnectionManager(connectionString.GetMasterConnection());
DropDatabaseTask.DropIfExists(masterConnection, dbName);
CreateDatabaseTask.Create(masterConnection, dbName);
}
public LoginResult CaretakerLogin(string username, string password)
{
using (var connection = SqlConnectionManager.GetConnection())
using (var command = new NpgsqlCommand())
{
command.Connection = connection;
command.CommandText = "select caretakerLogin(@uname, @pwd)";
command.Parameters.AddWithValue("@uname", username);
command.Parameters.AddWithValue("@pwd", password);
var reader = command.ExecuteReader();
var result = LoginResult.InvalidRollNumber;
if (reader.Read())
{
int x = (int)reader.GetValue(0);
if (x == 1)
{
result = LoginResult.Successful;
}
else if (x == 0)
{
result = result = LoginResult.InvalidPassword;
}
}
return(result);
}
}
void RecreateDatabase(string dbName, SqlConnectionString connectionString)
{
var masterConnection = new SqlConnectionManager(connectionString.CloneWithMasterDbName());
DropDatabaseTask.DropIfExists(masterConnection, dbName);
CreateDatabaseTask.Create(masterConnection, dbName);
}
public Caretaker GetCaretakerInfo(string username)
{
using (var connection = SqlConnectionManager.GetConnection())
using (var command = new NpgsqlCommand()) {
command.Connection = connection;
command.CommandText = "select * from getCaretakerInfo(@uname)";
command.Parameters.AddWithValue("@uname", username);
var reader = command.ExecuteReader();
if (reader.Read())
{
return(new Caretaker()
{
Id = int.Parse(reader["id"].ToString()),
Name = reader["name"].ToString(),
Username = reader["username"].ToString(),
Email = reader["email"].ToString(),
Contact = reader["contact"].ToString(),
Hostel = new Hostel()
{
Id = int.Parse(reader["hostel_id"].ToString()),
Name = reader["hostel_name"].ToString()
}
});
}
return(null);
}
}
static void Main(string[] args)
{
//Set up database
Preparation();
//Define the source
CsvSource <string[]> source = new CsvSource <string[]>("demodata.csv");
//Define the transformation
RowTransformation <string[], Order> rowTrans = new RowTransformation <string[], Order>(
row => new Order()
{
Item = row[1],
Quantity = int.Parse(row[2]) + int.Parse(row[3]),
Price = int.Parse(row[4]) / 100
});
//DbDestination needs a connection manager pointing to the right database
SqlConnectionManager connMan = new SqlConnectionManager("Data Source=localhost;User Id=sa;Password=YourStrong@Passw0rd;Initial Catalog=demo;");
//Define the destination
DbDestination <Order> dest = new DbDestination <Order>(connMan, "OrderTable");
//Link & run flow
source.LinkTo(rowTrans);
rowTrans.LinkTo(dest);
source.Execute();
dest.Wait();
Console.WriteLine("Press any key to continue...");
Console.ReadLine();
}
public void TestTransferBetweenDBs()
{
var sourceConnection = new SqlConnectionManager(new ConnectionString(ConnectionStringSource));
var destConnection = new SqlConnectionManager(new ConnectionString(ConnectionStringDest));
ControlFlow.CurrentDbConnection = new SqlConnectionManager(ConnectionStringSource);
SqlTask.ExecuteNonQuery("Drop source table", @"DROP TABLE IF EXISTS test.Source");
SqlTask.ExecuteNonQuery("Create source table", @"CREATE TABLE test.Source
(Col1 nvarchar(100) null, Col2 int null)");
SqlTask.ExecuteNonQuery("Insert demo data", "insert into test.Source values('Test1',1)");
SqlTask.ExecuteNonQuery("Insert demo data", "insert into test.Source values('Test2',2)");
SqlTask.ExecuteNonQuery("Insert demo data", "insert into test.Source values('Test3',3)");
ControlFlow.CurrentDbConnection = new SqlConnectionManager(ConnectionStringDest);
SqlTask.ExecuteNonQuery("Drop source table", @"DROP TABLE IF EXISTS test.Destination");
SqlTask.ExecuteNonQuery("Create destination table", @"CREATE TABLE test.Destination
(Col1 nvarchar(30) null, Col2 bigint null)");
DBSource source = new DBSource(sourceConnection, "test.Source");
DBDestination dest = new DBDestination(destConnection, "test.Destination");
source.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(3, RowCountTask.Count("test.Destination"));
Assert.AreEqual(1, RowCountTask.Count("test.Destination", "Col1 = 'Test1' AND Col2=1"));
Assert.AreEqual(1, RowCountTask.Count("test.Destination", "Col1 = 'Test2' AND Col2=2"));
Assert.AreEqual(1, RowCountTask.Count("test.Destination", "Col1 = 'Test3' AND Col2=3"));
}
public void TestLeaveConnectionOpenInParallel()
{
//Arrange
SqlConnectionManager con = new SqlConnectionManager(ConnectionStringParameter)
{
LeaveOpen = true,
MaxLoginAttempts = 1
};
//Act
Assert.Throws <InvalidOperationException>(() =>
{
try
{
Task t1 = Task.Factory.StartNew(() => SqlTask.ExecuteNonQuery(con, "Dummy", "WAITFOR DELAY '0:00:01.000'"));
Task t2 = Task.Factory.StartNew(() => SqlTask.ExecuteNonQuery(con, "Dummy", "WAITFOR DELAY '0:00:01.000'"));
t1.Start();
t2.Start();
Task.WaitAll(t1, t2);
//Parallel.Invoke(new ParallelOptions() { TaskScheduler = TaskScheduler.Default },
// () => SqlTask.ExecuteNonQuery(con, "Dummy", "WAITFOR DELAY '0:00:01.000'"),
// () => SqlTask.ExecuteNonQuery(con, "Dummy", "WAITFOR DELAY '0:00:01.000'")
// ); ;
}
catch (AggregateException e)
{
throw e.InnerException;
}
});
con.Close();
SqlConnection.ClearAllPools();
}
public Student GetStudentInfo(string rollno)
{
using (var connection = SqlConnectionManager.GetConnection())
using (var command = new NpgsqlCommand()
{
Connection = connection
}) {
command.CommandText = "select * from getStudentInfo(@rollno)";
command.Parameters.AddWithValue("@rollno", rollno);
var reader = command.ExecuteReader();
if (reader.Read())
{
return(new Student()
{
Id = int.Parse(reader["id"].ToString()),
Name = reader["name"].ToString(),
Rollno = reader["rollno"].ToString(),
PersonalContact = reader["personal_contact"].ToString(),
ParentContact = reader["parent_contact"].ToString(),
Gender = ((BitArray)reader["gender"]).Get(0) ? 'M' : 'F',
Email = reader["email"].ToString(),
Hostel = new Hostel()
{
Id = int.Parse(reader["hostel_id"].ToString()),
Name = reader["hostel_name"].ToString(),
RoomNumber = reader["hostel_room_no"].ToString(),
Type = ((BitArray)reader["gender"]).Get(0) ? "Boys" : "Girls"
}
});
}
return(null);
}
}
static void Main(string[] args)
{
Preparation();
SqlConnectionManager connMan = new SqlConnectionManager("Data Source=.;Initial Catalog=demo;Integrated Security=false;User=sa;password=reallyStrongPwd123");
CsvSource <string[]> source = new CsvSource <string[]>("demodata.csv");
RowTransformation <string[], Order> rowTrans = new RowTransformation <string[], Order>(
row => new Order()
{
Id = int.Parse(row[0]),
Item = row[1],
Quantity = int.Parse(row[2]) + int.Parse(row[3]),
Price = double.Parse(row[4]) * 100
});
DbDestination <Order> dest = new DbDestination <Order>(connMan, "OrderTable");
source.LinkTo(rowTrans);
rowTrans.LinkTo(dest);
source.Execute();
dest.Wait();
Console.WriteLine("Press any key to continue...");
Console.ReadLine();
}
public Teacher GetTeacherByCode(string code)
{
using (var connection = SqlConnectionManager.GetConnection())
using (var command = new NpgsqlCommand())
{
command.Connection = connection;
command.CommandText = "select * from profileteacherinfo(@code)";
command.Parameters.AddWithValue("@code", code);
var reader = command.ExecuteReader();
if (reader.Read())
{
return(new Teacher()
{
Id = int.Parse(reader["tid"].ToString()),
Name = reader["tname"].ToString(),
Code = reader["tcode"].ToString(),
Contact = reader["tcontact"].ToString(),
Designation = reader["tdes"].ToString(),
Email = reader["tmail"].ToString()
});
}
return(null);
}
}
public List <Evaluation> GetClassEvaluationDetail(int classId, int examId)
{
using (var studentsConnection = SqlConnectionManager.GetConnection())
using (var studentsCommand = new NpgsqlCommand())
{
studentsCommand.Connection = studentsConnection;
studentsCommand.CommandText = "select * from getStudentsByClass(@cid)";
studentsCommand.Parameters.AddWithValue("@cid", classId);
var studentsReader = studentsCommand.ExecuteReader();
var evaluations = new List <Evaluation>();
while (studentsReader.Read())
{
var e = new Evaluation()
{
Student = new Student()
{
Id = int.Parse(studentsReader["id"].ToString()),
Rollno = studentsReader["rollno"].ToString(),
Name = studentsReader["name"].ToString()
}
};
using (var evalConnection = SqlConnectionManager.GetConnection())
using (var evalCommand = new NpgsqlCommand())
{
evalCommand.Connection = evalConnection;
evalCommand.CommandText = "select * from getStudentEvaluationDetail(@sid, @eid)";
evalCommand.Parameters.AddWithValue("@sid", e.Student.Id);
evalCommand.Parameters.AddWithValue("@eid", examId);
var evalReader = evalCommand.ExecuteReader();
if (evalReader.Read())
{
e.Id = int.Parse(evalReader["id"].ToString());
e.Examination = new Examination()
{
Id = int.Parse(evalReader["exam_id"].ToString()),
TotalMarks = int.Parse(evalReader["total_marks"].ToString())
};
e.Teacher = new Teacher()
{
Id = int.Parse(evalReader["teacher_id"].ToString())
};
e.MarksObtained = int.Parse(evalReader["marks_obtained"].ToString());
e.DateTime = DateTime.Parse(evalReader["datetime"].ToString());
e.AnswerSheet = evalReader["ans_sheet"].ToString();
e.Status = true;
}
else
{
e.Status = false;
}
}
evaluations.Add(e);
}
return(evaluations);
}
}
public void Ctor_WithConnectionStringWorks()
{
var expectedString = "Server=server1;Database=DB1";
var target = new SqlConnectionManager(expectedString);
target.ConnectionString.Should().Be(expectedString);
}
public static void TestInit(TestContext testContext)
{
string ServerName = testContext.Properties["ServerName"].ToString();
string InitialCatalog = testContext.Properties["InitialCatalog"].ToString();
TestDb = new SqlConnectionManager(ServerName, InitialCatalog);
new CreateSchemaTask(TestDb.SqlConnection).Create("test");
}
public void Update()
{
using (var sqlConnection = SqlConnectionManager.GetOpenConnection())
{
_testNames = SqlCommands.GetTestCollections(ScriptAttribute.TestNames, sqlConnection);
_testBuilds = SqlCommands.GetTestCollections(ScriptAttribute.BuildNames, sqlConnection);
}
}
/// <summary>
/// Creates the LINQ to SQL repository.
/// </summary>
/// <returns>
/// The memory repository.
/// </returns>
private static IRepository CreateLinqToSqlRepository()
{
var connectionString = new ConfigurationConnectionString("BasicSampleDatabase");
var connectionMananger = new SqlConnectionManager(connectionString);
var mappingSourceManager = new SampleMappingSourceManager();
return(new LinqToSqlRepository(connectionMananger, mappingSourceManager));
}
protected void btnTimein_Click(object sender, EventArgs e)
{
var connection = SqlConnectionManager.GetConnection();
string eid = ((string)Session["id"]);
string intime = DateTime.Now.ToString("HH:mm");
string adate = DateTime.Today.ToString("MM/dd/yyyy");
string month = DateTime.Now.Month.ToString();
string year = DateTime.Now.Year.ToString();
string query = "SELECT * FROM [dbo].[tblAttendance] WHERE EId='" + eid + "' AND Adate='" + adate + "'";
var cmd = SqlConnectionManager.GetCommand(query, connection);
cmd.Connection.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
string it = "10:00";
while (reader.Read())
{
it = reader["Intime"].ToString();
}
if (it == "10:00")
{
lblError.Visible = false;
string query2 = "UPDATE tblAttendance SET Intime='" + intime + "', Outtime='19:30', Status='present' WHERE EId='" + eid + "' AND Adate='" + adate + "'";
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.InsertCommand = new SqlCommand(query2, connection);
adapter.InsertCommand.ExecuteNonQuery();
Response.Write("<script>alert('In-Time Registered Successfully.');</script>");
}
else
{
lblError.Visible = true;
lblError.Text = "In-Time already registered!";
}
}
else
{
string query2 = "SELECT * FROM tblEmployee";
var cmd2 = SqlConnectionManager.GetCommand(query2, connection);
using (SqlDataReader reader2 = cmd2.ExecuteReader())
{
if (reader2.HasRows)
{
SqlDataAdapter sa = new SqlDataAdapter();
while (reader2.Read())
{
int empid = reader2.GetInt32(0);
string insertQuery = "INSERT INTO tblAttendance (EId, Intime, Outtime, Adate, Status, Month, Year) VALUES ('" + empid + "', '10:00', '19:30', '" + adate + "', 'absent', '" + month + "', '" + year + "')";
sa.InsertCommand = new SqlCommand(insertQuery, connection);
sa.InsertCommand.ExecuteNonQuery();
}
}
}
Response.Write("<script>alert('Unable to register In-time, try again.');window.location = 'eAttendance.aspx';</script>");
}
}
}
public static void RecreateSqlDatabase(string section)
{
var connManagerMaster = new SqlConnectionManager(
Config.SqlConnection.ConnectionString(section).CloneWithMasterDbName()
);
var dbName = Config.SqlConnection.ConnectionString(section).DbName;
DropAndCreate(connManagerMaster, dbName);
}
public bool IsAvailable(int scheduledClass_id, int classRoom_id, DateTime startDate, DateTime endDate)
{
using (var cm = new SqlConnectionManager())
{
using (var cmd = new Classroom_IsAvailable_p())
{
cmd.Parameters.ScheduledClass_id = scheduledClass_id;
cmd.Parameters.ClassRoom_id = classRoom_id;
cmd.Parameters.BeginDate = startDate;
cmd.Parameters.EndDate = endDate;
cmd.Parameters.IsAvailable = false;
cm.ExecuteNonQuery(cmd, _context);
return cmd.Parameters.IsAvailable.GetValueOrDefault();
}
}
}
public List<Classroom_lkpInfo> LoadClassroomAllWithoutCache()
{
List<Classroom_lkpInfo> result = null;
using(var cm = new SqlConnectionManager())
{
using (var cmd = new Classroom_lkp_LoadAll_p())
{
using(var reader = cm.ExecuteReader(cmd,_context))
{
result = Classroom_lkpInfoAssembler.CreateList(reader);
}
}
}
return result;
}
public void UpdateClassroom(Classroom_lkpInfo info)
{
if (info == null)
{
throw new Exception(ExceptionMessage.InputParameterNull);
}
using (SqlConnectionManager cm = new SqlConnectionManager())
{
using (var cmd = new Classroom_lkp_Save_p())
{
cmd.Parameters.IsInsert = false;
cmd.Parameters.Classroom_id = info.Classroom_id;
cmd.Parameters.School_id = info.School_id;
cmd.Parameters.IsDeleted = info.IsDeleted;
cmd.Parameters.ClassroomName = info.ClassroomName;
cmd.Parameters.Classroom_Blurb_id = info.Classroom_Blurb_id;
cmd.Parameters.DefaultPhysicalCapacity = info.DefaultPhysicalCapacity;
cmd.Parameters.DisplayOrder = info.DisplayOrder;
cmd.Parameters.Insertby = info.Insertby;
cmd.Parameters.Updateby = info.Updateby;
cmd.Parameters.IsHidden = info.IsHidden;
cm.ExecuteNonQuery(cmd, _context);
}
}
}
private DateTime? GetLastDateClassroomScheduled(int classRoomId)
{
using (var cm = new SqlConnectionManager())
{
using (var cmd = new Classroom_GetLastDateScheduled_p())
{
cmd.Parameters.ClassRoom_id = classRoomId;
cmd.Parameters.LastDate = null;
cm.ExecuteNonQuery(cmd, _context);
return cmd.Parameters.LastDate;
}
}
}
static Assignment GetFromSO(ConfigurationContext config)
{
using (var cmd = new Assignment_Load_p())
{
cmd.Parameters.Assignment_id = 99;
using (var sm = new SqlConnectionManager())
using (var dr = sm.ExecuteReader(cmd, config))
{
var data = AssignmentInfoAssembler.CreateList(dr)[0];
return new Assignment
{
Assignment_id = data.Assignment_id,
AssignmentType = (AssignmentType)Enum.Parse(typeof(AssignmentType), data.AssignmentTypeCode),
Parent_id = data.ParentAssignment_id,
Title = data.Title,
};
}
}
}
public BookingContextInfo LoadBookingContextInfo(ScheduledClassInfoList classesInfoList, int student_id, DateTime classUtcDate, DateTime studentBookingBeginUtcDate)
{
BookingContextInfo result = new BookingContextInfo();
using (SqlConnectionManager cm = new SqlConnectionManager())
{
//
// Load Student related data
//
using (var cmd = new Student_Load_p())
{
cmd.Parameters.Student_id = student_id;
using (SqlDataReader reader = cm.ExecuteReader(cmd, _studentContext))
{
result.Student = StudentInfoAssembler.CreateList(reader).First();
}
}
using (Coupon_LoadCount_p cmd = new Coupon_LoadCount_p())
{
cmd.Parameters.Student_id = student_id;
using (SqlDataReader reader = cm.ExecuteReader(cmd, _couponContext))
{
result.StudentCoupon = CouponCountInfoAssembler.CreateList(reader);
}
}
using (var cmd = new Booking_LoadByStudentDateRange_p())
{
cmd.Parameters.Student_id = student_id;
cmd.Parameters.BeginDate = studentBookingBeginUtcDate;
cmd.Parameters.EndDate = DateTime.MaxValue;
using (SqlDataReader reader = cm.ExecuteReader(cmd, _bookingContext))
{
result.StudentBookingInfo = new StudentBookingInfoList(BookingInfoAssembler.CreateList(reader));
}
}
//
// Load scheduled class related data
//
result.ClassesInfoList = classesInfoList;
result.BookingInfoListForClasses = new System.Collections.Generic.List<ScheduledClassBookingInfoList>();
foreach (var c in result.ClassesInfoList.ScheduledClass)
{
using (Booking_LoadByScheduledClass_id_p cmd = new Booking_LoadByScheduledClass_id_p())
{
cmd.Parameters.ScheduledClass_id = c.ScheduledClass_id;
using (SqlDataReader reader = cm.ExecuteReader(cmd, _scheduledClassContext))
{
var bookingList = BookingInfoAssembler.CreateList(reader);
result.BookingInfoListForClasses.Add(new ScheduledClassBookingInfoList(c.ScheduledClass_id, bookingList));
}
}
}
}
return result;
}