Generer les lignes insert sql pour vos tables sql server

Soyez le premier à donner votre avis sur cette source.

Snippet vu 19 852 fois - Téléchargée 28 fois

Contenu du snippet

Voici un bout de code sympa pour vous qui comme moi developpez des appli puis parfois se pose le probleme de copier certaines données d'une base a une autres a travers un script sql (install par exemple).
Ce code vous affiche les tables sous une base et vous pouvez cocher puis generer les requetes insert pour les colonnes .
Essayez plus tot
PS: je n'ai pas teste comment ca reagirait fasse a une table qui a un champ image.

Copy past the code into a winform under c#. ( i speak more often english than french, sorry)

Source / Exemple :


using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Data.SqlTypes;
using System.Text;
using System.IO;
namespace InsertGenerator
{
	/// <summary>
	/// Summary description for Form1.
	/// </summary>
	public class Form1 : System.Windows.Forms.Form
	{
		private System.Windows.Forms.CheckedListBox checkedCols;
		private System.Windows.Forms.Label label1;
		private System.Windows.Forms.TextBox textBoxConn;
		private System.Windows.Forms.Label label2;
		private System.Windows.Forms.ComboBox comboBox1;
		private System.Windows.Forms.TextBox textBox2;
		private System.Windows.Forms.FolderBrowserDialog folderBrowserDialog1;
		private System.Windows.Forms.Button buttonWhere;
		private System.Windows.Forms.Button buttonConnect;
		private System.Windows.Forms.Button buttonGen;
		/// <summary>
		/// Required designer variable.
		/// </summary>
		private System.ComponentModel.Container components = null;
		private System.Windows.Forms.Button buttonNewCon;

		SqlConnection con ;
		public Form1()
		{
			//
			// Required for Windows Form Designer support
			//
			InitializeComponent();

			//
			// TODO: Add any constructor code after InitializeComponent call
			//
		}

