| Novembre 2009 | ||||||||||
| L | M | M | J | V | S | D | ||||
| 1 | ||||||||||
| 2 | 3 | 4 | 5 | 6 | 7 | 8 | ||||
| 9 | 10 | 11 | 12 | 13 | 14 | 15 | ||||
| 16 | 17 | 18 | 19 | 20 | 21 | 22 | ||||
| 23 | 24 | 25 | 26 | 27 | 28 | 29 | ||||
| 30 | ||||||||||
|
||||||||||
|
CREATE PROCEDURE nomprocedure(@param1
int,@param2 char(100))
|
|
DataTable dataTable =
connection.GetSchema("ProcedureParameters", newstring[] { null, null, "UpdateContact", });
|
|
CREATE PROCEDURE nomprocedure(@param1
int,@param2 char(100))
AS
BEGIN
SELECT [Colonne1],[Colonne2]
FROM dbo.[NomTable]
END
GO
|
|
SqlConnection connection =
new SqlConnection("Data Source=.;Initial Catalog=ContractDb;Integrated
Security=SSPI;");
SqlCommand command = new SqlCommand();
command.CommandText = "dbo.NomProcedure";
command.CommandType = CommandType.StoredProcedure;
command.Connection = connection;
connection.Open();
SqlDataReader reader =
command.ExecuteReader(CommandBehavior.SchemaOnly);
DataTable dt =
reader.GetSchemaTable();
|
|
/* récupérer toutes les bases de données*/
select * from sys.databases
|
|
/* récupérer toutes les tables de la base de données*/
select name,object_id from sys.tables
|
|
/* récupèrer les colonnes de la table Contact */
select * from sys.tables t,sys.columns c
where t.object_id=c.object_id
and t.name='Contact'
|
|
/* récupérer les clés primaires de la table Contact*/
select * from sys.tables t,sys.indexes p
where t.object_id=p.object_id
and t.name='Contact'
|
|
/* récupèrer les clés étrangères */
select * from sys.foreign_keys
select * from sys.foreign_key_columns
/*
select * from sys.foreign_key_columns
-donne la table clé primaire
avec referenced_object_id
-donne la table clé etrangère avec parent_object_id
*/
select distinct
pt.name as PK_TABLE_NAME,
ft.name as FK_TABLE_NAME,
pc.name as PK_COLUMN_NAME,
fc.name as FK_COLUMN_NAME,
f.name as RELATION_NAME
from
sys.tables pt,
sys.tables ft,
sys.columns pc,
sys.columns fc,
sys.foreign_keys f,
sys.foreign_key_columns k,
sys.indexes i
where
pt.object_id = f.referenced_object_id and
ft.object_id = f.parent_object_id and
pc.object_id = f.referenced_object_id and
fc.object_id = f.parent_object_id and
pc.column_id = k.referenced_column_id and
fc.column_id = k.parent_column_id
|
|
/* récupération des procédures stockées */
select * from sys.procedures
/* récupération des paramètres de procédure UpdateContact*/
select * from sys.procedures pr,sys.parameters pa
where pr.object_id=pa.object_id
and pr.name='UpdateContact'
|
|
DataTable dataTable = connection.GetSchema("tables")
DataTable dataTable = connection.GetSchema("tables", new string[] { null, null, "Contact", });
DataTable dataTable = connection.GetSchema("Procedures")
DataTable dataTable = connection.GetSchema("ProcedureParameters", new string[] { null, null, "UpdateContact", });
|
|
DataTable dt = Connection.GetSchema();
foreach (DataRow row in dt.Rows)
{
//row["CollectionName"]
}
|
|
SELECT
[ProductID],[ProductName],[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued] FROM dbo.[Products]
|
|
public class SqlDependencyExpiration :
ICacheItemExpiration
{
private bool isExpired;
public SqlDependencyExpiration(SqlCommand
command)
{
SqlDependency.Stop(command.Connection.ConnectionString);
SqlDependency.Start(command.Connection.ConnectionString);
command.Notification = null;
SqlDependency sqlDependency = new SqlDependency(command);
sqlDependency.OnChange += new OnChangeEventHandler(OnChanged);
if (command.Connection.State == ConnectionState.Closed)
command.Connection.Open();
command.ExecuteReader(CommandBehavior.CloseConnection);
}
void OnChanged(object sender, SqlNotificationEventArgs e)
{
SqlDependency sqlDependency = sender as SqlDependency;
sqlDependency.OnChange -= OnChanged;
isExpired = true;
}
#region ICacheItemExpiration Membres
public bool HasExpired()
{
return isExpired;
}
#endregion
}
|
|
private void button1_Click(object sender,
EventArgs e)
{
DataTable dataTable = new DataTable();
if (manager.Get("1")
!= null)
{
dataTable =(DataTable)
manager.Get("1").Value;
label1.Text = "from
cache";
}
else
{
SqlCommand sqlCommand =
new SqlCommand("Select ContactID,Contactname from dbo.[Contact]", new SqlConnection("Data Source=.;Initial Catalog=ContactDB;Integrated Security=SSPI;"));
sqlCommand.Connection.Open();
dataTable.Load(sqlCommand.ExecuteReader());
label1.Text = "from sql
server";
manager.Add("1", dataTable,
new ICacheItemExpiration[] { new SqlDependencyExpiration(sqlCommand) });
}
dataGridView1.DataSource = dataTable;
}
|
|
public class PollTimer :IDisposable
{
private System.Threading.Timer timer;
public void StartPolling(TimerCallback callbackMethod, int period)
{
if (callbackMethod == null)
throw new ArgumentNullException("callbackMethod");
if (period <= 0)
throw new Exception("period");
timer = new System.Threading.Timer(callbackMethod, null, 1000, period);
}
public void StopPolling()
{
if (timer == null)
throw
new InvalidOperationException("");
Dispose();
}
#region IDisposable Membres
public void Dispose()
{
timer.Dispose();
timer = null;
}
#endregion
}
|
|
PollTimer pollTimer =
new PollTimer();
private void button1_Click(object sender, EventArgs e)
{
pollTimer.StartPolling(Message, 1000);
}
public void Message(object obj)
{
MessageBox.Show("now");
}
|
|
///<summary>
/// Génére un DataSet typé
///</summary>
///<param name="path">chemin vers le fichier Xml</param>
///<param name="Namespace">Namespace à affecter au DataSet créé</param>
public void GenerateTypedDataSet(string path, string Namespace)
{
DataSet dataSet = new
DataSet();
dataSet.ReadXml(path);
CodeDomProvider codeDomProvider = new CSharpCodeProvider();
CodeNamespace codeNamespace = new CodeNamespace(Namespace);
using (TextWriter
textWriter = new StreamWriter(Path.ChangeExtension(path, ".Designer.cs"), false, Encoding.Default))
{
System.Data.Design.TypedDataSetGenerator.Generate(dataSet, codeNamespace, codeDomProvider);
codeDomProvider.GenerateCodeFromNamespace(codeNamespace,
textWriter, null);
}
dataSet.WriteXmlSchema(Path.ChangeExtension(path,
".xsd"));
}
///<summary>
/// Génére
un DataSet typé
///</summary>
///<param name="dataSet">DataSet contenant les DataTables,DataColumns,.. devant etre converti en DataSet typé</param>
///<param name="path">Chenim de sortie ou doit etre généré le DataSet typé</param>
///<param name="Namespace">Namespace à affecter au DataSet créé</param>
public void GenerateTypedDataSet(DataSet dataSet, string path, string
Namespace)
{
CodeDomProvider codeDomProvider = new CSharpCodeProvider();
CodeNamespace codeNamespace = new CodeNamespace(Namespace);
using (TextWriter
textWriter = new StreamWriter(Path.ChangeExtension(path,dataSet.DataSetName +
".Designer.cs"), false, Encoding.UTF8))
{
System.Data.Design.TypedDataSetGenerator.Generate(dataSet, codeNamespace, codeDomProvider);
codeDomProvider.GenerateCodeFromNamespace(codeNamespace,
textWriter, null);
}
dataSet.WriteXmlSchema(Path.ChangeExtension(path,
dataSet.DataSetName + ".xsd"));
}
|
|
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog openFileDialog = new OpenFileDialog();
if(openFileDialog.ShowDialog()==DialogResult.OK)
{
GenerateTypedDataSet(openFileDialog.FileName,"ContactNamespace");
}
}
|
|
public void GenerateTypedDataSet(string
path, string Namespace)
{
DataSet dataSet = new
DataSet();
dataSet.ReadXml(path);
ICodeGenerator codeGenerator = new CSharpCodeProvider().CreateGenerator();
CodeNamespace CodeNamespace = new CodeNamespace(Namespace);
using (TextWriter
textWriter = new StreamWriter(Path.ChangeExtension(path, ".Designer.cs"), false, Encoding.UTF8))
{
TypedDataSetGenerator.Generate(dataSet, CodeNamespace, codeGenerator);
codeGenerator.GenerateCodeFromNamespace(CodeNamespace,
textWriter, null);
}
dataSet.WriteXmlSchema(Path.ChangeExtension(path,
".xsd"));
}
|
|
public delegate void ChangedEventHandler();
|
|
public class ContactDAO
{
public event ChangedEventHandler Changed;
private static string connectionString = "Server=.;Database=ContactDB;Integrated Security=SSPI";
private SqlConnection sqlConnection =
null;
public ContactDAO()
{
SqlDependency.Stop(connectionString);
SqlDependency.Start(connectionString);
sqlConnection = new SqlConnection(connectionString);
}
~ContactDAO()
{
SqlDependency.Stop(connectionString);
}
//
public DataTable GetContacts()
{
try
{
DataTable dataTable =
new DataTable();
// IMPORTANT : spécifier le nom des colonnes (ne pas utiliser *)!!
SqlCommand sqlCommand = new SqlCommand(@"SELECT ContactName FROM dbo.[Contact]", sqlConnection);
sqlCommand.Notification = null;
SqlDependency sqlDependency =
new SqlDependency(sqlCommand);
sqlDependency.OnChange += new
OnChangeEventHandler(OnChanged);
if (sqlConnection.State ==
ConnectionState.Closed)
sqlConnection.Open();
// Autre point important : il faut obligatoirement exécuter la commande (du sqldependency)
dataTable.Load(sqlCommand.ExecuteReader(CommandBehavior.CloseConnection));
return dataTable;
}
catch (Exception
ex)
{
throw ex;
}
}
// evenement
void OnChanged(object sender, SqlNotificationEventArgs e)
{
SqlDependency sqlDependency = sender as SqlDependency;
sqlDependency.OnChange -= OnChanged;
if (Changed != null)
Changed();
}
}
|
|
ContactDAO contactDAO = new ContactDAO();
private void button1_Click(object sender, EventArgs e)
{
try
{
SqlClientPermission
sqlClientPermission = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);
sqlClientPermission.Demand();
contactDAO.Changed += new
ChangedEventHandler(contactDAO_Changed);
dataGridView1.DataSource = contactDAO.GetContacts();
}
catch (Exception
ex)
{
MessageBox.Show(ex.Message);
}
}
void contactDAO_Changed()
{
MessageBox.Show("données
modifiées");
// évite les opérations inter threads
ISynchronizeInvoke iSynchronizeInvoke = (ISynchronizeInvoke)this;
if (iSynchronizeInvoke.InvokeRequired)
{
ChangedEventHandler changed =
new ChangedEventHandler(contactDAO_Changed);
iSynchronizeInvoke.BeginInvoke(changed, null);
return;
}
// rechargement
dataGridView1.DataSource = contactDAO.GetContacts();
}
|
|
TypeTest typeTest = new TypeTest();
|
|
public class TypeTest
{
// 1 Pour tout ces types si on desire qu'ils puissent etre null
// utiliser les nullables
// puis test HasValue
public int TypeTestint;//0
public Decimal TypeTestDecimal;//0
public Byte TypeTestByte;//0
public SByte TypeTestSByte;//0
public Double TypeTestDouble;//0.0
public Single TypeTestSingle;//0.0
public DateTime TypeTestDateTimr;//01/01/0001 00:00:00
public bool TypeTestbool;// false
public Guid TypeTestGuid;//{00000000-0000-0000-0000-000000000000}
// 2 ne pas utiliser les
// mais faire un test !=null
public Byte[] TypeTestBytes;//null
public object TypeTestObject;//null
public string TypeTeststring;//null
public TypeTest()
{ }
}
|
|
create table TESTDBNULL
(
testint int null,
testBytes binary null,
teststring char(250) null,
testDecimal decimal null,
testDouble float null,
testSingle real null,
testobject sql_variant null,
testGuid uniqueidentifier null
)
|
|
public class TESTDBNULL
{
private Nullable<int> _testint;
public Nullable<int> testint
{
get { return _testint;
}
set { _testint = value;
}
}
private Nullable<Decimal> _testDecimal;
public Nullable<Decimal> testDecimal
{
get { return
_testDecimal; }
set { _testDecimal = value; }
}
private Nullable<Double> _testDouble;
public Nullable<Double> testDouble
{
get { return
_testDouble; }
set { _testDouble = value; }
}
private Nullable<Single> _testSingle;
public Nullable<Single> testSingle
{
get { return
_testSingle; }
set { _testSingle = value; }
}
private Nullable<Guid> _testGuid;
public Nullable<Guid> testGuid
{
get { return
_testGuid; }
set { _testGuid = value; }
}
private Byte[] _testBytes;
public Byte[] testBytes
{
get { return
_testBytes; }
set { _testBytes = value; }
}
private string _teststring;
public string teststring
{
get { return
_teststring; }
set { _teststring = value; }
}
private object _testobject;
public object testobject
{
get { return
_testobject; }
set { _testobject = value; }
}
public TESTDBNULL()
{ }
}
|
|
DbDataReader oDbDataReader;
DbCommand oDbCommand = DbProviderHelper.CreateCommand("SELECT
[testint],[testBytes],[teststring],[testDecimal],[testDouble],[testSingle],[testobject],[testGuid] FROM [TESTDBNULL]", DbProviderHelper.DbConnection,CommandType.Text);
oDbDataReader =
DbProviderHelper.ExecuteReader(oDbCommand);
while (oDbDataReader.Read())
{
TESTDBNULL oTESTDBNULL = new TESTDBNULL();
//LECTURE
if
(oDbDataReader["teststring"] != DBNull.Value)
oTESTDBNULL.teststring = Convert.ToString(oDbDataReader["teststring"]);
if
(oDbDataReader["testint"] != DBNull.Value)
oTESTDBNULL.testint = Convert.ToInt32(oDbDataReader["testint"]);
if
(oDbDataReader["testBytes"] != DBNull.Value)
oTESTDBNULL.testBytes = (System.Byte[])(oDbDataReader["testBytes"]);
if
(oDbDataReader["testDecimal"] != DBNull.Value)
oTESTDBNULL.testDecimal = Convert.ToDecimal(oDbDataReader["testDecimal"]);
if
(oDbDataReader["testDouble"] != DBNull.Value)
oTESTDBNULL.testDouble = Convert.ToDouble(oDbDataReader["testDouble"]);
if
(oDbDataReader["testSingle"] != DBNull.Value)
oTESTDBNULL.testSingle = Convert.ToSingle(oDbDataReader["testSingle"]);
if
(oDbDataReader["testobject"] != DBNull.Value)
oTESTDBNULL.testobject = oDbDataReader["testobject"];
if
(oDbDataReader["testGuid"] != DBNull.Value)
oTESTDBNULL.testGuid = (System.Guid)oDbDataReader["testGuid"];
lstTESTDBNULLs.Add(oTESTDBNULL);
}
oDbDataReader.Close();
|
|
DbCommand oDbCommand = DbProviderHelper.CreateCommand("INSERT INTO
[TESTDBNULL]([testint],[testBytes],[teststring],[testDecimal],[testDouble],[testSingle],[testobject],[testGuid])VALUES(@testint,@testBytes,@teststring,@testDecimal,@testDouble,@testSingle,@testobject,@testGuid)",
DbProviderHelper.DbConnection,CommandType.Text);
- // Soit la variable est un Nullable
(int,Decimal …Guid voir plus haut)
if
(oTESTDBNULL.testint.HasValue)
oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@testint", DbType.Int32, oTESTDBNULL.testint));
else
oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@testint", DbType.Int32, DBNull.Value));
- // Soit la variable peut avoir la valeur null
(object,string,Byte[])
if (oTESTDBNULL.testBytes != null)
oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@testBytes", DbType.Binary, oTESTDBNULL.testBytes));
else
oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@testBytes", DbType.Binary, DBNull.Value));
result = DbProviderHelper.ExecuteNonQuery(oDbCommand);
|
|
BLLCtrl oBLLCtrl = new
BLLCtrl();
List<Contact>
Contacts;
// Get all
Contacts = oBLLCtrl.ContactCtrl.GetContacts();
dataGridView1.DataSource = Contacts;
// get element(s) of relation
List<Contact>
result = oBLLCtrl.ContactCtrl.GetContactsOfCategory(1);
Category category =
oBLLCtrl.ContactCtrl.GetCategoryOfContact(contact);
// filtre + trie sur la couche
metier
List<Contact> result = Contacts.FindAll(
new PredicateBuilder<Contact>().MakePredicate(
PredicateExpression.StartsWith("Contactname", "A")
)
);
result.Sort(new Contact.ContactnameComparer(SorterMode.Ascending));
dataGridView1.DataSource = result;
// Add
oBLLCtrl.ContactCtrl.AddContact(new Contact(4, "Dupond", 25, 1));
// Update
Contact result = Contacts.Find(new PredicateBuilder<Contact>().MakePredicate(
PredicateExpression.Eq("ContactID", 4)
)
);
result.Contactname = "Lama";
oBLLCtrl.ContactCtrl.UpdateContact(result);
// Delete
Contact result = Contacts.Find(new PredicateBuilder<Contact>().MakePredicate(
PredicateExpression.Eq("ContactID", 4)
)
);
oBLLCtrl.ContactCtrl.RemoveContact(result);
//Xml
Serialization
oBLLCtrl.ContactCtrl.SerializeContacts(@"c:Contacts.xml", Contacts);
List<Contact>
result = oBLLCtrl.ContactCtrl.DeserializeContacts(@"c:Contacts.xml");
|
|
public partial class CategoriesComboBox : ComboBox
{
public CategoriesComboBox()
{
InitializeComponent();
this.ImageList = imageList1;
}
public CategoriesComboBox(IContainer
container)
{
container.Add(this);
InitializeComponent();
this.ImageList = imageList1;
}
// l'imaglist utilisée pour afficher les images dans la comboBox
private ImageList ImageList;
public void AddCategoriess(List<Categories> categoriess)
{
this.Items.Clear();
foreach (Categories categories in categoriess)
this.Items.Add(new CategoriesItem(categories));
if (this.Items.Count > 0)
this.SelectedIndex =
0;
}
public Categories
GetCategories()
{
CategoriesItem categoriesItem = this.SelectedItem as CategoriesItem;
return new
Categories(categoriesItem.CategoryID, categoriesItem.CategoryName, categoriesItem.Description, categoriesItem.Picture);
}
protected override void OnDrawItem(System.Windows.Forms.DrawItemEventArgs e)
{
base.OnDrawItem(e);
if (e.Index != -1)
{
CategoriesItem
categoriesItem;
Rectangle rectangle;
Size size;
rectangle = e.Bounds;
size = ImageList.ImageSize;
e.DrawBackground();
e.DrawFocusRectangle();
categoriesItem = (CategoriesItem)this.Items[e.Index];
if (categoriesItem.ImageIndex !=
-1)
{
ImageList.Draw(e.Graphics, rectangle.Left,
rectangle.Top, categoriesItem.ImageIndex);
e.Graphics.DrawString(categoriesItem.ToString(), e.Font, new SolidBrush(e.ForeColor), rectangle.Left + size.Width,
rectangle.Top);
}
else
e.Graphics.DrawString(categoriesItem.ToString(), e.Font, new SolidBrush(e.ForeColor), rectangle.Left,
rectangle.Top);
}
}
}
public class CategoriesItem
{
private int _CategoryID;
public int CategoryID
{
get { return
_CategoryID; }
set { _CategoryID = value; }
}
private string _CategoryName;
public string CategoryName
{
get { return
_CategoryName; }
set { _CategoryName = value; }
}
private string _Description;
public string Description
{
get { return
_Description; }
set { _Description = value; }
}
private Byte[] _Picture;
public Byte[] Picture
{
get { return _Picture;
}
set { _Picture = value;
}
}
// imageIndex de l'item
private int _imageIndex;
public int ImageIndex
{
get { return
_imageIndex; }
set { _imageIndex = value; }
}
public CategoriesItem()
{ }
public CategoriesItem(Categories
categories)
{
this.CategoryID = categories.CategoryID;
this.CategoryName = categories.CategoryName;
this.Description = categories.Description;
this.Picture = categories.Picture;
this.ImageIndex = 1;
}
// important !! c'est la valeur retournée qui sera
affichée
public override string ToString()
{
return CategoryName;
}
}
|
|
partial class CategoriesComboBox
{
///<summary>
/// Variable nécessaire au concepteur.
///</summary>
private System.ComponentModel.IContainer
components = null;
///<summary>
/// Nettoyage des ressources utilisées.
///</summary>
///<param name="disposing">true si les ressources managées doivent être supprimées ; sinon, false.</param>
protected override void Dispose(bool disposing)
{
if (disposing && (components != null))
{
components.Dispose();
}
base.Dispose(disposing);
}
#region Code généré par le Concepteur de composants
///<summary>
/// Méthode requise pour la prise en charge du
concepteur - ne modifiez pas
/// le contenu de cette méthode avec l'éditeur de
code.
///</summary>
private void InitializeComponent()
{
this.components = new
System.ComponentModel.Container();
System.ComponentModel.ComponentResourceManager resources
= new System.ComponentModel.ComponentResourceManager(typeof(CategoriesComboBox));
this.imageList1 = new
System.Windows.Forms.ImageList(this.components);
this.SuspendLayout();
//
// imageList1
//
this.imageList1.ImageStream =
((System.Windows.Forms.ImageListStreamer)(resources.GetObject("imageList1.ImageStream")));
this.imageList1.TransparentColor =
System.Drawing.Color.Fuchsia;
this.imageList1.Images.SetKeyName(0, "VSObject_Map_Protected.bmp");
//
// CategoriesComboBox
//
this.DrawMode =
System.Windows.Forms.DrawMode.OwnerDrawFixed;
this.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList;
this.ResumeLayout(false);
}
#endregion
private System.Windows.Forms.ImageList
imageList1;
}
|
Persistent DataSet
même si je ne suis pas trop adepte du DataSet(qui selon moi ne peut etre applicable qu'à un petit projet entre autres)
Persistent DataSet est une solution qui peut être interessante, génération de tout le code d'accès aux données, bases supportées sur SQL Server,Oracle,PostgreSQL,système de requetage,etc.
la solution s'integre à Visual Studio 2005, il suffit alors d'ajouter un nouvel élément de type PersistentLayer, puis aller dans les propriétés et générer gràce à la barre d'outil persistent
dataset (à afficher)
voila un lien vers un bon tutorial sur Developpez.com
http://morpheus.developpez.com/persistentdatasets/
http://www.lastcomponent.com/index.php?page=downloads/main&left=left
moi ce qui m'interesse le plus c'est la mise en place d'un systeme de requetage ressemblant un peu à Linq (en "décompilé") que je vais étudié
|
public byte[] ConvertImageTobyte(string
path)
{
byte[] result = null;
using (FileStream
oFileStream = new FileStream(path, FileMode.Open, FileAccess.Read))
{
result = new byte[oFileStream.Length];
oFileStream.Read(result, 0, (int)oFileStream.Length);
}
return result;
}
private Bitmap ConvertbyteToImage(byte[] ImageBuffer)
{
Bitmap result = null;
using (MemoryStream
oMemoryStream = new MemoryStream(ImageBuffer, true))
{
oMemoryStream.Write(ImageBuffer, 0, ImageBuffer.Length);
result = new Bitmap(oMemoryStream);
}
return result;
}
|
|
// from file to DB
// oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("Photo", DbType.Binary, oImageTable.Photo));
OpenFileDialog oOpenFileDialog = new OpenFileDialog();
if (oOpenFileDialog.ShowDialog() == DialogResult.OK)
{
oBLLCtrl.ImageTableCtrl.AddImageTable(new ImageTable(1, ConvertImageTobyte(oOpenFileDialog.FileName)));
}
//from DB to picturebox
//oImageTable.Photo est de type
byte[]
ImageTable oImageTable =
oBLLCtrl.ImageTableCtrl.GetImageTable(1);
pictureBox1.Image = ConvertbyteToImage(oImageTable.Photo);
|
|
create database contactsdb;
use contactsdb;
create table Contact
(
ContactID int not null primary key AUTO_INCREMENT,
ContactName char(150) not null,
ContactFirstName char(150) not null,
ContactAge int null,
ContactCategory char(50) not null
);
create table ContactCategory
(
ContactCategory char(50) not null primary key
);
ALTER TABLE contactsdb.contact ADD CONSTRAINT FK_contact_ContactCategory FOREIGN KEY FK_contact_ContactCategory (ContactCategory)
REFERENCES contactcategory (ContactCategory);
INSERT INTO ContactCategory(ContactCategory)
VALUES('Famille');
INSERT INTO ContactCategory(ContactCategory)
VALUES('Amis');
INSERT INTO ContactCategory(ContactCategory)
VALUES('Travail');
INSERT INTO ContactCategory(ContactCategory)
VALUES('autres');
|
|
Cs2DbProvider.DbProviderUtilities DbProviderUtilities;
System.Data.Common.DbConnection oDbConnection;
private void Form1_Load(object sender, EventArgs e)
{
DbProviderUtilities = new Cs2DbProvider.DbProviderUtilities();
oDbConnection = DbProviderUtilities.CreateConnection("System.Data.Odbc", "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=contactsdb");
}
private void button1_Click(object sender, EventArgs e)
{
//MessageBox.Show(InsertCategory("nouveau").ToString());
// MessageBox.Show(UpdateCategory("nouveau","changement du nom de category").ToString());
MessageBox.Show(RemoveCategory("changement du nom de category").ToString());
GetContactCategories();
}
public void GetContactCategories()
{
DataSet oDataSet =
DbProviderUtilities.FillDataSet(DbProviderUtilities.CreateCommand(oDbConnection, "select * from ContactCategory", CommandType.Text));
dataGridView1.DataSource = oDataSet.Tables[0];
}
public int InsertCategory(string Category)
{
System.Data.Common.DbCommand oDbCommand =
DbProviderUtilities.CreateCommand(oDbConnection,"insert into ContactCategory(ContactCategory) values(?)", CommandType.Text);
oDbCommand.Parameters.Add(DbProviderUtilities.CreateParameter("Category", DbType.String, Category));
int result =
DbProviderUtilities.ExecuteNonQuery(oDbCommand);
return result;
}
public int UpdateCategory(string Category,string NewCategory)
{
System.Data.Common.DbCommand oDbCommand =
DbProviderUtilities.CreateCommand(oDbConnection, "update ContactCategory set ContactCategory=? where ContactCategory=?", CommandType.Text);
oDbCommand.Parameters.Add(DbProviderUtilities.CreateParameter("NewCategory", DbType.String, NewCategory));
oDbCommand.Parameters.Add(DbProviderUtilities.CreateParameter("Category", DbType.String, Category));
int result =
DbProviderUtilities.ExecuteNonQuery(oDbCommand);
return result;
}
public int RemoveCategory(string Category)
{
System.Data.Common.DbCommand oDbCommand =
DbProviderUtilities.CreateCommand(oDbConnection, "delete from ContactCategory where ContactCategory=?", CommandType.Text);
oDbCommand.Parameters.Add(DbProviderUtilities.CreateParameter("Category", DbType.String, Category));
int result =
DbProviderUtilities.ExecuteNonQuery(oDbCommand);
return result;
}
|
|
///<summary>
/// Classe de gestion du cache tirant parti des generics du Framework .NET
2.0
///</summary>
///<typeparam name="K">Type de la
clé</typeparam>
///<typeparam name="T">Type de l'objet
inséré</typeparam>
public class
CacheManager<K, T>
{
///<summary>
/// Le cache
///</summary>
public static Dictionary<K, T> Cache;
///<summary>
/// Constructeur static
///</summary>
static CacheManager()
{
Cache = new Dictionary<K, T>();
}
///<summary>
/// Récupère
l'objet en cache
///</summary>
///<param
name="Key">Clé</param>
///<returns>L'objet correspondant à la clé</returns>
public static T GetDataFromCache(K Key)
{
if (Cache.ContainsKey(Key))
return Cache[Key];
else
return default(T);
}
///<summary>
/// Ajoute un
objet au cache
///</summary>
///<param
name="Key">Clé</param>
///<param
name="obj">Objet ajoué</param>
public static void Add(K Key, T obj)
{
if (Cache.ContainsKey(Key))
Cache[Key] = obj;
else
Cache.Add(Key, obj);
}
///<summary>
/// supprime l'objet du cache
///</summary>
///<param name="Key">Clé permettant de trouver l'objet à supprimer dans le cache</param>
public static void Remove(K Key)
{
if (Cache.ContainsKey(Key))
Cache.Remove(Key);
}
///<summary>
/// Renseigne si l'objet est en cache
///</summary>
///<param name="Key">Clé de l'objet recherché</param>
///<returns>si
l'objet correspondant à la clé est ou non en cache</returns>
public static bool isInCache(K Key)
{
return Cache.ContainsKey(Key);
}
///<summary>
/// Compte le
nombre d'objets en cache
///</summary>
///<returns></returns>
public static int Count()
{
return Cache.Count;
}
}
|
|
List<Contact> cs = CacheManager<string, List<Contact>>.GetDataFromCache("1");
if (cs != null)
{
dataGridView1.DataSource = cs;
}
else
{
List<Contact> oContacts = new List<Contact>();
oContacts.Add(new Contact(1, "Romagny", "Jérôme", null));
oContacts.Add(new Contact(3, "Martin", "Paul", 35));
oContacts.Add(new Contact(4, "Bairant", "André", 40));
CacheManager<string, List<Contact>>.Add("1", oContacts);
dataGridView1.DataSource = oContacts;
}
|
|
///<summary>
/// Classe de gestion de cache pour le Framework .NET 1.0 ou
supérieur
///</summary>
public class CacheManager
{
///<summary>
/// Le cache
///</summary>
public static System.Collections.Hashtable Cache;
///<summary>
/// Constructeur static
///</summary>
static CacheManager()
{
Cache = new System.Collections.Hashtable();
}
///<summary>
/// Récupère
l'objet en cache
///</summary>
///<param
name="Key">Clé</param>
///<returns>L'objet correspondant à la clé</returns>
public static object GetDataFromCache(string Key)
{
if (Cache.ContainsKey(Key))
return Cache[Key];
else
return null;
}
///<summary>
/// Ajoute un objet au cache
///</summary>
///<param name="Key">Clé</param>
///<param
name="obj">Objet ajoué</param>
public static void Add(string Key, object obj)
{
if (Cache.ContainsKey(Key))
Cache[Key] = obj;
else
Cache.Add(Key, obj);
}
///<summary>
/// supprime l'objet du cache
///</summary>
///<param name="Key">Clé permettant de trouver l'objet à supprimer dans le cache</param>
public static void Remove(string Key)
{
if (Cache.ContainsKey(Key))
Cache.Remove(Key);
}
///<summary>
/// Renseigne si l'objet est en cache
///</summary>
///<param name="Key">Clé de l'objet recherché</param>
///<returns>si
l'objet correspondant à la clé est ou non en cache</returns>
public static bool isInCache(string Key)
{
return Cache.ContainsKey(Key);
}
///<summary>
/// Compte le
nombre d'objets en cache
///</summary>
///<returns></returns>
public static int Count()
{
return Cache.Count;
}
}
|
|
List<Contact> cs =(List<Contact>) CacheManager.GetDataFromCache("1");
if (cs != null)
{
dataGridView1.DataSource = cs;
}
else
{
// TRAVAIL AVEC UNE CLASSE
COLLECTION
List<Contact> oContacts = new List<Contact>();
oContacts.Add(new Contact(1, "Romagny", "Jérôme", null));
oContacts.Add(new Contact(3, "Martin", "Paul", 35));
oContacts.Add(new Contact(4, "Bairant", "André", 40));
CacheManager.Add("1", oContacts);
dataGridView1.DataSource = oContacts;
}
|
|
private void button2_Click(object sender,
EventArgs e)
{
System.Data.DataTable oDataTable = new DataTable();
System.Data.OleDb.OleDbDataAdapter oOleDbDataAdapter =
new System.Data.OleDb.OleDbDataAdapter();
oOleDbDataAdapter.SelectCommand = new
System.Data.OleDb.OleDbCommand();
oOleDbDataAdapter.SelectCommand.Connection = new
System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=C:Documents and SettingsromagnyMes documentsDIVERS.xlsx;Extended Properties=""Excel 12.0;HDR=YES;""");
oOleDbDataAdapter.SelectCommand.CommandType = CommandType.Text;
oOleDbDataAdapter.SelectCommand.CommandText = "SELECT
[ContactID],[ContactName],[ContactFirstName],[ContactEmail],[ContactCategoryID] FROM [Contact$]";
oOleDbDataAdapter.Fill(oDataTable);
dataGridView1.DataSource = oDataTable;
}
|
Compléments d'exploration de données Microsoft SQL Server 2005 pour Microsoft Office 2007
Creating and Using a DataTableReader
|
public BObject.CONTACT ReadDataGridViewRow(int nRowIndex)
{
BObject.CONTACT oCONTACT;
oCONTACT = new BObject.CONTACT();
oCONTACT.code_contact = Convert.ToInt32(dataGridView1[0, nRowIndex].Value);
oCONTACT.nom_contact = Convert.ToString(dataGridView1[1, nRowIndex].Value);
oCONTACT.prenom_contact = Convert.ToString(dataGridView1[2, nRowIndex].Value);
oCONTACT.photo_contact = Convert.ToString(dataGridView1[3, nRowIndex].Value);
oCONTACT.code_type_contact = Convert.ToInt32(dataGridView1[3, nRowIndex].Value);
oCONTACT.code_genre_contact = Convert.ToInt32(dataGridView1[4, nRowIndex].Value);
oCONTACT.date_naissance_contact = Convert.ToDateTime(dataGridView1[5, nRowIndex].Value);
return oCONTACT;
} public BObject.CONTACT ReadDataGridViewRow()
{
BObject.CONTACT oCONTACT;
oCONTACT = new BObject.CONTACT();
int nRowIndex = dataGridView1.CurrentRow.Index;
oCONTACT.code_contact = Convert.ToInt32(dataGridView1[0, nRowIndex].Value);
oCONTACT.nom_contact = Convert.ToString(dataGridView1[1, nRowIndex].Value);
oCONTACT.prenom_contact = Convert.ToString(dataGridView1[2, nRowIndex].Value);
oCONTACT.photo_contact = Convert.ToString(dataGridView1[3, nRowIndex].Value);
oCONTACT.code_type_contact = Convert.ToInt32(dataGridView1[3, nRowIndex].Value);
oCONTACT.code_genre_contact = Convert.ToInt32(dataGridView1[4, nRowIndex].Value);
oCONTACT.date_naissance_contact = Convert.ToDateTime(dataGridView1[5, nRowIndex].Value);
return oCONTACT;
}
|
|
DataGridViewComboBoxCell oDataGridViewCellcode_genre_contact;
oDataGridViewCellcode_genre_contact = new DataGridViewComboBoxCell();
// 1 remplir la liste
foreach (BObject.GENRE_CONTACT oGENRE_CONTACTRow in oGENRE_CONTACT.BObjectGENRE_CONTACTs)
{
oDataGridViewCellcode_genre_contact.Items.Add(oGENRE_CONTACTRow.libelle_genre_contact);
}
// 2 Selection de la ligne
foreach (BObject.GENRE_CONTACT oGENRE_CONTACTRow in oGENRE_CONTACT.BObjectGENRE_CONTACTs)
{
if (oGENRE_CONTACTRow.code_genre_contact == oContactRow.code_genre_contact)
{
oDataGridViewCellcode_genre_contact.Value = oGENRE_CONTACTRow.libelle_genre_contact;
}
}
if (oContactRow.date_naissance_contact != null)
{
oDataGridViewCelldate_naissance_contact.Value = oContactRow.date_naissance_contact.ToString();
}
|