public List <CatagotyViewModel> BindAllMainCatagoryAndSubCatagoty()
{
List <CatagotyViewModel> catagoryViewModelListObj = new List <CatagotyViewModel>();
//try
//{
using (SqlConnection)
{
SqlCmd.CommandText = "select *from vwCatagoryWithSubCatagory";
SqlConnection.Open();
SqlDataReader sqlDataReaderObj = SqlCmd.ExecuteReader();
if (sqlDataReaderObj.HasRows)
{
Int16 serial = 0;
while (sqlDataReaderObj.Read())
{
CatagotyViewModel catagoryViewModelObj = new CatagotyViewModel();
catagoryViewModelObj.SerialID = ++serial;
catagoryViewModelObj.SubCatagoryID = sqlDataReaderObj["SubCatagoryID"].ToString();
catagoryViewModelObj.SubCatagoryName = sqlDataReaderObj["SubCatagoryName"].ToString();
catagoryViewModelObj.MainCatagoryID = sqlDataReaderObj["CatagoryID"].ToString();
catagoryViewModelObj.MainCatagoryName = sqlDataReaderObj["CatagoryName"].ToString();
catagoryViewModelListObj.Add(catagoryViewModelObj);
}
}
sqlDataReaderObj.Close();
}
return(catagoryViewModelListObj);
}
public List <Manufacturers> BindAllManufacturers()
{
List <Manufacturers> manufacturersListObj = new List <Manufacturers>();
using (SqlConnection)
{
SqlCmd.CommandText = "select ManufacturersID, ManufacturersName from Table_Manufacturers";
SqlConnection.Open();
SqlDataReader sqlDataReaderObj = SqlCmd.ExecuteReader();
if (sqlDataReaderObj.HasRows)
{
Int16 serial = 0;
while (sqlDataReaderObj.Read())
{
Manufacturers manufacturerObj = new Manufacturers();
manufacturerObj.SerialID = ++serial;
manufacturerObj.ManufacturerID = sqlDataReaderObj["ManufacturersID"].ToString();
manufacturerObj.ManufacturerName = sqlDataReaderObj["ManufacturersName"].ToString();
manufacturersListObj.Add(manufacturerObj);
}
}
sqlDataReaderObj.Close();
}
return(manufacturersListObj);
}
public static User GetByLogin(string login)
{
using (SqlCmd cmd = new SqlCmd("SELECT Id, UserName, UserEmail, TimeZone, JoinDate, ShowEmail, DisplayName, IsActive, TwoFactor FROM [User] WHERE UserName = @Login OR UserEmail = @Login", false))
{
cmd.AddIString("@Login", 50, login);
var r = cmd.ExecuteSingleRowOrNull();
if (r == null)
{
return(null);
}
return(new User
{
Id = (int)r["Id"],
UserName = (string)r["UserName"],
Email = (string)r["UserEmail"],
TimeZone = TimeZoneInfo.FindSystemTimeZoneById((string)r["TimeZone"]),
JoinDate = (DateTime)r["JoinDate"],
ShowEmail = (bool)r["ShowEmail"],
DisplayName = r["DisplayName"] as string ?? "",
IsActive = (bool)r["IsActive"],
TwoFactorAuth = r["TwoFactor"].ToString()
});
}
}
public Company GetCompanyById(int compId)
{
OpenConnection();
SqlCmd.CommandText = @"SELECT * FROM companies WHERE company_id = @companyId";
SqlCmd.Parameters.Add(new SQLiteParameter("@companyId")
{
Value = compId
});
SqlCmd.ExecuteNonQuery();
SQLiteDataReader reader;
reader = SqlCmd.ExecuteReader();
if (reader.Read())
{
Company comp = new Company(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4),
reader.GetString(5), reader.GetString(6), reader.GetString(7));
reader.Close();
CloseConnection();
return(comp);
}
else
{
return(null);
}
}
public Card GetCurrentCard(Card card)
{
OpenConnection();
SqlCmd.CommandText = @"SELECT * FROM cards WHERE cardnumber = @cardnumber";
SqlCmd.Parameters.Add(new SQLiteParameter("@cardnumber")
{
Value = card.CardNumber
});
SqlCmd.ExecuteNonQuery();
SQLiteDataReader reader;
reader = SqlCmd.ExecuteReader();
if (reader.Read())
{
card = new Card(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetInt32(3),
reader.GetInt32(4), reader.GetString(5), reader.GetString(6), reader.GetInt32(7), reader.GetInt32(8), CheckConfirm(reader.GetInt32(9)));
reader.Close();
CloseConnection();
return(card);
}
else
{
return(null);
}
}
public IList <CovalcoDTO> GetAllData()
{
try
{
return(SqlCmd.NewTextCommand(this._conn.DatabaseConnString)
.SetCommandText(@"SELECT TOP (1000) [AirportCode]
,[CurrencyCode]
,[Rate]
,[TaxAmount]
,[RTOrder]
,[Details]
,[CountryCode]
,[TaxCode]
,[TaxDefinition]
,[RemittanceBySelling]
,[RemittanceByLifting]
,[AppliedOnDeparture]
,[AppliedOnArrival]
,[LastModified]
,[InsertedDate]
FROM [Connectio].[dbo].[CovalcoDetails]")
.Execute().ToList <CovalcoDTO>());
}
catch (Exception ex)
{
throw new SqlServerException("", ex);
}
}
/**
* méthode qui lit dans la base de données tous les élèves
* retour : la liste de tous les élèves
*/
// ********* LISTER TOUS *******************
public override List <Equipe> ListerTous()
{
List <Equipe> liste = new List <Equipe>();
try
{
SqlCmd.CommandText =
"select ide, nom, joueur1, joueur2 " +
"from equipe " +
"order by ide asc ";
SqlCmd.Parameters.Clear();
SqlDataReader sqlReader = SqlCmd.ExecuteReader();
while (sqlReader.Read() == true)
{
liste.Add(new Equipe(Convert.ToInt32(sqlReader["ide"]),
Convert.ToString(sqlReader["nom"]),
Convert.ToInt32(sqlReader["joueur1"]),
Convert.ToInt32(sqlReader["joueur2"])));
}
sqlReader.Close();
}
catch (Exception e)
{
throw new ExceptionAccesBD(e.Message);
}
return(liste);
}
public List <Manufacturers> BindAllManufacturers()
{
List <Manufacturers> manufacturersListObj = new List <Manufacturers>();
try
{
SqlCmd.CommandText = "select ManufacturersID, ManufacturersName from Table_Manufacturers";
SqlConnection.Open();
SqlDataReader sqldataReaderObj = SqlCmd.ExecuteReader();
if (sqldataReaderObj.HasRows)
{
while (sqldataReaderObj.Read())
{
Manufacturers manufacturersObj = new Manufacturers();
//manufacturersObj.ID = (int)sqldataReaderObj["ID"];
manufacturersObj.ManufacturerID = sqldataReaderObj["ManufacturersID"].ToString();
manufacturersObj.ManufacturerName = sqldataReaderObj["ManufacturersName"].ToString();
manufacturersListObj.Add(manufacturersObj);
}
}
sqldataReaderObj.Close();
}
catch (Exception ex)
{
}
finally
{
SqlConnection.Close();
}
return(manufacturersListObj);
}
// ********** AJOUTER UN JOUEUR ************************
public override void Ajouter(Joueur obj)
{
try
{
int idj;
SqlCmd.CommandText = "select max(idj) + 1 from joueurs";
SqlCmd.Parameters.Clear();
SqlDataReader sqlReader = SqlCmd.ExecuteReader();
sqlReader.Read();
if (sqlReader[0] == DBNull.Value)
{
idj = 1;
}
else
{
idj = Convert.ToInt32(sqlReader[0]);
}
sqlReader.Close();
SqlCmd.CommandText =
"insert into joueurs(idj,nom,prenom,style,nomImage) " +
"values(@idj,@nom,@prenom,@style,@nomImage)";
SqlCmd.Parameters.Clear();
SqlCmd.Parameters.Add("@idj", SqlDbType.Int).Value = idj;
SqlCmd.Parameters.Add("@nom", SqlDbType.VarChar).Value = obj.Nom;
SqlCmd.Parameters.Add("@prenom", SqlDbType.VarChar).Value = obj.Prenom;
SqlCmd.Parameters.Add("@style", SqlDbType.VarChar).Value = obj.Style;
SqlCmd.Parameters.Add("@nomImage", SqlDbType.VarChar).Value = obj.NomImage;
SqlCmd.ExecuteNonQuery();
}
catch (Exception e)
{
throw new ExceptionAccesBD(e.Message);
}
}
private Admin CheckAdmin(Admin admin)
{
OpenConnection();
SqlCmd.CommandText = @"SELECT * FROM admins WHERE password = @password";
SqlCmd.Parameters.Add(new SQLiteParameter("@password")
{
Value = admin.Password
});
SqlCmd.ExecuteNonQuery();
SQLiteDataReader reader;
reader = SqlCmd.ExecuteReader();
if (reader.Read())
{
admin = new Admin(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4),
reader.GetInt32(5), reader.GetString(6), reader.GetString(7), reader.GetString(8), reader.GetString(9));
reader.Close();
CloseConnection();
return(admin);
}
else
{
return(null);
}
}
/**
* // ********* LISTER TOUS *******************
*/
public override List <Joueur> ListerTous()
{
List <Joueur> liste = new List <Joueur>();
try
{
SqlCmd.CommandText =
"select idj, nom, prenom, style, nomImage " +
"from joueurs " +
"order by nom asc";
SqlCmd.Parameters.Clear();
SqlDataReader sqlReader = SqlCmd.ExecuteReader();
while (sqlReader.Read() == true)
{
liste.Add(new Joueur(Convert.ToInt32(sqlReader["idj"]),
Convert.ToString(sqlReader["nom"]),
Convert.ToString(sqlReader["prenom"]),
Convert.ToString(sqlReader["style"]),
Convert.ToString(sqlReader["nomImage"])));
}
sqlReader.Close();
}
catch (Exception e)
{
throw new ExceptionAccesBD(e.Message);
}
return(liste);
}
public override void Disconnect()
{
kcp.RemoveLink(this);
var cmd = SqlCmd.UpdateRow(userInfo, userInfo.id, "userinfo");
SqlClient.Instance.ExecuteCmd(cmd);
}
public void likeDislike(bool like_dislike)
{
SQLiteConnection Con = new SQLiteConnection(sqlCon);
Con.Open();
SQLiteCommand SqlCmd;
if (theirProfileID == null)
{
}
else
{
if (like_dislike)
{
SqlCmd = new SQLiteCommand("INSERT INTO Liked([profileID], [shownProfileID], [liked]) " +
"VALUES('" + myProfileID + "', '" + theirProfileID + "', 1)", Con);
}
else
{
SqlCmd = new SQLiteCommand("INSERT INTO Liked([profileID], [shownProfileID], [liked]) " +
"VALUES('" + myProfileID + "', '" + theirProfileID + "', 0)", Con);
}
SqlCmd.ExecuteNonQuery();
}
Con.Close();
}
private void button2_Click(object sender, EventArgs e)
{
try
{
Con_UsersDB.Close();
Con_UsersDB.Open();
SQLiteCommand SqlCmd = new SQLiteCommand();
if (comboBox1.SelectedIndex == 0)
{
SqlCmd = new SQLiteCommand("INSERT INTO Users(Username, [Password], Usertype) VALUES('" + textBox1.Text + "', '" + textBox2.Text + "', 'Bruger');", Con_UsersDB);
} //if you selected "Bruger" in the usertype drop down menu, then an insert query without values, will be saved in "SqlCmd"
if (comboBox1.SelectedIndex == 1)
{
SqlCmd = new SQLiteCommand("INSERT INTO Users(Username, [Password], Usertype) VALUES('" + textBox1.Text + "', '" + textBox2.Text + "', 'Superbruger');", Con_UsersDB);
}
if (comboBox1.SelectedIndex == 2)
{
SqlCmd = new SQLiteCommand("INSERT INTO Users(Username, [Password], Usertype) VALUES('" + textBox1.Text + "', '" + textBox2.Text + "', 'Administrator');", Con_UsersDB);
}
object obj = SqlCmd.ExecuteNonQuery();
textBox1.Clear();
textBox2.Clear();
MessageBox.Show("Data has been submitted");
Con_UsersDB.Close();
}
catch (Exception exc) { MessageBox.Show(exc.Message); }
}
// ********** AJOUTER UNE EQUIPE ************************
public override void Ajouter(Equipe obj)
{
try
{
int ide;
SqlCmd.CommandText = "select max(ide) + 1 from equipe";
SqlCmd.Parameters.Clear();
SqlDataReader sqlReader = SqlCmd.ExecuteReader();
sqlReader.Read();
if (sqlReader[0] == DBNull.Value)
{
ide = 1;
}
else
{
ide = Convert.ToInt32(sqlReader[0]);
}
sqlReader.Close();
SqlCmd.CommandText =
"insert into equipe(ide,nom,joueur1,joueur2) " +
"values(@ide,@nom,@joueur1,@joueur2)";
SqlCmd.Parameters.Clear();
SqlCmd.Parameters.Add("@ide", SqlDbType.Int).Value = ide;
SqlCmd.Parameters.Add("@nom", SqlDbType.VarChar).Value = obj.Nom;
SqlCmd.Parameters.Add("@joueur1", SqlDbType.Int).Value = obj.Joueur1;
SqlCmd.Parameters.Add("@joueur2", SqlDbType.Int).Value = obj.Joueur2;
SqlCmd.ExecuteNonQuery();
}
catch (Exception e)
{
throw new ExceptionAccesBD(e.Message);
}
}
public List <SubCatagory> BindSubCatagoryAccordingToSelectedMainCatagory(string selectedMainCatagoryID)
{
List <SubCatagory> subCatagoryListObj = new List <SubCatagory>();
using (SqlConnection)
{
SqlCmd.CommandText = "select SubCatagoryID, SubCatagoryName from Table_SubCatagory where MainCatagoryID = '" + selectedMainCatagoryID + "'";
SqlConnection.Open();
SqlDataReader sqlDataReaderObj = SqlCmd.ExecuteReader();
if (sqlDataReaderObj.HasRows)
{
while (sqlDataReaderObj.Read())
{
SubCatagory subCatagoryObj = new SubCatagory();
subCatagoryObj.SubCatagoryID = sqlDataReaderObj["SubCatagoryID"].ToString();
subCatagoryObj.SubCatagoryName = sqlDataReaderObj["SubCatagoryName"].ToString();
subCatagoryListObj.Add(subCatagoryObj);
}
}
sqlDataReaderObj.Close();
}
return(subCatagoryListObj);
}
/// <summary>
/// 执行SQL语句操作(Update,Delete,Insert)，返回受影响的行数
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parms">参数集合</param>
/// <returns>受影响的行数</returns>
public int ExectueNonQueryBySql(string sql, SqlParameter[] parms = null)
{
try
{
if (SqlCon.State != ConnectionState.Open)
{
SqlCon.Open();
}
SqlCmd = SqlTransactionFlag ? new SqlCommand(sql, SqlCon, SqlTran) : new SqlCommand(sql, SqlCon);
SqlCmd.CommandType = CommandType.Text;
if (parms != null)
{
AddParameters(parms);
}
return(SqlCmd.ExecuteNonQuery());
}
catch (Exception ex)
{
throw ex;
}
finally
{
SqlCmd.Dispose();
if (!SqlTransactionFlag)
{
SqlCon.Close();
SqlCon.Dispose();
}
}
}
/// <summary>
/// Save into database
/// </summary>
/// <param name="entries"></param>
public void SubmitChanges(List <ResourceEntry> entries, bool deleteRowNotInResource)
{
SqlMaker gen = new SqlMaker(tname.FormalName)
{
PrimaryKeys = new string[] { name_column }
};
StringBuilder builder = new StringBuilder();
int count = 0;
int max = 1000;
foreach (var entry in entries)
{
gen.Clear();
gen.Add(name_column, entry.Name);
gen.Add(value_column, entry.NewValue);
if (order_column != null)
{
gen.Add(order_column, entry.Index);
}
switch (entry.Action)
{
case DataRowAction.Add:
builder.AppendLine(gen.Insert());
break;
case DataRowAction.Change:
builder.AppendLine(gen.Update());
break;
case DataRowAction.Delete:
if (deleteRowNotInResource)
{
builder.AppendLine(gen.Delete());
}
break;
}
count = (count + 1) % max;
if (count == 0)
{
ExecuteNonQuery();
}
}
ExecuteNonQuery();
void ExecuteNonQuery()
{
string SQL = builder.ToString();
builder.Clear();
if (!string.IsNullOrEmpty(SQL))
{
SqlCmd cmd = new SqlCmd(tname.Provider, SQL);
cmd.ExecuteNonQuery();
}
}
}
public Client GetClientById(int clientId)
{
OpenConnection();
SqlCmd.CommandText = @"SELECT * FROM clients WHERE client_id = @clientId";
SqlCmd.Parameters.Add(new SQLiteParameter("@clientId")
{
Value = clientId
});
SqlCmd.ExecuteNonQuery();
SQLiteDataReader reader;
reader = SqlCmd.ExecuteReader();
if (reader.Read())
{
Client client = new Client(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4),
reader.GetInt32(5), reader.GetString(6), reader.GetString(7), reader.GetString(8), reader.GetString(9),
reader.GetString(10), reader.GetDouble(11), reader.GetInt32(12));
reader.Close();
CloseConnection();
return(client);
}
else
{
return(null);
}
}
public int Generate(string nameSpace, string sa, string password)
{
string SQL = @"
USE [{0}]
SELECT name, OBJECT_DEFINITION(OBJECT_ID) AS sp
FROM sys.procedures
WHERE is_ms_shipped <> 1
ORDER BY name
";
SqlCmd cmd = new SqlCmd(databaseName.Provider, string.Format(SQL, databaseName.Name));
// cmd.ChangeConnection(sa, password);
DataTable dt = cmd.FillDataTable();
foreach (DataRow row in dt.Rows)
{
SpProc proc = new SpProc(databaseName, (string)row[SP_NAME], row[SP_DEFINITION].IsNull <string>(""));
string sourceCode = proc.Proc(nameSpace, databaseName.Name, sa, password);
WriteFile(proc.SpName, sourceCode, nameSpace, proc.IsSpChanged(nameSpace, databaseName.Name));
}
return(dt.Rows.Count);
}
public void UpdateCashback(Client client, double cback)
{
double newCash;
if (cashback == 0.0)
{
newCash = cback;
}
else
{
newCash = client.Cashback + cback;
}
OpenConnection();
SqlCmd.CommandText = @"UPDATE clients SET cashback = @cashback WHERE client_id = @clientid";
SqlCmd.Parameters.Add(new SQLiteParameter("@cashback")
{
Value = newCash
});
SqlCmd.Parameters.Add(new SQLiteParameter("@clientid")
{
Value = client.Id
});
SqlCmd.ExecuteNonQuery();
SQLiteDataReader reader;
reader = SqlCmd.ExecuteReader();
reader.Close();
CloseConnection();
}
private static DataTable FillTable(TableName tname)
{
var dt = new SqlCmd(tname.Provider, $"SELECT TOP 1 * FROM {tname.FormalName}").FillDataTable();
dt.SetSchemaAndTableName(tname);
var schema = new TableSchema(tname);
dt.PrimaryKeys(schema.PrimaryKeys.Keys);
foreach (IColumn column in schema.Columns)
{
DataColumn col = dt.Columns[column.ColumnName];
col.AllowDBNull = column.Nullable;
col.AutoIncrement = column.IsIdentity;
//because string supports Unicode
if (column.CType == CType.NVarChar || column.CType == CType.NChar)
{
if (column.Length > 0)
{
col.MaxLength = column.Length / 2;
}
}
}
if (dt.Rows.Count > 0)
{
dt.Rows[0].Delete();
}
dt.AcceptChanges();
return(dt);
}
public void UpdateMoneyBox(Client client, double mbox)
{
double newCash;
if (mbox == 0.0)
{
newCash = mbox;
}
else
{
newCash = client.Moneybox + mbox;
}
OpenConnection();
SqlCmd.CommandText = @"UPDATE clients SET moneybox = @moneybox WHERE client_id = @clientid";
SqlCmd.Parameters.Add(new SQLiteParameter("@moneybox")
{
Value = newCash
});
SqlCmd.Parameters.Add(new SQLiteParameter("@clientid")
{
Value = client.Id
});
SqlCmd.ExecuteNonQuery();
SQLiteDataReader reader;
reader = SqlCmd.ExecuteReader();
reader.Close();
CloseConnection();
}
private Company CheckCompany(Company comp)
{
OpenConnection();
SqlCmd.CommandText = @"SELECT * FROM companies WHERE login = @login and password = @password";
SqlCmd.Parameters.Add(new SQLiteParameter("@login")
{
Value = comp.Login
});
SqlCmd.Parameters.Add(new SQLiteParameter("@password")
{
Value = comp.Password
});
SqlCmd.ExecuteNonQuery();
SQLiteDataReader reader;
reader = SqlCmd.ExecuteReader();
if (reader.Read())
{
comp = new Company(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4),
reader.GetString(5), reader.GetString(6), reader.GetString(7));
reader.Close();
CloseConnection();
return(comp);
}
else
{
return(null);
}
}
private Client CheckClient(Client client)
{
OpenConnection();
SqlCmd.CommandText = @"SELECT * FROM clients WHERE login = @login and password = @password";
SqlCmd.Parameters.Add(new SQLiteParameter("@login")
{
Value = client.Login
});
SqlCmd.Parameters.Add(new SQLiteParameter("@password")
{
Value = client.Password
});
SqlCmd.ExecuteNonQuery();
SQLiteDataReader reader;
reader = SqlCmd.ExecuteReader();
if (reader.Read())
{
client = new Client(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4),
reader.GetInt32(5), reader.GetString(6), reader.GetString(7), reader.GetString(8), reader.GetString(9),
reader.GetString(10), reader.GetDouble(11), reader.GetInt32(12));
reader.Close();
CloseConnection();
return(client);
}
else
{
return(null);
}
}
public List <MainCatagory> GetAllMainCatagory()
{
List <MainCatagory> mainCatagoryListObj = new List <MainCatagory>();
using (SqlConnection)
{
SqlCmd.CommandText = "select CatagoryID, CatagoryName from Table_Catagory";
SqlConnection.Open();
SqlDataReader sqldataReaderObj = SqlCmd.ExecuteReader();
if (sqldataReaderObj.HasRows)
{
while (sqldataReaderObj.Read())
{
MainCatagory mainCatagoryObj = new MainCatagory();
mainCatagoryObj.CatagoryID = sqldataReaderObj["CatagoryID"].ToString();
mainCatagoryObj.CatagoryName = sqldataReaderObj["CatagoryName"].ToString();
mainCatagoryListObj.Add(mainCatagoryObj);
}
}
sqldataReaderObj.Close();
}
return(mainCatagoryListObj);
}
public static DataSet LastOrCurrentTable(TableName tname, int rows = -1)
{
if (LastResult != null)
{
return(LastResult);
}
if (tname != null)
{
string sql;
if (rows > 0)
{
sql = $"SELECT TOP {rows} * FROM {tname.FormalName}";
}
else
{
sql = $"SELECT * FROM {tname.FormalName}";
}
var dt = new SqlCmd(tname.Provider, sql).FillDataTable();
dt.SetSchemaAndTableName(tname);
return(dt.DataSet);
}
return(null);
}
/**
* méthode qui lit dans la base de données tous les élèves
* retour : la liste de tous les élèves
*/
// ********* LISTER TOUS *******************
public override List <Rencontre> ListerTous()
{
List <Rencontre> liste = new List <Rencontre>();
try
{
SqlCmd.CommandText =
"select idr, phase, NumEquipe1, NumEquipe2, NumArbitre, NumTable, NumGagnant, score " +
"from rencontres " +
"order by idr asc ";
SqlCmd.Parameters.Clear();
SqlDataReader sqlReader = SqlCmd.ExecuteReader();
while (sqlReader.Read() == true)
{
liste.Add(new Rencontre(Convert.ToInt32(sqlReader["idr"]),
Convert.ToString(sqlReader["phase"]),
Convert.ToInt32(sqlReader["NumEquipe1"]),
Convert.ToInt32(sqlReader["NumEquipe2"]),
Convert.ToInt32(sqlReader["NumArbitre"]),
Convert.ToInt32(sqlReader["NumTable"]),
Convert.ToInt32(sqlReader["NumGagnant"]),
Convert.ToString(sqlReader["score"])));
}
sqlReader.Close();
}
catch (Exception e)
{
throw new ExceptionAccesBD(e.Message);
}
return(liste);
}
public int AddNewProduct(SqlParameter[] objDataParameter)
{
int affectedRows = 0;
//using (SqlConnection)
//{
try
{
SqlCmd.CommandText = "spAddNewproduct";
SqlCmd.CommandType = CommandType.StoredProcedure;
SqlCmd.Parameters.AddRange(objDataParameter);
SqlConnection.Open();
affectedRows = (int)SqlCmd.ExecuteScalar();
}
catch (Exception ex)
{
}
finally
{
SqlConnection.Close();
}
//}
return(affectedRows);
}
public int AddManufacturer(SqlParameter[] objDataParameter)
{
using (SqlConnection)
{
SqlCmd.CommandText = "spAddManufactures";
SqlCmd.CommandType = CommandType.StoredProcedure;
SqlCmd.Parameters.AddRange(objDataParameter);
SqlConnection.Open();
affectedRows = (int)SqlCmd.ExecuteScalar();
//SqlConnection.Close();
}
//try
//{
// SqlCmd.CommandText = "spAddManufacture";
// SqlCmd.CommandType = CommandType.StoredProcedure;
// SqlCmd.Parameters.AddRange(objDataParameter);
// SqlConnection.Open();
// affectedRows = (int)SqlCmd.ExecuteScalar();
//}
//catch (Exception ex)
//{
//}
//finally
//{
// SqlConnection.Close();
//}
return(affectedRows);
}
public static int GenerateRows(StreamWriter writer, TableSchema schema, Locator where, bool hasIfExists)
{
TableName tableName = schema.TableName;
string sql = string.Format("SELECT * FROM {0}", tableName);
if (where != null)
sql = string.Format("SELECT * FROM {0} WHERE {1}", tableName, where);
SqlCmd cmd = new SqlCmd(tableName.Provider, sql);
TableClause script = new TableClause(schema);
int count = 0;
cmd.Read(
reader =>
{
DataTable schema1 = reader.GetSchemaTable();
string[] columns = schema1.AsEnumerable().Select(row => row.Field<string>("ColumnName")).ToArray();
object[] values = new object[columns.Length];
while (reader.HasRows)
{
while (reader.Read())
{
reader.GetValues(values);
if (hasIfExists)
writer.WriteLine(script.IF_NOT_EXISTS_INSERT(columns, values));
else
writer.WriteLine(script.INSERT(columns, values));
count++;
if (count % 5000 == 0)
writer.WriteLine(TableClause.GO);
}
reader.NextResult();
}
});
if (count != 0)
writer.WriteLine(TableClause.GO);
return count;
}