		/// <summary>
		/// Clean up any resources being used.
		/// </summary>
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if (components != null) 
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
			System.Resources.ResourceManager resources = new System.Resources.ResourceManager(typeof(Form1));
			this.checkedCols = new System.Windows.Forms.CheckedListBox();
			this.label1 = new System.Windows.Forms.Label();
			this.textBoxConn = new System.Windows.Forms.TextBox();
			this.label2 = new System.Windows.Forms.Label();
			this.comboBox1 = new System.Windows.Forms.ComboBox();
			this.textBox2 = new System.Windows.Forms.TextBox();
			this.folderBrowserDialog1 = new System.Windows.Forms.FolderBrowserDialog();
			this.buttonWhere = new System.Windows.Forms.Button();
			this.buttonConnect = new System.Windows.Forms.Button();
			this.buttonGen = new System.Windows.Forms.Button();
			this.buttonNewCon = new System.Windows.Forms.Button();
			this.SuspendLayout();
			// 
			// checkedCols
			// 
			this.checkedCols.Location = new System.Drawing.Point(8, 16);
			this.checkedCols.Name = "checkedCols";
			this.checkedCols.Size = new System.Drawing.Size(184, 349);
			this.checkedCols.TabIndex = 0;
			// 
			// label1
			// 
			this.label1.Location = new System.Drawing.Point(200, 24);
			this.label1.Name = "label1";
			this.label1.Size = new System.Drawing.Size(96, 16);
			this.label1.TabIndex = 1;
			this.label1.Text = "Connection String";
			// 
			// textBoxConn
			// 
			this.textBoxConn.Location = new System.Drawing.Point(304, 24);
			this.textBoxConn.Name = "textBoxConn";
			this.textBoxConn.Size = new System.Drawing.Size(288, 20);
			this.textBoxConn.TabIndex = 2;
			this.textBoxConn.Text = "Server=(local);Database=PROPHARMADEMO;user=Mana;pwd=Mana;";
			// 
			// label2
			// 
			this.label2.Location = new System.Drawing.Point(208, 88);
			this.label2.Name = "label2";
			this.label2.Size = new System.Drawing.Size(72, 16);
			this.label2.TabIndex = 3;
			this.label2.Text = "Tables List";
			// 
			// comboBox1
			// 
			this.comboBox1.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList;
			this.comboBox1.Location = new System.Drawing.Point(304, 88);
			this.comboBox1.Name = "comboBox1";
			this.comboBox1.Size = new System.Drawing.Size(288, 21);
			this.comboBox1.TabIndex = 4;
			this.comboBox1.SelectedIndexChanged += new System.EventHandler(this.OnTableSelectedChanged);
			// 
			// textBox2
			// 
			this.textBox2.Location = new System.Drawing.Point(304, 120);
			this.textBox2.Name = "textBox2";
			this.textBox2.ReadOnly = true;
			this.textBox2.Size = new System.Drawing.Size(248, 20);
			this.textBox2.TabIndex = 5;
			this.textBox2.Text = "c:\\";
			// 
			// buttonWhere
			// 
			this.buttonWhere.Image = ((System.Drawing.Image)(resources.GetObject("buttonWhere.Image")));
			this.buttonWhere.Location = new System.Drawing.Point(560, 120);
			this.buttonWhere.Name = "buttonWhere";
			this.buttonWhere.Size = new System.Drawing.Size(32, 24);
			this.buttonWhere.TabIndex = 6;
			this.buttonWhere.Click += new System.EventHandler(this.OnFolderSelect);
			// 
			// buttonConnect
			// 
			this.buttonConnect.Location = new System.Drawing.Point(400, 56);
			this.buttonConnect.Name = "buttonConnect";
			this.buttonConnect.Size = new System.Drawing.Size(88, 24);
			this.buttonConnect.TabIndex = 7;
			this.buttonConnect.Text = "Connect";
			this.buttonConnect.Click += new System.EventHandler(this.OnConnectClick);
			// 
			// buttonGen
			// 
			this.buttonGen.Location = new System.Drawing.Point(480, 328);
			this.buttonGen.Name = "buttonGen";
			this.buttonGen.Size = new System.Drawing.Size(120, 24);
			this.buttonGen.TabIndex = 8;
			this.buttonGen.Text = "Generate";
			this.buttonGen.Click += new System.EventHandler(this.OnGenerateClick);
			// 
			// buttonNewCon
			// 
			this.buttonNewCon.Location = new System.Drawing.Point(496, 56);
			this.buttonNewCon.Name = "buttonNewCon";
			this.buttonNewCon.Size = new System.Drawing.Size(96, 24);
			this.buttonNewCon.TabIndex = 9;
			this.buttonNewCon.Text = "New Connection";
			this.buttonNewCon.Click += new System.EventHandler(this.OnCloseConClick);
			// 
			// Form1
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
			this.ClientSize = new System.Drawing.Size(608, 373);
			this.Controls.Add(this.buttonNewCon);
			this.Controls.Add(this.buttonGen);
			this.Controls.Add(this.buttonConnect);
			this.Controls.Add(this.buttonWhere);
			this.Controls.Add(this.textBox2);
			this.Controls.Add(this.comboBox1);
			this.Controls.Add(this.label2);
			this.Controls.Add(this.textBoxConn);
			this.Controls.Add(this.label1);
			this.Controls.Add(this.checkedCols);
			this.Name = "Form1";
			this.Text = "Form1";
			this.ResumeLayout(false);

		}
		#endregion

