public void SøgÅrstaloprettetFrit(SearchForm searchForm)
{
DbConnection = new DBConnectionString();
var constring = new SqlConnection(DbConnection.DbConnectionString);
constring.Open();
// searchForm.dateTimePickerOprettet.Format = DateTimePickerFormat.Custom;
//searchForm.dateTimePickerOprettet.CustomFormat = "dd-MM-yyyy";
var sqlDataAdapter1 =
new SqlDataAdapter(
"Select tblSagsoplysninger.sagens_titel, tblSagsoplysninger.journalNr, tblSagsoplysninger.dato_oprettet, tblSagsoplysninger.dato_afsluttet from tblSagsoplysninger" +
" where dato_oprettet='%" + searchForm.textBoxSagoprettet.Text + "'"
,
constring);
var datatable1 = new DataTable();
sqlDataAdapter1.Fill(datatable1);
if (datatable1.Rows.Count == 0)
{
MessageBox.Show("Ingen rapporter fundet");
}
else
{
MessageBox.Show("fundet =" + datatable1.Rows.Count);
}
searchForm.dataGridView1.DataSource = datatable1;
constring.Close();
searchForm.Refresh();
}
public void SøgFritekstPeriode(SearchForm searchForm)
{
DbConnection = new DBConnectionString();
var constring = new SqlConnection(DbConnection.DbConnectionString);
constring.Open();
var sqlDataAdapter1 =
new SqlDataAdapter(
"Select tblSagsoplysninger.sagens_titel, tblSagsoplysninger.journalNr, tblPeriodetyper.periodetype ,tblEmnetyper.emnetype, tblEmnetyper.beskrivelse, tblMaterialetyper.materialetype from tblSagsoplysninger, tblEmnetyper, tblMaterialetyper, tblPeriodetyper" +
" where periodetype='" + searchForm.PeriodeTPComboBox.SelectedItem +
"' and tblSagsoplysninger.sagens_titel like'%" + searchForm.textboxSagstitel.Text + "%'"
,
constring);
var datatable1 = new DataTable();
sqlDataAdapter1.Fill(datatable1);
//if (datatable1.Rows.Count == 0)
//{
// MessageBox.Show("Ingen rapporter fundet");
//}
//else
//{
// MessageBox.Show("fundet =" + datatable1.Rows.Count);
//}
searchForm.dataGridView1.DataSource = datatable1;
//searchForm.Refresh();
constring.Close();
}
public void SagstitelSøgFritekst(SearchForm searchForm)
{
DbConnection = new DBConnectionString();
var constring = new SqlConnection(DbConnection.DbConnectionString);
constring.Open();
//clear combo hvis man ønsker søge fritekst
//searchForm.comboEmnetyper.ResetText();
//searchForm.PeriodeTPComboBox.ResetText();
//searchForm.MatriealetypeBox.ResetText();
var sqlDataAdapter1 =
new SqlDataAdapter(
"select tblSagsoplysninger.sagens_titel, tblSagsoplysninger.journalNr, tblKontaktpersoner.fornavn, tblKontaktpersoner.efternavn,tblPeriodetyper.periodetype, tblEmnetyper.emnetype, tblEksperimenttyper.eksperimenttype, tblMaterialetyper.materialetype, tblSagsoplysningerEmnetyperLink.sagsIDref from tblSagsoplysninger " +
"join tblKontaktpersoner on tblSagsoplysninger.kontaktpersonIDref=tblKontaktpersoner.kontaktpersonID " +
"join tblSagsoplysningerEmnetyperLink on tblSagsoplysninger.sagsID=tblSagsoplysningerEmnetyperLink.sagsIDref " +
"join tblEmnetyper " +
"on tblSagsoplysningerEmnetyperLink.emneIDref=tblEmnetyper.emneID " +
"join tblSagsoplysningerMaterialetyperLink " +
"on tblSagsoplysninger.sagsID=tblSagsoplysningerMaterialetyperLink.sagsIDref " +
"join tblMaterialetyper " +
"on tblSagsoplysningerMaterialetyperLink.materialeIDref=tblMaterialetyper.materialeID " +
"join tblSagsoplysningerEksperimenttyperLink on tblSagsoplysninger.sagsID=tblSagsoplysningerEksperimenttyperLink.sagsIDref " +
"join tblEksperimenttyper " +
"on tblSagsoplysningerEksperimenttyperLink.eksperimentIDref=tblEksperimenttyper.eksperimentID " +
"join tblSagsoplysningerPeriodetyperLink " +
"on tblSagsoplysninger.sagsID = tblSagsoplysningerPeriodetyperLink.sagsIDref " +
"join tblPeriodetyper " +
"on tblSagsoplysningerPeriodetyperLink.periodeIDref = tblPeriodetyper.periodeID " +
"where sagens_titel " +
"like '%" + searchForm.textBoxFrisøgning.Text +"%'" +
"or fornavn like'%"+ searchForm.textBoxFrisøgning.Text +"%'" +
"or efternavn like'%"+ searchForm.textBoxFrisøgning.Text +"%'" +
"or emnetype like'%"+ searchForm.textBoxFrisøgning.Text +"%'"+
"or materialetype like'%"+ searchForm.textBoxFrisøgning.Text +"%'"+
"or eksperimenttype like'%"+ searchForm.textBoxFrisøgning.Text +"%'"+
"or periodetype like'%"+ searchForm.textBoxFrisøgning.Text +"%'"
, constring);
var datatable = new DataTable();
sqlDataAdapter1.Fill(datatable);
searchForm.dataGridView1.DataSource = datatable;
var rows = searchForm.dataGridView1.RowCount;
if (searchForm.dataGridView1.RowCount == 0)
{
MessageBox.Show("ingen rapporter fundet!");
}
else
{
MessageBox.Show("Fundet: " + rows);
}
constring.Close();
searchForm.dataGridView1.Refresh();
}
public void KontaktpersonSøg(SearchForm searchForm)
{
DbConnection = new DBConnectionString();
var constring = new SqlConnection(DbConnection.DbConnectionString);
constring.Open();
var sqlDataAdapter1 =
new SqlDataAdapter(
"Select tblKontaktpersoner.fornavn,tblKontaktpersoner.efternavn, tblSagsoplysninger.sagens_titel, tblSagsoplysninger.journalNr, tblSagsoplysninger.dato_oprettet, tblSagsoplysninger.dato_afsluttet, tblSagsoplysninger.sags_placering from tblSagsoplysninger, tblKontaktpersoner " +
"where efternavn like'%" + searchForm.textBoxKontaktperson.Text + "%'", constring);
var datatable = new DataTable();
sqlDataAdapter1.Fill(datatable);
searchForm.dataGridView1.DataSource = datatable;
var rows = searchForm.dataGridView1.RowCount;
//if (searchForm.dataGridView1.RowCount == 0)
//{
// MessageBox.Show("ingen rapporter fundet!");
//}
//else
//{
// MessageBox.Show("Fundet: " + rows);
//}
constring.Close();
searchForm.dataGridView1.Refresh();
}
public long AddMoreDepartmentClasses(DBConnectionString.TeacherSubject teacher)
{
int recAffected = Convert.ToInt32(teacher.Insert());
if (recAffected > 0)
{
return teacher.Id;
}
return 0;
}
internal static void AddEntry(NameValuePermission kvtree, ArrayList entries, DBConnectionString entry)
{
if (entry.KeyChain != null)
{
for (NameValuePair pair = entry.KeyChain; pair != null; pair = pair.Next)
{
NameValuePermission permit = kvtree.CheckKeyForValue(pair.Name);
if (permit == null)
{
permit = new NameValuePermission(pair.Name);
kvtree.Add(permit);
}
kvtree = permit;
permit = kvtree.CheckKeyForValue(pair.Value);
if (permit == null)
{
DBConnectionString str2 = (pair.Next != null) ? null : entry;
permit = new NameValuePermission(pair.Value, str2);
kvtree.Add(permit);
if (str2 != null)
{
entries.Add(str2);
}
}
else if (pair.Next == null)
{
if (permit._entry != null)
{
entries.Remove(permit._entry);
permit._entry = permit._entry.Intersect(entry);
}
else
{
permit._entry = entry;
}
entries.Add(permit._entry);
}
kvtree = permit;
}
}
else
{
DBConnectionString str = kvtree._entry;
if (str != null)
{
entries.Remove(str);
kvtree._entry = str.Intersect(entry);
}
else
{
kvtree._entry = entry;
}
entries.Add(kvtree._entry);
}
}
/// <summary>
/// 添加一条记录到图片路径表
/// </summary>
/// <param name="TpId"></param>
/// <param name="UploadTime"></param>
/// <param name="ImagePath"></param>
/// <param name="ThumbnailImgPath"></param>
/// <param name="Sort"></param>
/// <returns></returns>
public void AddImagePath(int TPUId, DateTime UploadTime, string ImgPath, string ThumbnailImgPath, int Sort, string ExportImgPath)
{
string sql = "INSERT INTO dbo.ImageDetail (TPUId,UploadTime,ImgPath,ThumbnailImgPath,Sort,ExportImgPath) VALUES (@TPUId,@UploadTime,@ImgPath,@ThumbnailImgPath,@Sort,@ExportImgPath) SELECT @@IDENTITY";
SqlParameter[] parameters =
{
new SqlParameter("@TPUId", TPUId),
new SqlParameter("@UploadTime", UploadTime),
new SqlParameter("@ImgPath", ImgPath),
new SqlParameter("@ThumbnailImgPath", ThumbnailImgPath),
new SqlParameter("@Sort", Sort),
new SqlParameter("@ExportImgPath", ExportImgPath)
};
ConvertHelper.GetInteger(SqlHelper.ExecuteScalar(DBConnectionString.Get(ConnectionString), CommandType.Text, sql, parameters));
}
public bool Update(Smartphone phone)
{
using (SqlConnection con = new SqlConnection(DBConnectionString.Get()))
{
string query = "UPDATE Smartphones SET Title = @Title, Description = @Description, Price = @Price WHERE Id = @Id";
var cmd = new SqlCommand(query, con);
cmd.Parameters.AddWithValue("@Title", phone.Title);
cmd.Parameters.AddWithValue("@Description", phone.Description);
cmd.Parameters.AddWithValue("@Price", phone.Price);
cmd.Parameters.AddWithValue("@Id", phone.Id);
con.Open();
return(cmd.ExecuteNonQuery() == 1);
}
}
internal MySqlConnection GetDBConnection()
{
string connectionString = new DBConnectionString().ToString();
var conn = new MySqlConnection(connectionString);
try
{
conn.Open();
return(conn);
}
catch (Exception e)
{
return(null);
}
}
/// <summary>
/// 保存图片路径
/// </summary>
/// <param name="TpId"></param>
/// <param name="UserId"></param>
/// <param name="ImagePath"></param>
/// <returns></returns>
public int SaveImagePath(int TpId, int UserId, int CusId, string ImagePath, string ThumbnailImgPath, DateTime ShootTime, string ShootPosition)
{
string sql = @"UPDATE dbo.TaskProjectUserRelation SET Relation=0,ImgPath=@ImgPath,ThumbnailImgPath=@ThumbnailImgPath,ShootTime=@ShootTime,ShootPosition=@ShootPosition WHERE TPId=@TPId AND (UserId=@UserId OR CusId=@CusId) AND Relation in(0,3);UPDATE dbo.TaskProject SET Status=2 WHERE TPId=@TPId";
SqlParameter[] parameters =
{
new SqlParameter("@TPId", TpId),
new SqlParameter("@UserId", UserId),
new SqlParameter("@CusId", CusId),
new SqlParameter("@ImgPath", ImagePath),
new SqlParameter("@ThumbnailImgPath", ThumbnailImgPath),
new SqlParameter("@ShootTime", ShootTime),
new SqlParameter("@ShootPosition", ShootPosition)
};
return(SqlHelper.ExecuteNonQuery(DBConnectionString.Get(ConnectionString), CommandType.Text, sql, parameters));
}
internal void Import()
{
var connectionString = new DBConnectionString().ToString();
using (var connToMySql = new MySqlConnection(connectionString))
{
connToMySql.Open();
MySqlCommand cmd = connToMySql.CreateCommand();
string sql = string.Format("LOAD DATA INFILE '{0}' IGNORE INTO TABLE scada.hpic_rec FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' lines terminated by '\n' (time,Doserate,Highvoltage,Battery,Temperature);", this.csvFile);
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
public void Connect()
{
try
{
string connectionString = new DBConnectionString().ToString();
this.conn = new MySqlConnection(connectionString);
this.conn.Open();
this.cmd = this.conn.CreateCommand();
}
catch (Exception e)
{
RecordManager.DoSystemEventRecord(Device.Main, e.Message, RecordType.Error);
// disable RetryConnection by Kaikai
//this.RetryConnection(e);
}
}
private static void TimerTick(object sender, EventArgs e)
{
foreach (var rentedCar in rentedCars)
{
if (rentedCar.Item2 < DateTime.Now)
{
var conn = new DBConnectionString();
var car = conn.Автомобиль.FirstOrDefault(x => x.id == rentedCar.Item1);
if (car != null)
{
car.Доступность = true;
}
rentedCars.Remove(rentedCar);
conn.SaveChangesAsync();
}
}
}
internal void CreateDAQDB()
{
var s = new DBConnectionString();
s.Database = "mysql";
var connectionString = s.ToString();
using (var connToMySql = new MySqlConnection(connectionString))
{
connToMySql.Open();
MySqlCommand cmd = connToMySql.CreateCommand();
cmd.CommandText = "CREATE DATABASE if NOT EXISTS scada";
cmd.ExecuteNonQuery();
cmd.Dispose();
}
}
public void SøgSpecifiktEmnetyperFriTeskt(SearchForm searchForm)
{
DbConnection = new DBConnectionString();
var constring = new SqlConnection(DbConnection.DbConnectionString);
constring.Open();
var sqlDataAdapter1 =
new SqlDataAdapter(
"select tblSagsoplysninger.sagens_titel, tblSagsoplysninger.journalNr, tblKontaktpersoner.fornavn, tblKontaktpersoner.efternavn, tblEmnetyper.emnetype,tblPeriodetyper.periodetype, tblSagsoplysningerEmnetyperLink.sagsIDref from tblSagsoplysninger " +
"jOIN tblKontaktpersoner " +
"ON tblSagsoplysninger.kontaktpersonIDref = tblKontaktpersoner.kontaktpersonID " +
"join tblSagsoplysningerEmnetyperLink " +
"on tblSagsoplysninger.sagsID = tblSagsoplysningerEmnetyperLink.sagsIDref " +
"join tblEmnetyper " +
"on tblSagsoplysningerEmnetyperLink.emneIDref = tblEmnetyper.emneID " +
"join tblSagsoplysningerPeriodetyperLink " +
"on tblSagsoplysninger.sagsID = tblSagsoplysningerPeriodetyperLink.sagsIDref " +
"join tblPeriodetyper " +
"on tblSagsoplysningerPeriodetyperLink.periodeIDref = tblPeriodetyper.periodeID " +
"where sagens_titel " +
"like'%" + searchForm.textboxSagstitel.Text + "%' " +
"and emnetype " +
"like'%" + searchForm.comboEmnetyper.Text + "%'"+
"and periodetype like'%" + searchForm.PeriodeTPComboBox.Text+ "%'"
,
constring);
var datatable1 = new DataTable();
sqlDataAdapter1.Fill(datatable1);
if (datatable1.Rows.Count == 0)
{
MessageBox.Show("Ingen rapporter fundet");
}
else
{
MessageBox.Show("fundet =" + datatable1.Rows.Count);
}
searchForm.dataGridView1.DataSource = datatable1;
constring.Close();
searchForm.Refresh();
}
private NameValuePermission(NameValuePermission permit)
{
this._value = permit._value;
this._entry = permit._entry;
this._tree = permit._tree;
if (this._tree != null)
{
NameValuePermission[] permissionArray = this._tree.Clone() as NameValuePermission[];
for (int i = 0; i < permissionArray.Length; i++)
{
if (permissionArray[i] != null)
{
permissionArray[i] = permissionArray[i].CopyNameValue();
}
}
this._tree = permissionArray;
}
}
public void FillPeriodeCombobox(SearchForm searchForm)
{
DbConnection = new DBConnectionString();
var constring = new SqlConnection(DbConnection.DbConnectionString);
constring.Open();
var sqlDataAdapterEm = new SqlDataAdapter("select * from tblPeriodetyper order by periodetype", constring);
var datatableCmEm = new DataTable();
sqlDataAdapterEm.Fill(datatableCmEm);
for (var i = 0; i < datatableCmEm.Rows.Count; i++)
{
searchForm.PeriodeTPComboBox.Items.Add(datatableCmEm.Rows[i]["periodetype"]);
}
constring.Close();
}
protected override void Execute(CodeActivityContext context)
{
String dbconnStr = DBConnectionString.Get(context);
String query = "SELECT * FROM users WHERE usertype='" + this.UserType.Get(context) + "'";
List <User> filteredUsers = new List <User>();
DataSet ds = new DataSet();
if (dbconnStr == null)
{
dbconnStr = ConfigurationManager.ConnectionStrings["ApprovalProcessDB"].ConnectionString;
}
using (SqlConnection sCon = new SqlConnection(dbconnStr))
{
using (SqlDataAdapter sAda = new SqlDataAdapter(query, sCon))
{
sAda.Fill(ds);
if (ds.Tables["Table"] != null)
{
foreach (DataRow dr in ds.Tables["Table"].Rows)
{
// Ensure no selection of user from the user requesting approval
if (!(new Guid((String)dr["guid"])).Equals(this.UserContext.Get(context).Id))
{
filteredUsers.Add(new User((String)dr["username"], (String)dr["usertype"], (String)dr["addressrequest"], (String)dr["addressresponse"], (String)dr["guid"]));
}
}
}
}
}
// Only return as many users as is requested
int remove = filteredUsers.Count - this.SelectXUsers.Get(context);
for (int i = 0; i < remove; i++)
{
filteredUsers.Remove(filteredUsers.Last());
}
this.SelectedUsers.Set(context, filteredUsers);
}
public void DeltCar(Guid carId, bool typ)
{
var connection = new DBConnectionString();
var zakaz = (new DBConnectionString()).Заказ.ToArray();
var otziv = (new DBConnectionString()).Отзыв.ToArray();
var car = connection.Автомобиль.Single(o => o.id == carId);
foreach (var zak in zakaz)
{
if (zak.idАвтомобиль == carId)
{
foreach (var otz in otziv)
{
if (zak.id == otz.idЗаказ)
{
var itemOtz = connection.Отзыв.Single(o => o.id == otz.id);
connection.Отзыв.Remove(itemOtz);
try
{
var itemZakaz = connection.Заказ.Single(o => o.id == zak.id);
connection.Заказ.Remove(itemZakaz);
}
catch { };
}
}
}
}
connection.Автомобиль.Remove(car);
connection.SaveChanges();
// Clients.CallerState.showMenupolzSdan();
if (typ)
{
Clients.Caller.showMenupolzSdan();
}
else
{
Clients.Caller.showCarsAdmin();
}
}
public string Create(DBConnectionString.Section section)
{
try
{
if (ModelState.IsValid)
{
section.CreatedBy = _userStatistics.UserId;
section.InsertedOn = DateTime.Now;
if (Convert.ToInt32(section.Insert()) > 0)
{
return Convert.ToString(true);
}
return Convert.ToString(false);
}
return Convert.ToString(false);
}
catch (Exception ex)
{
return ex.Message.ToString();
}
}
/// <summary>
/// 添加微信关注者用户
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public int AddUser(User user)
{
const string sql = @"INSERT INTO dbo.WerXinUser (uPassWord ,NickName ,Sex , City ,OpenId,AreaName ) VALUES (@uPassWord,@NickName,@Sex,@City,@OpenId,@AreaName) SELECT @@IDENTITY";
SqlParameter[] parameters =
{
new SqlParameter("@uPassWord", SqlDbType.NVarChar, 50),
new SqlParameter("@NickName", SqlDbType.NVarChar, 50),
new SqlParameter("@Sex", SqlDbType.Int),
new SqlParameter("@City", SqlDbType.NVarChar, 20),
new SqlParameter("@OpenId", SqlDbType.VarChar, 50),
new SqlParameter("@AreaName", SqlDbType.NVarChar, 20)
};
parameters[0].Value = user.PassWord;
parameters[1].Value = user.NickName;
parameters[2].Value = user.Sex;
parameters[3].Value = user.City;
parameters[4].Value = user.OpenId;
parameters[5].Value = user.AreaName;
var userId = ConvertHelper.GetInteger(SqlHelper.ExecuteScalar(DBConnectionString.Get(ConnectionString), CommandType.Text, sql, parameters));
return(userId);
}
public static async Task <int> InsertSeniorAsync(SeniorsModel model)
{
using (IDbConnection connection = new MySqlConnection(DBConnectionString.GetConnectionString()))
{
string query = @"insert into seniors (SeniorId, FirstName, MiddleName, LastName, Gender, DOB, MaritialStatus, ContactNum, Religion, Occupation, Barangay, Municipality, Province, Photo) values (@SeniorID, upper(@FirstName), upper(@MiddleName), upper(@LastName), @Gender, @DOB, @MaritialStatus, @ContactNum, @Religion, @Occupation, upper(@Barangay), upper(@Municipality), upper(@Province), @Photo)";
var parameters = new DynamicParameters();
parameters.Add("SeniorID", model.SeniorID, DbType.Int32);
parameters.Add("FirstName", model.FirstName, DbType.String);
parameters.Add("MiddleName", model.MiddleName, DbType.String);
parameters.Add("LastName", model.LastName, DbType.String);
parameters.Add("Gender", model.Gender, DbType.String);
parameters.Add("DOB", model.DOB, DbType.Date);
parameters.Add("MaritialStatus", model.MaritialStatus, DbType.String);
parameters.Add("ContactNum", model.ContactNum, DbType.String);
parameters.Add("Religion", model.Religion, DbType.String);
parameters.Add("Occupation", model.Occupation, DbType.String);
parameters.Add("Barangay", model.Barangay, DbType.String);
parameters.Add("Municipality", model.Municipality, DbType.String);
parameters.Add("Province", model.Province, DbType.String);
parameters.Add("Photo", model.Photo, DbType.Binary);
return(await connection.ExecuteAsync(query, parameters));
}
}
public void CngAuto(Guid id, string marka, string model, string opis, string stoim2, string file, bool typ)
{
var connection = new DBConnectionString();
var car = connection.Автомобиль.Single(o => o.id == id);
// var models = connection.Модель.Single(x => x.Марка == marka && x.Модель1 == model);
//car.idМодель = models.id;
car.Описание = opis;
decimal stoim = 0;
if (stoim2 == "")
{
Clients.Caller.alertFuncCl("Введите стоиомость автомобиля");
}
else
{
stoim = Decimal.Parse(stoim2);
if (stoim <= 0 || stoim >= 100000)
{
Clients.Caller.alertFuncCl("Введите адекватную стоиомость автомобиля");
}
else
{
car.Стоимость = stoim;
car.Фото = file;
connection.SaveChanges();
if (typ)
{
Clients.Caller.showMenupolzSdan();
}
else
{
Clients.Caller.showCarsAdmin();
}
}
}
}
public void DeltPolz(Guid userId)
{
var connection = new DBConnectionString();
var zakaz = (new DBConnectionString()).Заказ.ToArray();
var otziv = (new DBConnectionString()).Отзыв.ToArray();
var cars = (new DBConnectionString()).Автомобиль.ToArray();
var user = connection.Пользователь.Single(o => o.id == userId);
foreach (var car in cars)
{
if (car.idВладелец == userId)
{
foreach (var zak in zakaz)
{
if (zak.idАвтомобиль == car.id)
{
foreach (var otz in otziv)
{
if (zak.id == otz.idЗаказ)
{
var itemOtz = connection.Отзыв.Single(o => o.id == otz.id);
connection.Отзыв.Remove(itemOtz);
}
}
var itemZakaz = connection.Заказ.Single(o => o.id == zak.id);
connection.Заказ.Remove(itemZakaz);
}
}
var itemCar = connection.Автомобиль.Single(o => o.id == car.id);
connection.Автомобиль.Remove(itemCar);
}
}
connection.Пользователь.Remove(user);
connection.SaveChanges();
Clients.Caller.showMenuTablpolz();
}
public bool Register(UserViewModel userVM)
{
using (SqlConnection con = new SqlConnection(DBConnectionString.Get()))
{
var query = "SELECT Id,Email, Password, IsAdmin FROM Users Where Email = @Email AND Password = @Password";
var cmd = new SqlCommand(query, con);
cmd.Parameters.AddWithValue("@Email", userVM.Email);
cmd.Parameters.AddWithValue("@Password", userVM.Password);
con.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
return(false);
}
}
query = "INSERT INTO Users(Email,Password) VALUES(@Email, @Password)";
cmd = new SqlCommand(query, con);
cmd.Parameters.AddWithValue("@Email", userVM.Email);
cmd.Parameters.AddWithValue("@Password", userVM.Password);
int result = cmd.ExecuteNonQuery();
return(result == 1);
}
}
public MySqlConnection GetMySqlConnection()
{
string installPath = Assembly.GetExecutingAssembly().Location;
string fileName = string.Format("{0}\\..\\local.ip", installPath);
var s = new DBConnectionString();
s.Address = "127.0.0.1";
if (File.Exists(fileName))
{
using (StreamReader sr = new StreamReader(fileName))
{
string ip = sr.ReadLine();
if (ip != null && ip.Length > 0)
{
s.Address = ip;
}
}
}
this.ConnectionString = s.ToString();
var conn = new MySqlConnection(this.ConnectionString);
if (conn != null)
{
try
{
conn.Open();
return(conn);
}
catch (Exception e)
{
string msg = e.Message;
}
}
return(null);
}
// <IPermission class="OleDbPermission" version="1" AllowBlankPassword=false>
// <keyword name="provider">
// <value value="sqloledb"/>
// <value value="sqloledb.1"/>
// </keyword>
// </IPermission>
/// <include file='doc\OleDbPermission.uex' path='docs/doc[@for="OleDbPermission.FromXml"]/*' />
override public void FromXml(SecurityElement securityElement)
{
base.FromXml(securityElement);
ArrayList children = securityElement.Children;
if (IsUnrestricted() || (null == children))
{
return;
}
int count;
Hashtable hash = new Hashtable();
foreach (SecurityElement keyElement in children)
{
string keyword = keyElement.Attribute(ODB._Name);
if (null != keyword)
{
keyword = keyword.ToLower(CultureInfo.InvariantCulture);
}
if (keyElement.Tag.Equals(ODB._Keyword) && (null != keyword) && (ODB.Provider == keyword))
{
ArrayList keyvaluepair = keyElement.Children;
if (null != keyvaluepair)
{
count = keyvaluepair.Count;
for (int i = 0; i < count; ++i)
{
SecurityElement valueElement = (SecurityElement)keyvaluepair[i];
string value = (string)valueElement.Attribute(ODB._Value);
if (valueElement.Tag.Equals(ODB._Value) && (null != value))
{
value = value.Trim();
if (0 < value.Length)
{
hash[value] = null;
}
}
}
}
}
}
count = hash.Count;
if (0 < count)
{
string[] oldrestrictions = _providerRestriction; // appending to
if (null != oldrestrictions)
{
count += oldrestrictions.Length;
}
string[] restrictions = new string[count];
hash.Keys.CopyTo(restrictions, 0);
if (null != oldrestrictions)
{
oldrestrictions.CopyTo(restrictions, count - oldrestrictions.Length);
}
_providerRestriction = DBConnectionString.RemoveDuplicates(restrictions);
_providers = null;
}
}
static void Main(string[] args)
{
var sqlCon = new DBConnectionData <SqlConnection>(
"SQLServer"
, new ConnectionAuth.UserAndPass("u", "p")
, "DBSql"
);
var sqlCS = sqlCon.ToString();
var hanaCon = new DBConnectionData <HanaConnection>(
"hanaServer"
, new ConnectionAuth.UserAndPass("u", "p")
);
var hanaCS = hanaCon.ToString();
//Simple Examples
//Simple Example 1
var sqlQuery = Command <SqlConnection>("select '123'").Then(c => (string)c.ExecuteScalar());
string sqlQueryResult = sqlQuery.Run(sqlCS);
//Simple Example 2 - with specific provider's DbParameters(HanaParameter in this case)
var bolOfRead =
Command <HanaConnection>("select :p1 from dummy",
new HanaParameter {
ParameterName = "p1", HanaDbType = HanaDbType.NVarChar, Value = "123"
}
).Then(c => c.ExecuteReader())
.ReadRow().Map(mr => mr.Map(r => r.First().Value.ToString()).ValueOr("NoValue"))
.Run(hanaCS);
//Queries that contain value(DBProvider wont be used)
Queries <string, DBState <HanaConnection> > d1 = s => ("", s);
//Multiple DBProvider options
var oneOfQueries =
Command <SqlConnection>("select @pSQL1"
//This SqlParameter will be given only to the SqlCommand
, new SqlParameter {
ParameterName = "@pSQL1", DbType = System.Data.DbType.Int32, Value = 1
}
).Or(Command <HanaConnection>("select :p1 from dummy"))
//DBMonad DbParameters can be supplied those parameters will be given to all the DbProviers commands options
.AddParameters(new DbParameter.BigInt("p1", 2))
.Then(c => c.ExecuteScalar()).Map(Convert.ToInt32);
//Run DBConnectionData (child object of DBConnectionString)
int sqlResult = oneOfQueries.Run(sqlCon);
int hanaResult = oneOfQueries.Run(hanaCon);
//or can be either DBConnectionString
var sqlConStr = new DBConnectionString <SqlConnection>(sqlCS);
int sqlResult2 = oneOfQueries.Run(sqlConStr);
//This can be a DBConnectionString/DBConnectionData with any connection type. If command from file cannot find an emmbeded query file with the specified name
//And extension name(based on the connection type name) runtime error gonna be thrown.
var resultFromQueriesFromFile = CommandFromFile("exampleQuery").Then(c => c.ExecuteScalar()).Run(sqlCon);
var EitherqueryFromFileOrQueryFromCommand =
//Will run for sql only the rest gonna use CommandFromFile
Command <SqlConnection>("select 'inlined query'")
.Or(
CommandFromFile("exampleQuery")
).Then(c => c.ExecuteScalar());
var resultFromEmbedded = EitherqueryFromFileOrQueryFromCommand.Run(sqlCon);
var resultFromCommand = EitherqueryFromFileOrQueryFromCommand.Run(hanaCon);
var str =
CommandFromFile("exampleQuery").Then(c => c.ExecuteScalar()).Map(Convert.ToString)
.FlatMap(i =>
Command("select 'im string'").Then(c => (string)c.ExecuteScalar())
).Run(new DBConnectionString <SqlConnection>(sqlCS));
//LINQ example
var qs =
from queryResult in Command <SqlConnection>("select 3.2").Then(c => c.ExecuteScalar()).Map(Convert.ToDouble)
from queryResult2 in Command <SqlConnection>("select GetDate()").Then(c => c.ExecuteScalar()).Map(Convert.ToDateTime)
//Note that the queries state needs to stay the same throwout the computation so the following wont compile
//If alternative is needed "Or" should be used before "Then"
select(queryResult, queryResult2);
var(r1, r2) = qs.Run(sqlCS);
//Multiple DBProvider options
var qs2 =
from queryResult in Command <SqlConnection>("select 3.2").Or(Command <HanaConnection>("select 4.3 from dummy"))
.Then(c => c.ExecuteScalar()).Map(Convert.ToDecimal)
from queryResult2 in Command <SqlConnection>("select 6.1").Or(Command <HanaConnection>("select 7.4 from dummy"))
.Then(c => c.ExecuteScalar()).Map(Convert.ToDecimal)
select(queryResult, queryResult2);
var(mulDBR1, mulDBR2) = qs2.Run(new DBConnectionString <SqlConnection>(sqlCS));
//Transactions
var(mulDBR3, mulDBR4) = qs2.RunWithTransaction(new DBConnectionString <HanaConnection>(hanaCS));
//If queries return type is of type Option<T,TError>
//if runned in transaction and TError was returned, the transaction will be rolled back
var qsT =
from queryResult in Command <SqlConnection>("select 3.2").Or(Command <HanaConnection>("select 4.3 from dummy"))
.Then(c => c.ExecuteScalar()).Map(Convert.ToDecimal)
from queryResult2 in Command <SqlConnection>("select 6.1").Or(Command <HanaConnection>("select 7.4 from dummy"))
.Then(c => c.ExecuteScalar()).Map(Convert.ToDecimal)
select Option.None <TMDBConnector.Unit, string>("some error this will rollback the transaction");
var optionalWillContainErr = qsT.RunWithTransaction(sqlCon);
//Streaming (Reactive Extensions)
var dbMainStream = CommandFromFile("selectALotOfRowsQuery")
.Then(DB.EnumerateRows).ToObservable(sqlCon);
var stopTrigger =
Observable.Interval(TimeSpan.FromSeconds(2))
.Select(_ =>
CommandFromFile("checkIfTableHasNewRows").Then(c => c.ExecuteScalar()).Map(Convert.ToInt32)
.Run(hanaCon)
);
var bigQueryWithRefresh = dbMainStream.WithLatestFrom(stopTrigger, (d, i) => (d: d, i: i))
.TakeWhile(t => (int)t.d["c1"] < t.i).Select(t => t.d);
stopTrigger.Subscribe(row => Console.WriteLine(row.ToString()));
Console.ReadKey();
}
public MySqlConnection CreateMySqlConnection()
{
string connectionString = new DBConnectionString().ToString();
return(new MySqlConnection(connectionString));
}
public override void Add(string connectionString, string restrictions, KeyRestrictionBehavior behavior)
{
DBConnectionString constr = new DBConnectionString(connectionString, restrictions, behavior, null, true);
AddPermissionEntry(constr);
}
/// <summary>
/// 傳入連線字串物件的建構子
/// </summary>
/// <param name="dbconnstr">連線字串物件</param>
public DataAccess(DBConnectionString dbconnstr)
{
_connectionString = dbconnstr.ConnectionString;
ParamOutput = null;
}
internal bool CheckValueForKeyPermit(DBConnectionString parsetable)
{
if (parsetable == null)
{
return false;
}
bool isEmpty = false;
NameValuePermission[] permissionArray = this._tree;
if (permissionArray != null)
{
isEmpty = parsetable.IsEmpty;
if (!isEmpty)
{
for (int i = 0; i < permissionArray.Length; i++)
{
NameValuePermission permission = permissionArray[i];
if (permission != null)
{
string keyword = permission._value;
if (parsetable.ContainsKey(keyword))
{
string keyInQuestion = parsetable[keyword];
NameValuePermission permission2 = permission.CheckKeyForValue(keyInQuestion);
if ((permission2 != null) && permission2.CheckValueForKeyPermit(parsetable))
{
isEmpty = true;
}
else
{
return false;
}
}
}
}
}
}
DBConnectionString str = this._entry;
if (str != null)
{
isEmpty = str.IsSupersetOf(parsetable);
}
return isEmpty;
}
public override void Add(string connectionString, string restrictions, KeyRestrictionBehavior behavior)
{
DBConnectionString constr = new DBConnectionString(connectionString, restrictions, behavior, SqlConnectionString.GetParseSynonyms(), false);
AddPermissionEntry(constr);
}
internal void Intersect(ArrayList entries, NameValuePermission target)
{
if (target == null)
{
this._tree = null;
this._entry = null;
}
else
{
if (this._entry != null)
{
entries.Remove(this._entry);
this._entry = this._entry.Intersect(target._entry);
entries.Add(this._entry);
}
else if (target._entry != null)
{
this._entry = target._entry.Intersect(null);
entries.Add(this._entry);
}
if (this._tree != null)
{
int length = this._tree.Length;
for (int i = 0; i < this._tree.Length; i++)
{
NameValuePermission permission = target.CheckKeyForValue(this._tree[i]._value);
if (permission != null)
{
this._tree[i].Intersect(entries, permission);
}
else
{
this._tree[i] = null;
length--;
}
}
if (length == 0)
{
this._tree = null;
}
else if (length < this._tree.Length)
{
NameValuePermission[] permissionArray = new NameValuePermission[length];
int index = 0;
int num4 = 0;
while (index < this._tree.Length)
{
if (this._tree[index] != null)
{
permissionArray[num4++] = this._tree[index];
}
index++;
}
this._tree = permissionArray;
}
}
}
}
public string Edit(DBConnectionString.ClassRoom objClass, string token)
{
try
{
if (ModelState.IsValid)
{
objClass.ModifiedBy = _userStatistics.UserId;
objClass.ModifiedOn = DateTime.Now;
if (objClass.Update() > 0)
{
SaveFiles(token, this.GetType().Name, objClass.ClassRoomId);
return Convert.ToString(true);
}
return Convert.ToString(false);
}
return Convert.ToString(false);
}
catch (Exception ex)
{
return ex.Message.ToString();
}
}
public DBConnectionStringController(DBConnectionString dbconnectionstring)
{
_dbconnectionstring = dbconnectionstring;
}
public void SøgComboBox(SearchForm searchForm)
{
DbConnection = new DBConnectionString();
var constring = new SqlConnection(DbConnection.DbConnectionString);
constring.Open();
var comboSøg =
"Select tblEmnetyper.emnetype, tblEmnetyper.beskrivelse, tblSagsoplysninger.sagens_titel, tblSagsoplysninger.journalNr, tblMaterialetyper.materialetype, tblPeriodetyper.periodetype from tblSagsoplysninger, tblEmnetyper, tblMaterialetyper, tblPeriodetyper" +
" where emnetype='" + searchForm.comboEmnetyper.Text +
"'and materialetype='" + searchForm.MatriealetypeBox.Text +
"'and periodetype='" + searchForm.PeriodeTPComboBox.Text +
"' and tblSagsoplysninger.sagens_titel like'" + searchForm.textboxSagstitel.Text + "%'";
var sqlcom = new SqlCommand(comboSøg, constring);
var sqlAdapter = new SqlDataAdapter();
sqlAdapter.SelectCommand = sqlcom;
var datatable = new DataTable();
sqlAdapter.Fill(datatable);
searchForm.dataGridView1.DataSource = datatable;
constring.Close();
}
/// <summary>
/// 对过期不进行任务进行解锁
/// </summary>
/// <param name="tpids"></param>
/// <param name="tpuids"></param>
/// <returns></returns>
public int UnLockTask(string tpids, string tpuids)
{
string sql = @"UPDATE dbo.TaskProject SET Status=0 WHERE TPId IN(" + tpids + ");UPDATE dbo.TaskProjectUserRelation SET Relation=3 WHERE TPUId IN(" + tpuids + ")";
return(SqlHelper.ExecuteNonQuery(DBConnectionString.Get(OutdoorMonitor), CommandType.Text, sql));
}
/// <summary>
/// 根据tpuid获取areaid+tid+tpid
/// </summary>
/// <param name="tpuid"></param>
/// <returns></returns>
public string GetIdByTPUId(int tpuid)
{
string sql = @"SELECT cast(r.AreaId as varchar(5))+'_'+cast(tp.TId as varchar(12))+'_'+cast(tp.TPId as varchar(12)) AS Id FROM dbo.TaskProject tp LEFT JOIN dbo.Region r ON tp.RegionId = r.RegionId WHERE tp.TPId=(SELECT TPId FROM dbo.TaskProjectUserRelation WHERE TPUId=@TPUId)";
return(ConvertHelper.GetString(SqlHelper.ExecuteScalar(DBConnectionString.Get(ConnectionString), CommandType.Text, sql, new SqlParameter("@TPUId", tpuid))));
}
public bool UpdateTeacherSubjects(DBConnectionString.TeacherSubject teacher)
{
int recAffected = teacher.Update();
if (recAffected > 0)
{
return true;
}
return false;
}
public void ÅrstalSøgBetween(SearchForm searchForm)
{
DbConnection = new DBConnectionString();
var constring = new SqlConnection(DbConnection.DbConnectionString);
constring.Open();
//searchForm.dateTimePickerOprettet.Format = DateTimePickerFormat.Custom;
//searchForm.dateTimePickerOprettet.CustomFormat = "dd-MM-yyyy";
//searchForm.dateTimePickerAfslut.Format = DateTimePickerFormat.Custom;
//searchForm.dateTimePickerAfslut.CustomFormat = "dd-MM-yyyy";
var datoOprettetList = new List<string>();
//var sqlDataAdapter1 =
// new SqlDataAdapter(
// "Select tblSagsoplysninger.dato_oprettet from tblSagsoplysninger"
// ,
// constring);
//var datatable1 = new DataTable();
//sqlDataAdapter1.Fill(datatable1);
//searchForm.dataGridView1.DataSource = datatable1;
var query = "Select tblSagsoplysninger.dato_oprettet from tblSagsoplysninger";
var sqlcmd = new SqlCommand(query, constring);
var reader = sqlcmd.ExecuteReader();
while (reader.Read())
{
datoOprettetList.Add(reader.GetString(0));
}
searchForm.dataGridView1.DataSource = datoOprettetList;
constring.Close();
searchForm.Refresh();
//Select* from tblSagsoplysninger where tblSagsoplysninger.dato_oprettet like '%1996%'
}
public static TCon GetConnection <TCon>(DBConnectionString <TCon> cs)
where TCon : DbConnection, new()
=> GetConnection <TCon>(cs.ConnectionString);
/// <summary>
/// 获取地区/城市列表
/// </summary>
/// <returns></returns>
public DataTable GetRegionList()
{
string sql = @"select tp.RegionId,(select RegionName from dbo.Region where RegionId=tp.RegionId) RegionName from dbo.TaskProject tp where Status=0 GROUP BY tp.RegionId";
return(SqlHelper.ExecuteDataset(DBConnectionString.Get(ConnectionString), CommandType.Text, sql).Tables[0]);
}
private NameValuePermission(string value, DBConnectionString entry)
{
this._value = value;
this._entry = entry;
}
public string Edit(DBConnectionString.Class objClass)
{
try
{
if (ModelState.IsValid)
{
objClass.ModifiedBy = _userStatistics.UserId;
objClass.ModifiedOn = DateTime.Now;
if (objClass.Update() > 0)
{
return Convert.ToString(true);
}
return Convert.ToString(false);
}
return Convert.ToString(false);
}
catch (Exception ex)
{
return ex.Message.ToString();
}
}
public string Edit(DBConnectionString.Organization organization, string token)
{
try
{
if (ModelState.IsValid)
{
organization.ModifiedBy = _userStatistics.UserId;
organization.ModifiedDate = DateTime.Now;
if (organization.Update() > 0)
{
SaveFiles(token, this.GetType().Name, organization.OrganizationId);
return Convert.ToString(true);
}
return Convert.ToString(false);
}
return Convert.ToString(false);
}
catch (Exception ex)
{
return ex.Message.ToString();
}
}
