public Criteria(DbAction action, String dbSelector)
{
this.criterions = new List <Criterion>();
this.dbTablesLinks = new DbTablesLinks();
this.dbAction = action;
this.dbSelector = dbSelector;
}
public void PersonSaver(DbAction action, Person person)
{
switch (action)
{
case DbAction.Attach:
//if (!_isInTest)
{
if (SharedDbContext.Person.Local.All(l => l.Id != person.Id))
{
SharedDbContext.Person.Attach(person);
}
}
break;
case DbAction.Save:
//if (!_isInTest)
{
//new PersonCacher(SharedDbContext).UpdateItemAndSaveCache(person);
}
break;
default:
throw new ArgumentOutOfRangeException("action");
}
}
public cLog(int index, IDbCommand cmd, DbAction act = DbAction.Null, string method = default(string))
{
Line = index + 1;
Date = DateTime.Now;
this.Method = method;
Action = act != DbAction.Null ? act.ToString() : default(string);
string query = cmd.CommandText;
foreach (IDbDataParameter p in cmd.Parameters)
{
string val = p.Value == null ? "NULL" : p.Value.ToString();
val = val.Replace("'", "''");
switch (p.DbType)
{
case DbType.String: val = "'" + val + "'"; break;
case DbType.DateTime2: val = "'" + val + "'"; break;
case DbType.DateTime: val = "'" + val + "'"; break;
}
query = query.Replace(p.ParameterName, val);
}
query = query.Replace(" ", "\t ");
query = query.Replace("\t", "\t ");
query = query.RemoveWhiteSpaces();
Command = query;
}
public cSqlResult Execute(string sql, List <cSqlParameter> parameters, DbAction action)
{
Open();
if (parameters != null)
{
foreach (cSqlParameter p in parameters)
{
AddSQLParameter(p);
}
}
cSqlResult result = new cSqlResult();
result.AffectedRows = ExecuteNonQuery(sql, action);
if (action == DbAction.Insert)
{
if (result.AffectedRows > 0)
{
result.LastId = GetLastID();
}
}
Close();
return(result);
}
public void BallotSaver(DbAction action, Ballot ballot)
{
switch (action)
{
case DbAction.Attach:
//if (!_isInTest)
{
if (SharedDbContext.Ballot.Local.All(l => l.Id != ballot.Id))
{
SharedDbContext.Ballot.Attach(ballot);
}
}
break;
case DbAction.Save:
//if (!_isInTest)
{
//new BallotCacher(SharedDbContext).UpdateItemAndSaveCache(ballot);
}
break;
default:
throw new ArgumentOutOfRangeException("action");
}
}
public bool WorkDepartment(Department department, DbAction dbAction)
{
try
{
string sqlAdd = String.Empty;
using (SqlCommand command = new SqlCommand())
{
if (dbAction == DbAction.Insert)
{
sqlAdd = $@" INSERT INTO Departments(DepartmentName)
VALUES(@DepartmentName)";
}
else if (dbAction == DbAction.Update)
{
sqlAdd = $"Update Departments set DepartmentName=@DepartmentName where deptId=@deptId";
command.Parameters.Add("@deptId", SqlDbType.Int, 0, "Deptid");
command.Parameters["deptId"].Value = department.DepartmentId;
}
if (sqlAdd != String.Empty)
{
command.Parameters.Add("@DepartmentName", SqlDbType.Date, 0, "DepartmentName");
command.Parameters["DepartmentName"].Value = department.DepartmentName;
command.CommandText = sqlAdd;
command.Connection = dbConnection;
command.ExecuteNonQuery();
}
}
}
catch (Exception e)
{
return(false);
}
return(true);
}
public void BallotSaver(DbAction action, Ballot ballot)
{
switch (action)
{
case DbAction.Attach:
//if (!_isInTest)
{
if (Db.Ballot.Local.All(l => l.C_RowId != ballot.C_RowId))
{
Db.Ballot.Attach(ballot);
}
}
break;
// case DbAction.Save:
// //if (!_isInTest)
// {
// //new BallotCacher(Db).UpdateItemAndSaveCache(ballot);
// }
// break;
default:
throw new ArgumentOutOfRangeException("action");
}
}
public void VoteSaver(DbAction action, Vote vote)
{
switch (action)
{
case DbAction.Attach:
//if (!_isInTest)
{
if (SharedDbContext.Vote.Local.All(l => l.Id != vote.Id))
{
SharedDbContext.Vote.Attach(vote);
}
}
break;
case DbAction.Save:
//if (!_isInTest)
{
//new VoteCacher(SharedDbContext).UpdateItemAndSaveCache(vote);
}
break;
default:
throw new ArgumentOutOfRangeException("action");
}
}
public void PersonSaver(DbAction action, Person person)
{
switch (action)
{
case DbAction.Attach:
//if (!_isInTest)
{
if (Db.Person.Local.All(l => l.C_RowId != person.C_RowId))
{
Db.Person.Attach(person);
}
}
break;
// case DbAction.Save:
// //if (!_isInTest)
// {
// //new PersonCacher(Db).UpdateItemAndSaveCache(person);
// }
// break;
default:
throw new ArgumentOutOfRangeException("action");
}
}
public async Task CreateAsync(T entity)
{
Query query = this.Query().AsEntityInsert(entity);
DbAction <int> fetchId = async command => Convert.ToInt32(await command.ExecuteScalarAsync());
entity.Id = await this.ExecuteAsync(query, fetchId);
}
private void btnClick(object sender, RoutedEventArgs e)
{
obj.Data[0].Total_new_cases = 200;
int i = 0;
var db = DbAction.getData();
foreach (var data in db.Datas)
{
if (i < 3)
{
obj.Data[i].Total_active_cases = data.Total_active_cases;
obj.Data[i].Total_new_cases = data.Total_new_cases_today;
obj.Data[i++].Date = data.Date;
}
else
{
obj.Data.Add(new Sales
{
Date = data.Date,
Total_active_cases = data.Total_active_cases,
Total_new_cases = data.Total_new_cases_today
});
}
}
}
private void btnSave_Click(object sender, EventArgs e)
{
if (tbNewPassword.Text == tbNewPassword2.Text)
{
try
{
if (Crypto.CompareHash(tbOldPassword.Text, DbAction.GetPasswordByUser(credentials.email)))
{
var serverSidePass = cbBackUpPassword.Checked ? tbNewPassword2.Text : string.Empty;
var hushPuppy = Crypto.MakeHash(tbNewPassword2.Text);
ServerAction.ChangePassword(credentials.email, tbOldPassword.Text, serverSidePass);
DbAction.ChangePassword(credentials.email, hushPuppy);
credentials.password = hushPuppy;
Close();
}
else
{
throw new Internals.UnauthorizedAccessException("Wrong Username/Password");
}
}
catch (Exception ex)
{
lblError.Text = ex.Message;
lblError.Visible = true;
}
}
else
{
lblError.Text = "Passwords don't match";
lblError.Visible = true;
}
}
internal void ReadRecord(DataRow pDataRow)
{
StorageId = Convert.ToInt32(pDataRow["SDCardId"]);
StorageDescription = pDataRow["SDCardDescription"].ToString();
StorageSize = pDataRow["SDCardSize"].ToString();
DbConnection connection = new DbConnection(DatabaseSettings.ConnectionString);
DbAction action = connection.GetDbAction();
DataSet ds = null;
if (action.ExecDs(connection.GetConnection(), "SELECT SDCardId, PlaylistId FROM tblSDCardPlaylists WHERE SDCardId = " + StorageId, ref ds))
{
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
Playlists.Add(row["PlaylistId"].ToString());
}
}
}
else
{
LastMessage = action.LastMessage;
}
}
private void btnSave_Click(object sender, EventArgs e)
{
if (Credential.IsEmailValid(tbEmail.Text) && !DbAction.IsUsernameInDatabase(tbEmail.Text) && !ServerAction.IsUsernameInServer(tbEmail.Text))
{
try
{
var serverSidePass = cbBackUpPassword.Checked ? tbPassword.Text : string.Empty;
if (ServerAction.RegisterNewUser(tbEmail.Text, serverSidePass))
{
DbAction.AddNewUser(tbEmail.Text, Crypto.MakeHash(tbPassword.Text));
Close();
}
else
{
lblInvalidRegistration.Text = "Could not Register user. Please try again";
lblInvalidRegistration.Visible = true;
}
}
catch (Exception ex)
{
lblInvalidRegistration.Text = ex.Message;
}
}
else
{
lblInvalidRegistration.Visible = true;
}
}
public static void CreateTable(Dictionary <string, object> tableInfo)
{
string str;
BaseDbActionService service = DbAction.CurrentDB();;
SQLHelper helper;
str = ResolveCreateTableSql(tableInfo);
try
{
helper = new SQLHelper(str, Array.Empty <object>());
service.ExecuteNonQuery(helper);
}
catch (Exception e)
{
throw new Exception("数据表创建失败:执行语句\n" + str, e);
}
finally
{
service.Dispose();
}
return;
}
public void VoteSaver(DbAction action, Vote vote)
{
switch (action)
{
case DbAction.Attach:
//if (!_isInTest)
{
if (Db.Vote.Local.All(l => l.C_RowId != vote.C_RowId))
{
Db.Vote.Attach(vote);
}
}
break;
// case DbAction.Save:
// //if (!_isInTest)
// {
// //new VoteCacher(Db).UpdateItemAndSaveCache(vote);
// }
// break;
default:
throw new ArgumentOutOfRangeException("action");
}
}
/// <summary>
/// Retrieve the list of storage defined.
/// </summary>
/// <returns></returns>
public List <Storage> GetListOfStorage()
{
DbConnection connection = new DbConnection(DatabaseSettings.ConnectionString);
DbAction action = connection.GetDbAction();
DataSet ds = null;
List <Storage> listOfStorage = new List <Storage>();
if (action.ExecDs(connection.GetConnection(), "SELECT SDCardId, SDCardDescription, SDCardSize FROM tblSDCards ORDER BY SDCardDescription", ref ds))
{
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
Storage storage = new Storage();
storage.ReadRecord(row);
listOfStorage.Add(storage);
}
}
return(listOfStorage);
}
LastMessage = action.LastMessage;
return(null);
}
public override object DecodePage(HttpResponseMessage responce, string htmlPage, object mydata)
{
try
{
DbAction dbRequest = (DbAction)mydata;
switch (dbRequest)
{
case DbAction.CreateUser:
string id = htmlPage.Substring(0, htmlPage.Length - 1);
return(id);
case DbAction.DownloadBasicData:
Dictionary <string, List <string> > database = MySqlWebServer.DecodeMySqlDatabase(htmlPage); //convert to readable data
Medals = Convert.ToInt32(database["Medals"][0]);
Credits = Convert.ToInt32(database["Credits"][0]);
Losses = Convert.ToInt32(database["Losses"][0]);
Draws = Convert.ToInt32(database["Draws"][0]);
Wins = Convert.ToInt32(database["Wins"][0]);
SpairParts = Convert.ToInt32(database["SpairParts"][0]);
Rank = Convert.ToInt32(database["Rank"][0]);
Level = Convert.ToInt32(database["Level"][0]);
Protection = Convert.ToInt32(database["Protection"][0]);
return(database);
case DbAction.SendAction:
return(htmlPage);
}
}
catch
{ }
return(true);
}
public void DatabaseActionRegion_ChangeActionSomethingChanged_RestoreRegion_ExpectedRestore()
{
//------------Setup for test--------------------------
var id = Guid.NewGuid();
var act = new DsfSqlServerDatabaseActivity()
{
SourceId = id
};
var src = new Mock <IDbServiceModel>();
var dbsrc = new DbSourceDefinition()
{
Id = id
};
var s2 = new DbSourceDefinition()
{
Id = Guid.NewGuid()
};
var action = new DbAction()
{
Name = "bravo"
};
src.Setup(a => a.RetrieveSources()).Returns(new ObservableCollection <IDbSource>()
{
dbsrc, s2
});
var sourceRegion = new DatabaseSourceRegion(src.Object, ModelItemUtils.CreateModelItem(new DsfSqlServerDatabaseActivity()), enSourceType.SqlDatabase);
//------------Execute Test---------------------------
var dbActionRegion = new DbActionRegion(src.Object, ModelItemUtils.CreateModelItem(act), sourceRegion, new SynchronousAsyncWorker());
var dbActionRegionToRestore = new DbActionMemento
{
IsEnabled = false,
SelectedAction = action,
ToolRegionName = "New Tool Action Region",
Dependants = new List <IToolRegion> {
new DbActionMemento()
},
Errors = new List <string> {
"New Action Region Error"
},
Actions = new List <IDbAction> {
new DbAction()
},
IsActionEnabled = true,
IsRefreshing = false,
LabelWidth = 1.0
};
dbActionRegion.RestoreRegion(dbActionRegionToRestore);
//------------Assert Results-------------------------
Assert.AreEqual(dbActionRegion.SelectedAction, action);
Assert.IsFalse(dbActionRegion.IsEnabled);
}
public void DatabaseActionRegion_ChangeActionSomethingChanged_RegionsNotRestored_Invalid()
{
//------------Setup for test--------------------------
var id = Guid.NewGuid();
var act = new DsfSqlServerDatabaseActivity()
{
SourceId = id
};
var src = new Mock <IDbServiceModel>();
var dbsrc = new DbSourceDefinition()
{
Id = id
};
var action = new DbAction()
{
Name = "bravo"
};
var s2 = new DbSourceDefinition()
{
Id = Guid.NewGuid()
};
var action1 = new DbAction()
{
Name = "bravo"
};
src.Setup(a => a.RetrieveSources()).Returns(new ObservableCollection <IDbSource>()
{
dbsrc, s2
});
var sourceRegion = new DatabaseSourceRegion(src.Object, ModelItemUtils.CreateModelItem(new DsfSqlServerDatabaseActivity()), enSourceType.SqlDatabase);
//------------Execute Test---------------------------
var dbActionRegion = new DbActionRegion(src.Object, ModelItemUtils.CreateModelItem(act), sourceRegion, new SynchronousAsyncWorker());
var clone1 = new Mock <IToolRegion>();
var clone2 = new Mock <IToolRegion>();
var dep1 = new Mock <IToolRegion>();
dep1.Setup(a => a.CloneRegion()).Returns(clone1.Object);
var dep2 = new Mock <IToolRegion>();
dep2.Setup(a => a.CloneRegion()).Returns(clone2.Object);
dbActionRegion.Dependants = new List <IToolRegion> {
dep1.Object, dep2.Object
};
dbActionRegion.SelectedAction = action;
dbActionRegion.SelectedAction = action1;
//------------Assert Results-------------------------
dep1.Verify(a => a.RestoreRegion(clone1.Object), Times.Never);
dep2.Verify(a => a.RestoreRegion(clone2.Object), Times.Never);
}
private void UpdateActionRegionSelectionAction()
{
if (!string.IsNullOrEmpty(CommandText))
{
var command = new DbAction {
Name = CommandText, ExecuteAction = CommandText, SourceId = SourceRegion.SelectedSource.Id
};
ActionRegion.SelectedAction = command;
}
}
/// <summary>
/// add user to the GoogleApp
/// </summary>
/// <param name="username"></param>
public static void AddUserAccount(string username, string email,string firstname,string lastname, string password)
{
using (DbAction db = new DbAction("connStr1"))
{
string sql = string.Format("insert into GoogleApp_Users_t(username,email,firstname,lastname,password,createtime) values('{0}','{1}','{2}','{3}','{4}',getdate())", username, email,firstname,lastname, password);
SqlCommand cmd = new SqlCommand(sql);
db.ExecuetNonQuery(cmd);
}
}
public async Task <T> ExecuteAsync <T>(string query, DbAction <T> action, params QueryParameter[] parameters)
{
using (var connection = await this.connectionFactory.CreateConnectionAsync())
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = query;
this.AddParameters(command, parameters);
return(await action(command));
}
}
public DatabaseRequest(DbAction action, string url, Func<string, RequestData> encoder, Action<object> decoder)
{
GetData = null;
Type = MySqlDI.RequestType.Normal;
ActionType = action;
URL = url;
Name = "";
Description = "";
Decoder = decoder;
Encoder = encoder;
}
private void btnLogIn_Click(object sender, EventArgs e)
{
try
{
var Password = mtbPassword.Text;
string dbPassword = string.Empty;
var TbUsername = tbUsername.Text;
if (DbAction.IsUsernameInDatabase(TbUsername))
{
dbPassword = DbAction.GetPasswordByUser(TbUsername);
}
else
{
var SrvrSidePass = ServerAction.GetServerSidePass(TbUsername, Password);
if (SrvrSidePass != string.Empty)
{
try
{
if (DbAction.AddNewUser(TbUsername, SrvrSidePass))
{
throw new DatabaseConnectionFailure("Error inserting data into Database!");
}
}
catch (DatabaseConnectionFailure error)
{
MessageBox.Show(error.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
lblInfo.Text = "Wrong Username/Password";
lblInfo.Visible = true;
}
}
if (Crypto.CompareHash(Password, dbPassword))
{
Hide();
frm_main frm_Main = new frm_main(tbUsername.Text, dbPassword);
frm_Main.FormClosed += (s, args) => Close();
frm_Main.Show();
}
else
{
throw new Internals.UnauthorizedAccessException("Wrong Username/Password");
}
}
catch (Internals.UnauthorizedAccessException error)
{
MessageBox.Show(error.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void GivenSourceIs()
{
_importOrderAction = new DbAction();
_importOrderAction.Name = "Command";
_importOrderAction.Inputs = new List <IServiceInput> {
new ServiceInput("Prefix", "[[Prefix]]")
};
GetDbServiceModel().Setup(model => model.GetActions(It.IsAny <IDbSource>())).Returns(new List <IDbAction> {
_importOrderAction
});
GetViewModel <ODBCDatabaseDesignerViewModel>().SourceRegion.SelectedSource = _testingDbSource;
}
public void WhenISelectAsSource()
{
_importOrderAction = new DbAction();
_importOrderAction.Name = "HR.TESTPROC9";
_importOrderAction.Inputs = new List <IServiceInput> {
new ServiceInput("EID", "")
};
GetDbServiceModel().Setup(model => model.GetActions(It.IsAny <IDbSource>())).Returns(new List <IDbAction> {
_importOrderAction
});
GetViewModel().SourceRegion.SelectedSource = _greenPointSource;
}
public void WhenISelectedAsTheSource()
{
_selectedAction = new DbAction();
_selectedAction.Name = "getemployees";
_selectedAction.Inputs = new List <IServiceInput> {
new ServiceInput("fname", "")
};
GetDbServiceModel().Setup(model => model.GetActions(It.IsAny <IDbSource>())).Returns(new List <IDbAction> {
_selectedAction
});
GetViewModel().SourceRegion.SelectedSource = _postgresSqlSource;
}
/// <Summary>Add this resultTie to the datastore</Summary>
//protected Func<ResultTie, ResultTie> AddResultTie
//{
// get { return _addResultTie ?? SharedDbContext.ResultTie.Add; }
//}
public void ResultTieSaver(DbAction action, ResultTie resultTie)
{
switch (action)
{
case DbAction.Add:
//if (!_isInTest)
{
resultTie.Id = _tempRowId--;
SharedDbContext.ResultTie.Add(resultTie);
//new ResultTieCacher(SharedDbContext).UpdateItemAndSaveCache(resultTie);
}
//else
//{
// _addResultTie(resultTie);
//}
break;
case DbAction.Attach:
//if (!_isInTest)
{
if (SharedDbContext.ResultTie.Local.All(r => r.Id != resultTie.Id))
{
SharedDbContext.ResultTie.Attach(resultTie);
}
}
break;
case DbAction.Save:
//if (!_isInTest)
{
//new ResultTieCacher(SharedDbContext).UpdateItemAndSaveCache(resultTie);
}
break;
case DbAction.AttachAndRemove:
//if (!_isInTest && resultTie.Id > 0)
if (resultTie.Id > 0)
{
//new ResultTieCacher(SharedDbContext).RemoveItemAndSaveCache(resultTie);
if (SharedDbContext.ResultTie.Local.All(r => r.Id != resultTie.Id))
{
SharedDbContext.ResultTie.Attach(resultTie);
}
SharedDbContext.ResultTie.Remove(resultTie);
}
break;
default:
throw new ArgumentOutOfRangeException("action");
}
}
/// <Summary>Add this result to the datastore</Summary>
// protected void AddResultSummary(ResultSummary resultSummary)
// {
// ResultSummaries.Add(resultSummary);
// if (_addResultSummary != null)
// {
// _addResultSummary(resultSummary);
// }
// else
// {
// resultSummary.C_RowId = tempRowId--;
// Db.ResultSummary.Add(resultSummary);
// new ResultSummaryCacher(Db).UpdateItemAndSaveCache(resultSummary);
// }
// }
public void ResultSummarySaver(DbAction action, ResultSummary resultSummary)
{
switch (action)
{
case DbAction.Add:
resultSummary.C_RowId = _tempRowId--;
//if (!_isInTest)
{
Db.ResultSummary.Add(resultSummary);
//new ResultSummaryCacher(Db).UpdateItemAndSaveCache(resultSummary);
}
//else
//{
// _addResultSummary(resultSummary);
//}
break;
case DbAction.Attach:
//if (!_isInTest)
{
if (Db.ResultSummary.Local.All(r => r.C_RowId != resultSummary.C_RowId))
{
Db.ResultSummary.Attach(resultSummary);
}
}
break;
// case DbAction.Save:
// //if (!_isInTest)
// {
// //new ResultSummaryCacher(Db).UpdateItemAndSaveCache(resultSummary);
// }
// break;
case DbAction.AttachAndRemove:
//if (!_isInTest)
{
//new ResultSummaryCacher(Db).RemoveItemAndSaveCache(resultSummary);
if (Db.ResultSummary.Local.All(r => r.C_RowId != resultSummary.C_RowId))
{
Db.ResultSummary.Attach(resultSummary);
}
Db.ResultSummary.Remove(resultSummary);
}
break;
default:
throw new ArgumentOutOfRangeException("action");
}
}
public void WhenISelectAsSource(string sourceName)
{
if (sourceName == "GreenPoint")
{
_importOrderAction = new DbAction();
_importOrderAction.Name = "dbo.ImportOrder";
_importOrderAction.Inputs = new List <IServiceInput> {
new ServiceInput("ProductId", "")
};
GetDbServiceModel().Setup(model => model.GetActions(It.IsAny <IDbSource>())).Returns(new List <IDbAction> {
_importOrderAction
});
GetViewModel().SourceRegion.SelectedSource = _greenPointSource;
}
}
/// <summary>
/// Excel文档导入到数据库
/// 默认取Excel的第一个表
/// 第一行必须为标题行
/// </summary>
/// <param name="excelFileStream">Excel文档流</param>
/// <param name="insertSql">插入语句</param>
/// <param name="dbAction">更新到数据库的方法</param>
/// <returns></returns>
public static int RenderToDb(Stream excelFileStream, string insertSql, DbAction dbAction)
{
return RenderToDb(excelFileStream, insertSql, dbAction, 0, 0);
}
public void ExecuteInTransaction(DbAction<IDbTransaction, object> action)
{
using(IDbConnection dbConnection = dbPlatform.ProviderFactory.CreateConnection())
{
dbConnection.ConnectionString = connectionString;
dbConnection.Open();
using(IDbTransaction dbTransaction = dbConnection.BeginTransaction())
action(dbTransaction);
} // using
}
/// <summary>
/// Excel文档导入到数据库
/// </summary>
/// <param name="excelFileStream">Excel文档流</param>
/// <param name="insertSql">插入语句</param>
/// <param name="dbAction">更新到数据库的方法</param>
/// <param name="sheetIndex">表索引号，如第一个表为0</param>
/// <param name="headerRowIndex">标题行索引号，如第一行为0</param>
/// <returns></returns>
public static int RenderToDb(Stream excelFileStream, string insertSql, DbAction dbAction, int sheetIndex, int headerRowIndex)
{
int rowAffected = 0;
using (excelFileStream)
{
IWorkbook workbook = new HSSFWorkbook(excelFileStream);
{
ISheet sheet = workbook.GetSheetAt(sheetIndex);
{
StringBuilder builder = new StringBuilder();
IRow headerRow = sheet.GetRow(headerRowIndex);
int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1
for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
{
IRow row = sheet.GetRow(i);
if (row != null)
{
builder.Append(insertSql);
builder.Append(" values (");
for (int j = row.FirstCellNum; j < cellCount; j++)
{
builder.AppendFormat("'{0}',", GetCellValue(row.GetCell(j)).Replace("'", "''"));
}
builder.Length = builder.Length - 1;
builder.Append(");");
}
if ((i % 50 == 0 || i == rowCount) && builder.Length > 0)
{
//每50条记录一次批量插入到数据库
rowAffected += dbAction(builder.ToString());
builder.Length = 0;
}
}
}
}
}
return rowAffected;
}
/// <summary>
/// distinguish capital
/// </summary>
/// <param name="username"></param>
/// <param name="error"></param>
public static String GetUserNameInGoogleUser(string username)
{
using (DbAction db = new DbAction("connStr1"))
{
string sql = string.Format("select username from GoogleApp_Users_t where username = '******'", username);
SqlCommand cmd = new SqlCommand(sql);
return db.ExecuteScalar(cmd).ToString();
}
}
public static void LogsError(string username, string error)
{
using (DbAction db = new DbAction("connStr1"))
{
string sql = string.Format("insert into GoogleApp_Errors_t(username,error,adddate) values('{0}','{1}',getdate())",username,error);
SqlCommand cmd = new SqlCommand(sql);
db.ExecuetNonQuery(cmd);
}
}
/// <summary>
/// Check the User Account
/// </summary>
/// <param name="username"></param>
/// <returns></returns>
public static Boolean UserAccountIsExist(string username)
{
using (DbAction db = new DbAction("connStr1"))
{
string sql = string.Format("select * from GoogleApp_Users_t where username='******'", username);
SqlCommand cmd = new SqlCommand(sql);
if (db.ExecuteScalar(cmd) != null)
return true;
else
return false;
}
}