		/// <summary>
		/// The main entry point for the application.
		/// </summary>
		[STAThread]
		static void Main() 
		{
			Application.Run(new Form1());
		}
		private void OnConnectClick(object sender, EventArgs e)
		{
			con = new SqlConnection(@textBoxConn.Text.Trim());
			
			try
			{
				con.Open();
			}
			catch(Exception ex)
			{
				MessageBox.Show(ex.Message);
				return;
			}
			SqlCommand cc = new SqlCommand("select * from sysobjects where xtype = 'U'",con);
			SqlDataReader rd = cc.ExecuteReader();
			
			while(rd.Read())
			{
				comboBox1.Items.Add(rd.GetSqlString(0));
				
			}
			rd.Close();
			comboBox1.SelectedIndex = 0;
			textBoxConn.Enabled = false;
			MessageBox.Show("Connected !");
		}
		private void OnCloseConClick(object sender, EventArgs e)
		{
			if (textBoxConn.Enabled == true)return;
			con.Close();
			
			textBoxConn.Enabled = true;
		}
		private void OnTableSelectedChanged(object sender, EventArgs e)
		{
			LoadFields(GetTableFromName(comboBox1.Text));
		}
		private void LoadFields(DataTable _table)
		{
			checkedCols.Items.Clear();
			for (int i =0 ; i < _table.Columns.Count ;i++ )
			{
				if(_table.Columns[i].AutoIncrement)continue;
				checkedCols.Items.Add(_table.Columns[i].ColumnName);
			}
			_gl = _table;
		}
		private DataTable GetTableFromName(string _tableName)
		{
			#region GetTableFromName
			string retour = string.Empty;
			
			SqlDataAdapter da = new SqlDataAdapter("select top 1 * from " +"[" + _tableName + "]",con);
			DataTable t = new DataTable(_tableName);
			da.FillSchema(t,SchemaType.Source);
			da.Fill(t);
			return t;
			#endregion
		}
		private string GetPlaceHolder(Type _colType, int i, bool doForUpdate)
		{
			string retour = String.Empty;
			string _stringVarPlaceHolder = "'{{{0}}}'";
			 string _otherVarPlaceHolder = "{{{0}}}";
			string _castDatetime = "(cast '{0}' as DateTime)";
			
			#region Process String base var
			if (_colType == System.Type.GetType("System.Char"))
				retour = string.Format(_stringVarPlaceHolder,i.ToString());
				
			if (_colType == System.Type.GetType("System.String"))
				retour = string.Format(_stringVarPlaceHolder,i.ToString());
				
			#endregion

			#region Process Integer Base var
			if (_colType == System.Type.GetType("System.Boolean"))
				retour = string.Format( _otherVarPlaceHolder,i.ToString());

			if (_colType == System.Type.GetType("System.DateTime"))
			{
				if (!doForUpdate)
					retour = string.Format( _otherVarPlaceHolder,i.ToString());
				else
					retour = string.Format(String.Format(_castDatetime, _otherVarPlaceHolder),i.ToString());
			}

			if (_colType == System.Type.GetType("System.Decimal"))
				retour = string.Format( _otherVarPlaceHolder,i.ToString());
				
			if (_colType == System.Type.GetType("System.Double"))
				retour = string.Format( _otherVarPlaceHolder,i.ToString());

			if (_colType == System.Type.GetType("System.Int16"))
				retour = string.Format( _otherVarPlaceHolder,i.ToString());

			if (_colType == System.Type.GetType("System.Int32"))
				retour = string.Format( _otherVarPlaceHolder,i.ToString());

			if (_colType == System.Type.GetType("System.Byte"))
				retour = string.Format( _otherVarPlaceHolder,i.ToString());

			if (_colType == System.Type.GetType("System.Int64"))
				retour = string.Format( _otherVarPlaceHolder,i.ToString());
				
			if (_colType == System.Type.GetType("System.SByte"))
				retour = string.Format( _otherVarPlaceHolder,i.ToString());

			if (_colType == System.Type.GetType("System.Byte"))
				retour = string.Format( _otherVarPlaceHolder,i.ToString());

			if (_colType == System.Type.GetType("System.Byte[]"))
				retour = string.Format( _otherVarPlaceHolder,i.ToString());

			if (_colType == System.Type.GetType("System.Single"))
				retour = string.Format( _otherVarPlaceHolder,i.ToString());
			#endregion

			return retour;
		}
		private DataTable _gl;
		private string MakeInsert(string[] cols,DataTable _table) 
		{
			#region MakeInsert
			string _sqlInsert = "insert into  [{0}] (";
			string retour = string.Empty;
			StringBuilder tmp =  new StringBuilder();
			
			tmp.Append(String.Format(_sqlInsert,comboBox1.Text));		
			#region Build Insert sql
			
			for (int i =0 ; i < cols.Length ;i++ )
			{
				if(_table.Columns[cols[i]].AutoIncrement)continue;
				tmp.Append(cols[i]);
				if ((i + 1) == cols.Length)
					tmp.Append(")");
				else
					tmp.Append(",");
			}
			tmp.Append(" values (");
			int positionIndex = 0;
			for (int i =0 ; i < cols.Length ;i++ )
			{
				
				if (_table.Columns[cols[i]].AutoIncrement)
				{
					positionIndex = i;
					continue;
				}
				Type tp = _table.Columns[cols[i]].DataType; 
				tmp.Append(GetPlaceHolder(tp,positionIndex,false));
				
				if ((i + 1) != cols.Length)
					tmp.Append(",");
				positionIndex++;
			}
			
			tmp.Append(")" + "\nGO\n");
			#endregion
			StringBuilder tmp2 = new StringBuilder();

		    SqlCommand cmd = new SqlCommand("select " + string.Join(",",cols) + " from " + _table.TableName,con);
			SqlDataReader rd = cmd.ExecuteReader();
			string[] values = new string[cols.Length];
			while(rd.Read())
			{
				for (int k = 0; k < cols.Length; k++)
				{
					values[k] = rd.GetValue(rd.GetOrdinal(cols[k])).ToString().Replace("'","''").Replace(",",".");
				}
				tmp2.Append(string.Format(tmp.ToString(),values));
			}
			rd.Close();
			retour = tmp2.ToString();	    
			return retour;
			#endregion
		}
		private void MakeCS(string streamF,string dest)
		{
			try
			{
				
				string _header = "//**********************************************************//\n"+
					             "//*                    Table:{0}                            *//\n"+
								 "//*      Généré le : {1} par {2}                           *//\n"+
								"//*********************************************************//\n\n";	          
				StreamWriter Output=new StreamWriter(dest + @"\" + _gl.TableName.Trim() + ".sql");
				Output.WriteLine(string.Format(_header,_gl.TableName,DateTime.Now,"Sodson"));
				Output.WriteLine(streamF);
				Output.Flush();
				//Ferme les flots de données
				Output.Close();
			}
			catch(Exception ex)
			{
				Console.WriteLine(ex.Message);
			}
		}
		private void OnGenerateClick(object sender, EventArgs e)
		{
			string[] cols;
			if (checkedCols.CheckedItems.Count >0)
			{
				cols = new string[checkedCols.CheckedItems.Count];
			}
			else
			{
				return;
			}

			for(int i = 0; i < cols.Length; i++)
			{
				cols[i] = checkedCols.CheckedItems[i].ToString();
			}
			MakeCS(MakeInsert(cols,_gl),textBox2.Text);
			MessageBox.Show("Generation terminé");
		}
		private void OnFolderSelect(object sender,EventArgs e)
		{
			if ( folderBrowserDialog1.ShowDialog() == DialogResult.OK)
			{
				textBox2.Text = folderBrowserDialog1.SelectedPath;
			}
		}

	}
}

A voir également

Ajouter un commentaire

Commentaires

bloodyangel90
Messages postés
7
Date d'inscription
vendredi 29 février 2008
Statut
Membre
Dernière intervention
11 juin 2008
-
bon code mais dommage qui n'y a pas de zip ni de commentaire même si la compréhension est assez simple.
tmcuh
Messages postés
463
Date d'inscription
dimanche 22 décembre 2002
Statut
Membre
Dernière intervention
18 avril 2009
-
Bon code, j'aime bien les mécanismes. 9/10 car y'a pas de zip
amelinajc
Messages postés
2
Date d'inscription
jeudi 27 décembre 2001
Statut
Membre
Dernière intervention
11 janvier 2006
-
hi kmikaz
ben ce serait gentil de nous faire un zip
merci d'y penser
amelinajc

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.

Le fait d'être membre vous permet d'avoir des options supplémentaires.